T-SQL to script out logins and permissions of all databases

T-SQL to script out logins and passwords for the SQL server instance. Can be useful for taking backups for your logins and DB permissions and can also be useful for migrating logins and permissions.

Note: In SSMS, check the “Maximum number of characters displayed in each column” settings before executing the below script. It is defaulted to 256 characters.

Tools » Options » Query Results » Results to Text » Maximum number of characters displayed in each column

Also check the “Maximum Characters Retrieved” under “Results to Grid” section.  

Script to Script out Login Information

USE [master]
GO
IF OBJECT_ID ('fn_hexadecimal') IS NOT NULL
DROP function fn_hexadecimal
GO
CREATE FUNCTION [dbo].[fn_hexadecimal]
(
    -- Add the parameters for the function here
     @binvalue varbinary(256)
)
RETURNS VARCHAR(256)
AS
BEGIN

    DECLARE @thisissaroracharvalue varchar(1000)
    DECLARE @i int
    DECLARE @length int
    DECLARE @hexstring char(16)
    SELECT @charvalue = '0x'
    SELECT @i = 1
    SELECT @length = DATALENGTH (@binvalue)
    SELECT @hexstring = '0123456789ABCDEF'
    WHILE (@i <= @length)
    BEGIN
      DECLARE @tempint int
      DECLARE @firstint int
      DECLARE @secondint int
      SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
      SELECT @firstint = FLOOR(@tempint/16)
      SELECT @secondint = @tempint - (@firstint*16)
      SELECT @charvalue = @charvalue +
        SUBSTRING(@hexstring, @firstint+1, 1) +
        SUBSTRING(@hexstring, @secondint+1, 1)
      SELECT @i = @i + 1
    END
    return @charvalue

END
GO


SET NOCOUNT ON
GO
--use MASTER
GO
PRINT '-----------------------------------------------------------------------------'
PRINT '-- Script created on ' + CAST(GETDATE() AS VARCHAR(100))
PRINT '-----------------------------------------------------------------------------'
PRINT ''
PRINT '-----------------------------------------------------------------------------'
PRINT '-- Create the windows logins'
PRINT '-----------------------------------------------------------------------------'
SELECT 'IF NOT EXISTS (SELECT * FROM master.sys.server_principals WHERE [name] = ''' + [name] + ''')
    CREATE LOGIN [' + [name] + '] FROM WINDOWS WITH DEFAULT_DATABASE=[' +
        default_database_name + '], DEFAULT_LANGUAGE=[us_english]
GO

'
FROM master.sys.server_principals
where type_desc In ('WINDOWS_GROUP', 'WINDOWS_LOGIN')
AND [name] not like 'BUILTIN%'
and [NAME] not like 'NT AUTHORITY%'
and [name] not like '%\SQLServer%'
GO

PRINT '-----------------------------------------------------------------------------'
PRINT '-- Create the SQL Logins'
PRINT '-----------------------------------------------------------------------------'
select 'IF NOT EXISTS (SELECT * FROM master.sys.sql_logins WHERE [name] = ''' + [name] + ''')
CREATE LOGIN [' + [name] + '] WITH PASSWORD=' + [master].[dbo].[fn_hexadecimal](password_hash) + ' HASHED, SID = ' + [master].[dbo].[fn_hexadecimal]([sid]) + ', DEFAULT_DATABASE=[' + default_database_name + '], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=' + CASE WHEN is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END + ', CHECK_POLICY=OFF
GO
IF EXISTS (SELECT * FROM master.sys.sql_logins WHERE [name] = ''' + [name] + ''')
ALTER LOGIN [' + [name] + '] WITH CHECK_EXPIRATION=' + CASE WHEN is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END + ', CHECK_POLICY=' + CASE WHEN is_policy_checked = 1 THEN 'ON' ELSE 'OFF' END + '
GO
'
--[name], [sid] , password_hash
from master.sys.sql_logins
where type_desc = 'SQL_LOGIN'
and [name] not in ('sa', 'guest')

PRINT '-----------------------------------------------------------------------------'
PRINT '-- Disable any logins'
PRINT '-----------------------------------------------------------------------------'
SELECT 'ALTER LOGIN [' + [name] + '] DISABLE
GO
'
from master.sys.server_principals
where is_disabled = 1

PRINT '-----------------------------------------------------------------------------'
PRINT '-- Assign groups'
PRINT 'GO'
PRINT '-----------------------------------------------------------------------------'
select
'EXEC master..sp_addsrvrolemember @loginame = N''' + l.name + ''', @rolename = N''' + r.name + '''
GO

'
from master.sys.server_role_members rm
join master.sys.server_principals r on r.principal_id = rm.role_principal_id
join master.sys.server_principals l on l.principal_id = rm.member_principal_id
where l.[name] not in ('sa')
AND l.[name] not like 'BUILTIN%'
and l.[NAME] not like 'NT AUTHORITY%'
and l.[name] not like '%\SQLServer%'

Result this to a logins.sql file and keep that file safe somewhere. 

T-SQL to script out permissions of all databases using the below T-SQL code and save the output to a (.sql) file. Remember to result the output of the below code to “result to file”


Note: In SSMS, check the “Maximum number of characters displayed in each column” settings before executing the below script. It is defaulted to 256 characters.

Tools » Options » Query Results » Results to Text » Maximum number of characters displayed in each column

Also check the “Maximum Characters Retrieved” under “Results to Grid” section.

Script to Script out Permissions for all Databases

/*
This will script out all permissions on source.

Description - This Script has be run on source and then Save the output to a file and run the output on destination server.


Note: In SSMS, check the “Maximum number of characters displayed in each column” settings before executing the below script. It is defaulted to 256 characters.

Tools » Options » Query Results » Results to Text » Maximum number of characters displayed in each column

Also check the "Maximum Characters Retrieved" under "Results to Grid" section.


Make sure you open a new query window and execute the script after changing the "Maximum number of characters displayed in each column” setting.
*/


set nocount on

/*Grant All Databases Access Script*/

Print '---------------------------------Script Grant All DB Access---------------------------------------'
Print '--------------------------------------------------------------------------------------------------'

exec sp_msforeachdb 'Use ? select ''Use '' + ''?'' + CHAR(13) + CHAR(10)+ ''GO''
+ CHAR(13) + CHAR(10) + ''if not exists (select * from dbo.sysusers where name = N''''''+''''+usu.name+'''''')''+ Char(13) + Char(10) +   
''EXEC sp_grantdbaccess N''''''+ lo.loginname collate database_default +  '''''''' + '',N''''''+ usu.name collate database_default+ ''''''''  +char(13) + char(10) + ''GO''
from sysusers usu , master.dbo.syslogins lo where usu.sid = lo.sid and (usu.islogin = 1 and usu.isaliased = 0 and usu.hasdbaccess = 1)'

Print '--------------------------------Grant All DB Access Script Complete for All DB---------------------'
Print '---------------------------------------------------------------------------------------------------'

/*Add Roles to All Databases Script*/

Print '-------------------------------Script Adding Roles All Databases-----------------------------------'
Print '---------------------------------------------------------------------------------------------------'

exec sp_msforeachdb 'Use ? select ''Use '' + ''?'' + CHAR(13) + CHAR(10)+ ''GO''
+ CHAR(13) + CHAR(10) + ''if not exists (select *  from dbo.sysusers where name = N''''''+ name +'''''' )'' + Char(13) + Char(10) +
''EXEC sp_addrole N'''''' + name collate database_default +  '''''''' + Char(13) + Char(10) + ''GO''
from sysusers where uid > 0 and uid=gid and issqlrole=1'

Print '--------------------------------Adding Roles Script Complete for All DB----------------------------'
Print '---------------------------------------------------------------------------------------------------'

/*Add Role Members All Databases Script */

Print '-------------------------------Script Role Members All Databases-----------------------------------'
Print '---------------------------------------------------------------------------------------------------'

exec sp_msforeachdb 'Use ? select ''Use '' + ''?'' + CHAR(13) + CHAR(10)+ ''GO''+ CHAR(13) + CHAR(10) +
''exec sp_addrolemember N'''''' + user_name(groupuid) collate database_default + ''''''''+ '',N'''''' + user_name (memberuid) collate database_default + '''''''' + Char(13) + Char(10) + ''GO''
from sysmembers where  user_name (memberuid) <> ''dbo'' order by groupuid'

Print '-----------------------------Adding Role Members Script Complete for All DB------------------------'
Print '---------------------------------------------------------------------------------------------------'

/*Add Alias Login All Databases*/

Print '------------------------------Script Add Alias All Databases---------------------------------------'
Print '---------------------------------------------------------------------------------------------------'

exec sp_msforeachdb 'Use ? select ''Use '' + ''?'' + CHAR(13) + CHAR(10)+ ''GO''
+ CHAR(13) + CHAR(10) + ''if not exists (select * from dbo.sysusers where name = N''''''+''''+ a.name +'''''')'' + Char(13) + Char(10) +
''EXEC sp_addalias N'''''' + substring(a.name , 2, len(a.name)) collate database_default +''''''''+ '',N''''''+ b.name collate database_default +  '''''''' + Char(13) + Char(10) +''GO''
from sysusers a , sysusers b where a.altuid = b.uid and a.isaliased=1'

Print '------------------------------Add Alias Script Complete for All DB---------------------------------'
Print '---------------------------------------------------------------------------------------------------'

Result this to a AllDBPermissions.sql file and keep that file safe somewhere.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s