Script out DB/Object level permissions (GRANT/DENY)

  • Hello Guys,

    Can anyone provide a script to generate DB/Object level permissions which includes GRANT and DENY.

    Unfortunately I lost my script library due to my laptop crash.

    Many thanks in advance!

  • Here's my script, feel free to modify it as required, not that it should need it

    SET NOCOUNT ON

    DECLARE @sql NVARCHAR(MAX)

    DECLARE @majver INT

    DECLARE @minver INT

    DECLARE @build VARCHAR(16)

    SET @sql = ''

    SET @build = CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(16))

    SET @majver = PARSENAME(@build, 4)

    SET @minver = PARSENAME(@build, 2)

    SELECT @sql =

    '--======================================================================================' + CHAR(10) +

    '--==== IMPORTANT: Before executing these scripts check the details to ensure they ====' + CHAR(10) +

    '--==== are valid. For instance when crossing domains ====' + CHAR(10) +

    '--======================================================================================' + CHAR(10)

    PRINT @sql

    SET @sql = ''

    --=======================================================

    --Check the database encrytion state

    --=======================================================

    IF CAST(@majver AS INT) >= 10

    BEGIN

    IF (SELECT count(*) FROM sys.databases WHERE database_id = DB_ID() AND is_encrypted = 1) = 0

    BEGIN

    SELECT @sql = '/*Database ' + QUOTENAME(DB_NAME(DB_ID())) + ' is not TDE protected*/'

    END

    ELSE

    BEGIN

    SELECT @sql =

    CASE

    WHEN encryption_state <> 0 THEN '/*Database ' + QUOTENAME(DB_NAME(DB_ID())) +

    ' is TDE protected, ensure you have a backup of the certificate that the database is protected with,

    including the certificates public and private key passswords*/' + CHAR(13) + CHAR(13) +

    '/*Important: You must create a master key on your new instance first, do this now using the script below.' +

    CHAR(10) + '===================================================================================' +

    CHAR(13) + '*!Don''t forget to change the password before executing!*/' + CHAR(13) + CHAR(13) +

    'CREATE MASTER KEY ENCRYPTION BY PASSWORD = ''P@ssw0rd1''' + CHAR(13)

    WHEN encryption_state = 0 THEN @sql + CHAR(13)

    END

    FROM sys.dm_database_encryption_keys

    WHERE database_id = DB_ID()

    END

    PRINT @sql + CHAR(13) + CHAR(13)

    END

    ELSE IF CAST(@majver AS INT) <= 9

    BEGIN

    PRINT 'before 2008'

    end

    SET @sql = ''

    --========================================================

    --script any certificates in the database

    --========================================================

    IF (SELECT COUNT(*) FROM sys.certificates) = 0

    BEGIN

    SELECT @sql = @sql + '/*No certificates found*/'

    PRINT @sql + CHAR(13) + CHAR(13)

    END

    ELSE

    BEGIN

    SELECT @sql = '/*Scripting all user certificates' + CHAR(10) +

    '===================================================================================*/' + CHAR(13) + CHAR(13)

    SELECT @sql = @sql + 'CREATE CERTIFICATE ' + name +

    ' ENCRYPTION BY PASSWORD = ''P@ssw0rd1''

    WITH SUBJECT = ''' + issuer_name + ''',

    EXPIRY_DATE = ''' + CONVERT(NVARCHAR(25), expiry_date, 120) + '''' + CHAR(13)

    FROM sys.certificates

    PRINT @sql + CHAR(13) + CHAR(13)

    END

    SET @sql = ''

    --======================================================

    --Script the database users

    --======================================================

    SELECT principal_id INTO #users FROM sys.database_principals WHERE type IN ('U', 'G', 'S') AND principal_id > 4

    IF (SELECT COUNT(*) FROM #users) = 0

    BEGIN

    SELECT @sql = @sql + '/*No database users found*/'

    PRINT @sql + CHAR(13) + CHAR(13)

    END

    ELSE

    BEGIN

    SET CONCAT_NULL_YIELDS_NULL OFF

    DECLARE @uid INT

    SELECT @sql = '/*Scripting all database users and schemas' + CHAR(10) +

    '===================================================================================' + CHAR(13) +

    'Note: these are the users found in the database, but they may not all be valid, check them first*/' +

    CHAR(13) + CHAR(13)

    WHILE (SELECT TOP 1 principal_id FROM #users) IS NOT NULL

    BEGIN

    SELECT TOP 1 @uid = principal_id FROM #users

    SELECT @sql = @sql + 'IF (SELECT name FROM sys.database_principals WHERE name = ''' +

    dp.name + ''') IS NULL' + CHAR(13) + 'BEGIN' + CHAR(13) + CHAR(9) +

    'CREATE USER ' + QUOTENAME(dp.name) +

    CASE

    WHEN SUSER_SNAME(dp.sid) IS NULL THEN ' WITHOUT LOGIN'

    ELSE ' FOR LOGIN ' + QUOTENAME(SUSER_SNAME(dp.sid))

    END +

    CASE

    WHEN dp.type <> 'G' AND dp.default_schema_name IS NULL THEN ' WITH DEFAULT_SCHEMA = [dbo]'

    WHEN dp.type IN ('G', 'C', 'K') THEN ''

    ELSE ' WITH DEFAULT_SCHEMA = [' + dp.default_schema_name + ']'

    END + CHAR(13) + 'END'

    FROM sys.database_principals dp LEFT OUTER JOIN

    sys.schemas sch ON dp.principal_id = sch.principal_id

    WHERE dp.principal_id = @uid AND dp.[type] IN ('U', 'G', 'S') AND dp.principal_id > 4

    GROUP BY dp.name, dp.type, dp.sid, dp.default_schema_name

    PRINT @sql + CHAR(10)

    DELETE FROM #users WHERE principal_id = @uid

    SELECT @sql = ''

    END

    DROP TABLE #users

    END

    SELECT @sql = ''

    --========================================================

    --Script any users that are protected by a cert

    --========================================================

    IF (SELECT count(*) FROM sys.database_principals dp INNER JOIN sys.certificates c ON dp.sid = c.sid

    WHERE dp.type = 'C' AND dp.principal_id > 4) = 0

    BEGIN

    SELECT @sql = @sql + '/*No certificated users found*/'

    PRINT @sql + CHAR(13) + CHAR(13)

    END

    ELSE

    BEGIN

    SELECT @sql = '/*Scripting all certificated database users' + CHAR(10) +

    '===================================================================================*/' + CHAR(13) + CHAR(9)

    SELECT @sql = @sql + 'CREATE USER ' + QUOTENAME(dp.name) + ' FOR CERTIFICATE ' + c.name

    FROM sys.database_principals dp INNER JOIN sys.certificates c ON dp.sid = c.sid

    WHERE dp.type = 'C' AND dp.principal_id > 4

    PRINT @sql + CHAR(13) + CHAR(13)

    END

    SET @sql = ''

    --========================================================

    --script database roles from the database

    --========================================================

    IF (SELECT COUNT(*) FROM sys.database_principals WHERE type = 'R' AND is_fixed_role <> 1 AND principal_id > 4) = 0

    BEGIN

    SELECT @sql = @sql + '/*No database roles found*/'

    PRINT @sql + CHAR(13) + CHAR(13)

    END

    ELSE

    BEGIN

    SELECT @sql = '/*Scripting all database roles' + CHAR(10) +

    '===================================================================================*/' + CHAR(13)

    SELECT @sql = @sql + 'IF (SELECT name FROM sys.database_principals WHERE name = ''' + dp.name +

    ''' AND type = ''R'') IS NULL' + CHAR(13) + 'BEGIN' + CHAR(13) + CHAR(9) +

    'CREATE ROLE ' + QUOTENAME(dp.name) + ' AUTHORIZATION ' + QUOTENAME(dp2.name) + CHAR(13) + 'END' + CHAR(13)

    FROM sys.database_principals dp INNER JOIN sys.database_principals dp2

    ON dp.owning_principal_id = dp2.principal_id

    WHERE dp.type = 'R' AND dp.is_fixed_role <> 1 AND dp.principal_id > 4

    PRINT @sql + CHAR(13) + CHAR(13)

    END

    SET @sql = ''

    --=======================================================

    --script all schema permissions

    --=======================================================

    SELECT @sql = '/*Scripting all user schema permissions' + CHAR(10) +

    '===================================================================================*/' + CHAR(13)

    --Script the permission grants on the schemas

    SELECT @sql = @sql + CHAR(13) + dp.state_desc COLLATE latin1_general_ci_as + ' ' +

    dp.permission_name + ' ON ' + dp.class_desc + '::' + QUOTENAME(sch.name) +

    ' TO ' + QUOTENAME(dp2.name) + ' AS ' + QUOTENAME(dp3.name)

    FROM sys.database_permissions dp

    INNER JOIN sys.schemas sch ON dp.grantor_principal_id = sch.principal_id

    INNER JOIN sys.database_principals dp2 ON dp.grantee_principal_id = dp2.principal_id

    INNER JOIN sys.database_principals dp3 ON dp.grantor_principal_id = dp3.principal_id

    WHERE dp.class = 3

    PRINT @sql + CHAR(13) + CHAR(13)

    SET @sql = ''

    --=========================================================

    --script Application roles from the database

    --=========================================================

    IF (SELECT COUNT(*) FROM sys.database_principals WHERE type = 'A') = 0

    BEGIN

    SELECT @sql = @sql + '/*No application roles found*/'

    PRINT @sql + CHAR(13) + CHAR(13)

    END

    ELSE

    BEGIN

    SELECT @sql = '/*Scripting all application roles' + CHAR(10) +

    '===================================================================================*/' + CHAR(13)

    SELECT @sql = @sql + 'CREATE APPLICATION ROLE ' + dp.name + ' WITH DEFAULT_SCHEMA = ' +

    QUOTENAME(dp.default_schema_name) + ', PASSWORD = N''P@ssw0rd1''' + CHAR(10)

    FROM sys.database_principals dp

    WHERE dp.type = 'A' AND dp.is_fixed_role <> 1 AND dp.principal_id > 4

    PRINT @sql + CHAR(13) + CHAR(13)

    END

    SET @sql = ''

    --===============================================================

    --got the roles so now we need to get any nested role permissions

    --===============================================================

    IF (SELECT COUNT(*) from sys.database_principals dp inner join sys.database_role_members drm

    ON dp.principal_id = drm.member_principal_id inner join sys.database_principals dp2

    ON drm.role_principal_id = dp2.principal_id WHERE dp.type = 'R') = 0

    BEGIN

    SELECT @sql = + '/*No nested roles found*/'

    PRINT @sql + CHAR(13) + CHAR(13)

    END

    ELSE

    BEGIN

    SELECT @sql = '/*Scripting all nested roles' + CHAR(10) +

    '===================================================================================*/' + CHAR(13)

    SELECT @sql = @sql + 'EXEC sp_addrolemember ''' + dp2.name + ''', ''' + dp.name + '''' + CHAR(10)

    FROM sys.database_principals dp

    INNER JOIN sys.database_role_members drm

    ON dp.principal_id = drm.member_principal_id

    INNER JOIN sys.database_principals dp2

    ON drm.role_principal_id = dp2.principal_id

    WHERE dp.type = 'R'

    PRINT @sql + CHAR(13) + CHAR(13)

    END

    SET @sql = ''

    --================================================================

    --Scripting all user connection grants

    --================================================================

    IF(SELECT COUNT(*) FROM sys.database_permissions dpm INNER JOIN sys.database_principals dp

    ON dpm.grantee_principal_id = dp.principal_id WHERE dp.principal_id > 4 AND dpm.class = 0 AND dpm.type = 'CO') = 0

    BEGIN

    SELECT @sql = + '/*No database connection GRANTS found*/'

    PRINT @sql + CHAR(13) + CHAR(13)

    END

    ELSE

    BEGIN

    SELECT @sql = '/*Scripting all database and connection GRANTS' + CHAR(10) +

    '===================================================================================*/' + CHAR(13)

    SELECT @sql = @sql + dpm.state_desc COLLATE Latin1_General_CI_AS + ' ' +

    dpm.permission_name COLLATE Latin1_General_CI_AS + ' TO ' + QUOTENAME(dp.name) + CHAR(13)

    FROM sys.database_permissions dpm INNER JOIN sys.database_principals dp

    ON dpm.grantee_principal_id = dp.principal_id

    WHERE dp.principal_id > 4 AND dpm.class = 0 --AND dpm.type = 'CO'

    PRINT @sql + CHAR(13) + CHAR(13)

    END

    SET @sql = ''

    --=================================================================

    --Now all the object level permissions

    --=================================================================

    IF(SELECTCOUNT(*) FROM sys.database_permissions dbpe INNER JOIN sys.database_principals dbpr

    ON dbpr.principal_id = dbpe.grantee_principal_id INNER JOIN sys.objects obj

    ON dbpe.major_id = obj.object_id WHERE obj.type NOT IN ('IT','S','X')) = 0

    BEGIN

    SELECT @sql = + '/*No database user object GRANTS found*/'

    PRINT @sql + CHAR(13) + CHAR(13)

    END

    ELSE

    BEGIN

    SELECT @sql = '/*Scripting all database user object GRANTS' + CHAR(10) +

    '===================================================================================*/' + CHAR(13)

    PRINT @sql --+ CHAR(10)

    SET @sql = ''

    IF OBJECT_ID('tempdb..#objgrants') IS NOT NULL

    BEGIN

    DROP TABLE #objgrants

    END

    CREATE TABLE #objgrants(

    state_descVARCHAR(60)

    , perm_nameNVARCHAR(128)

    , sch_nameNVARCHAR(128)

    , maj_IDNVARCHAR(128)

    , nameNVARCHAR(128)

    , pr_nameNVARCHAR(128)

    )

    DECLARE @state_desc VARCHAR(60)

    DECLARE @perm_name NVARCHAR(128), @sch_name NVARCHAR(128), @maj_ID NVARCHAR(128)

    DECLARE @name NVARCHAR(128), @pr_name NVARCHAR(128)

    INSERT INTO #objgrants

    SELECT CASE dbpe.[state] WHEN 'W' THEN 'GRANT'

    ELSE dbpe.state_desc COLLATE Latin1_General_CI_AS

    END AS [state_desc]

    , dbpe.permission_name COLLATE Latin1_General_CI_AS AS perm_name

    , sch.name AS sch_name

    , OBJECT_NAME(dbpe.major_id) AS maj_ID

    , dbpr.name AS name

    , CASE dbpe.[state] WHEN 'W' THEN '] WITH GRANT OPTION'

    ELSE ']' END AS pr_name

    FROM sys.database_permissions dbpe INNER JOIN sys.database_principals dbpr

    ON dbpr.principal_id = dbpe.grantee_principal_id

    INNER JOIN sys.objects obj ON dbpe.major_id = obj.object_id

    INNER JOIN sys.schemas sch ON obj.schema_id = sch.schema_id

    WHERE obj.type NOT IN ('IT','S','X')

    ORDER BY dbpr.name, obj.name

    WHILE (SELECT COUNT(*) FROM #objgrants) > 0

    BEGIN

    SELECT TOP 1 @state_desc = state_desc, @perm_name = perm_name, @sch_name = sch_name,

    @maj_ID = maj_ID, @name = name, @pr_name = pr_name FROM #objgrants

    SELECT @sql = @sql + @state_desc + ' ' + @perm_name +

    ' ON [' + @sch_name + '].[' + @maj_ID + '] TO [' + @name + @pr_name

    PRINT @sql

    SET @sql = ''

    DELETE FROM #objgrants WHERE state_desc = @state_desc AND perm_name = @perm_name

    AND sch_name = @sch_name AND maj_ID = @maj_ID AND name = @name AND pr_name = @pr_name

    END

    PRINT CHAR(13)

    DROP TABLE #objgrants

    END

    SET @sql = ''

    --=================================================================

    --Now script all the database roles the user have permissions to

    --=================================================================

    IF(SELECT COUNT(*) FROM sys.database_principals dp

    INNER JOIN sys.database_role_members drm ON dp.principal_id = drm.role_principal_id

    INNER JOIN sys.database_principals dp2 ON drm.member_principal_id = dp2.principal_id

    WHERE dp2.principal_id > 4 AND dp2.type <> 'R') = 0

    BEGIN

    SELECT @sql = + '/*No database user role GRANTS found*/'

    PRINT @sql + CHAR(13) + CHAR(13)

    END

    ELSE

    BEGIN

    SELECT @sql = '/*Scripting all database user role permissions' + CHAR(10) +

    '===================================================================================*/' + CHAR(13)

    SELECT @sql = @sql + 'EXEC sp_addrolemember ''' + dp.name + ''', ''' + dp2.name + '''' + CHAR(13)

    FROM sys.database_principals dp

    INNER JOIN sys.database_role_members drm ON dp.principal_id = drm.role_principal_id

    INNER JOIN sys.database_principals dp2 ON drm.member_principal_id = dp2.principal_id

    WHERE dp2.principal_id > 4 AND dp2.type <> 'R'

    PRINT @sql + CHAR(13) + CHAR(13)

    END

    SET @sql = ''

    SELECT @sql = '--Finished!' + CHAR(13) + '--Please ensure you check the script output before executing' +

    CHAR(13) + '--against your target database.'

    PRINT @sql

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • There are also lots of scripts on this site in the script library[/url].

  • Thanks a lot Perry! Appreciate your help!!

  • Thanks Steve!

  • Steve Jones - SSC Editor (2/2/2016)


    There are also lots of scripts on this site in the script library[/url].

    Yeah but mine is super awesome ??

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • SQL!$@w$0ME (2/2/2016)


    Thanks a lot Perry! Appreciate your help!!

    You're welcome

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Hi Perry,

    I'm not able to script out the permissions for Symmetric keys/Certificate.

    Thanks!

  • a cert is an asymmetric key, what is it you are expecting to see

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • I would like to script out permissions assigned to a user for symmetric key/certificate created for column level encryption. Thanks.

  • this gets certs too, with a little work you can expand to get symm and asymm keys too

    SET NOCOUNT ON

    DECLARE @sql NVARCHAR(MAX)

    DECLARE @majver INT

    DECLARE @minver INT

    DECLARE @build VARCHAR(16)

    SET @sql = ''

    SET @build = CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(16))

    SET @majver = PARSENAME(@build, 4)

    SET @minver = PARSENAME(@build, 2)

    SELECT @sql =

    '--======================================================================================' + CHAR(10) +

    '--==== IMPORTANT: Before executing these scripts check the details to ensure they ====' + CHAR(10) +

    '--==== are valid. For instance when crossing domains ====' + CHAR(10) +

    '--======================================================================================' + CHAR(10)

    PRINT @sql

    SET @sql = ''

    --=======================================================

    --Check the database encrytion state

    --=======================================================

    IF CAST(@majver AS INT) >= 10

    BEGIN

    IF (SELECT count(*) FROM sys.databases WHERE database_id = DB_ID() AND is_encrypted = 1) = 0

    BEGIN

    SELECT @sql = '/*Database ' + QUOTENAME(DB_NAME(DB_ID())) + ' is not TDE protected*/'

    END

    ELSE

    BEGIN

    SELECT @sql =

    CASE

    WHEN encryption_state <> 0 THEN '/*Database ' + QUOTENAME(DB_NAME(DB_ID())) +

    ' is TDE protected, ensure you have a backup of the certificate that the database is protected with,

    including the certificates public and private key passswords*/' + CHAR(13) + CHAR(13) +

    '/*Important: You must create a master key on your new instance first, do this now using the script below.' +

    CHAR(10) + '===================================================================================' +

    CHAR(13) + '*!Don''t forget to change the password before executing!*/' + CHAR(13) + CHAR(13) +

    'CREATE MASTER KEY ENCRYPTION BY PASSWORD = ''P@ssw0rd1''' + CHAR(13)

    WHEN encryption_state = 0 THEN @sql + CHAR(13)

    END

    FROM sys.dm_database_encryption_keys

    WHERE database_id = DB_ID()

    END

    PRINT @sql + CHAR(13) + CHAR(13)

    END

    ELSE IF CAST(@majver AS INT) <= 9

    BEGIN

    PRINT 'before 2008'

    end

    SET @sql = ''

    --========================================================

    --script any certificates in the database

    --========================================================

    IF (SELECT COUNT(*) FROM sys.certificates) = 0

    BEGIN

    SELECT @sql = @sql + '/*No certificates found*/'

    PRINT @sql + CHAR(13) + CHAR(13)

    END

    ELSE

    BEGIN

    SELECT @sql = '/*Scripting all user certificates' + CHAR(10) +

    '===================================================================================*/' + CHAR(13) + CHAR(13)

    SELECT @sql = @sql + 'CREATE CERTIFICATE ' + name +

    ' ENCRYPTION BY PASSWORD = ''P@ssw0rd1''

    WITH SUBJECT = ''' + issuer_name + ''',

    START_DATE = ''' + CONVERT(NVARCHAR(25), start_date, 120) +

    ''', EXPIRY_DATE = ''' + CONVERT(NVARCHAR(25), expiry_date, 120) +

    '''' + CHAR(13)

    FROM sys.certificates

    PRINT @sql + CHAR(13) + CHAR(13)

    END

    SET @sql = ''

    --======================================================

    --Script the database users

    --======================================================

    SELECT principal_id INTO #users FROM sys.database_principals WHERE type IN ('U', 'G', 'S') AND principal_id > 4

    IF (SELECT COUNT(*) FROM #users) = 0

    BEGIN

    SELECT @sql = @sql + '/*No database users found*/'

    PRINT @sql + CHAR(13) + CHAR(13)

    END

    ELSE

    BEGIN

    SET CONCAT_NULL_YIELDS_NULL OFF

    DECLARE @uid INT

    SELECT @sql = '/*Scripting all database users and schemas' + CHAR(10) +

    '===================================================================================' + CHAR(13) +

    'Note: these are the users found in the database, but they may not all be valid, check them first*/' +

    CHAR(13) + CHAR(13)

    WHILE (SELECT TOP 1 principal_id FROM #users) IS NOT NULL

    BEGIN

    SELECT TOP 1 @uid = principal_id FROM #users

    SELECT @sql = @sql + 'IF (SELECT name FROM sys.database_principals WHERE name = ''' +

    dp.name + ''') IS NULL' + CHAR(13) + 'BEGIN' + CHAR(13) + CHAR(9) +

    'CREATE USER ' + QUOTENAME(dp.name) +

    CASE

    WHEN SUSER_SNAME(dp.sid) IS NULL THEN ' WITHOUT LOGIN'

    ELSE ' FOR LOGIN ' + QUOTENAME(SUSER_SNAME(dp.sid))

    END +

    CASE

    WHEN dp.type <> 'G' AND dp.default_schema_name IS NULL THEN ' WITH DEFAULT_SCHEMA = [dbo]'

    WHEN dp.type IN ('G', 'C', 'K') THEN ''

    ELSE ' WITH DEFAULT_SCHEMA = [' + dp.default_schema_name + ']'

    END + CHAR(13) + 'END'

    FROM sys.database_principals dp LEFT OUTER JOIN

    sys.schemas sch ON dp.principal_id = sch.principal_id

    WHERE dp.principal_id = @uid AND dp.[type] IN ('U', 'G', 'S') AND dp.principal_id > 4

    GROUP BY dp.name, dp.type, dp.sid, dp.default_schema_name

    PRINT @sql + CHAR(10)

    DELETE FROM #users WHERE principal_id = @uid

    SELECT @sql = ''

    END

    DROP TABLE #users

    END

    SELECT @sql = ''

    --========================================================

    --Script any users that are protected by a cert

    --========================================================

    IF (SELECT count(*) FROM sys.database_principals dp INNER JOIN sys.certificates c ON dp.sid = c.sid

    WHERE dp.type = 'C' AND dp.principal_id > 4) = 0

    BEGIN

    SELECT @sql = @sql + '/*No certificated users found*/'

    PRINT @sql + CHAR(13) + CHAR(13)

    END

    ELSE

    BEGIN

    SELECT @sql = '/*Scripting all certificated database users' + CHAR(10) +

    '===================================================================================*/' + CHAR(13) + CHAR(9)

    SELECT @sql = @sql + 'CREATE USER ' + QUOTENAME(dp.name) + ' FOR CERTIFICATE ' + c.name

    FROM sys.database_principals dp INNER JOIN sys.certificates c ON dp.sid = c.sid

    WHERE dp.type = 'C' AND dp.principal_id > 4

    PRINT @sql + CHAR(13) + CHAR(13)

    END

    SET @sql = ''

    --========================================================

    --script database roles from the database

    --========================================================

    IF (SELECT COUNT(*) FROM sys.database_principals WHERE type = 'R' AND is_fixed_role <> 1 AND principal_id > 4) = 0

    BEGIN

    SELECT @sql = @sql + '/*No database roles found*/'

    PRINT @sql + CHAR(13) + CHAR(13)

    END

    ELSE

    BEGIN

    SELECT @sql = '/*Scripting all database roles' + CHAR(10) +

    '===================================================================================*/' + CHAR(13)

    SELECT @sql = @sql + 'IF (SELECT name FROM sys.database_principals WHERE name = ''' + dp.name +

    ''' AND type = ''R'') IS NULL' + CHAR(13) + 'BEGIN' + CHAR(13) + CHAR(9) +

    'CREATE ROLE ' + QUOTENAME(dp.name) + ' AUTHORIZATION ' + QUOTENAME(dp2.name) + CHAR(13) + 'END' + CHAR(13)

    FROM sys.database_principals dp INNER JOIN sys.database_principals dp2

    ON dp.owning_principal_id = dp2.principal_id

    WHERE dp.type = 'R' AND dp.is_fixed_role <> 1 AND dp.principal_id > 4

    PRINT @sql + CHAR(13) + CHAR(13)

    END

    SET @sql = ''

    --=======================================================

    --script all schema permissions

    --=======================================================

    SELECT @sql = '/*Scripting all user schema permissions' + CHAR(10) +

    '===================================================================================*/' + CHAR(13)

    --Script the permission grants on the schemas

    SELECT @sql = @sql + CHAR(13) + dp.state_desc COLLATE latin1_general_ci_as + ' ' +

    dp.permission_name + ' ON ' + dp.class_desc + '::' + QUOTENAME(sch.name) +

    ' TO ' + QUOTENAME(dp2.name) + ' AS ' + QUOTENAME(dp3.name)

    FROM sys.database_permissions dp

    INNER JOIN sys.schemas sch ON dp.grantor_principal_id = sch.principal_id

    INNER JOIN sys.database_principals dp2 ON dp.grantee_principal_id = dp2.principal_id

    INNER JOIN sys.database_principals dp3 ON dp.grantor_principal_id = dp3.principal_id

    WHERE dp.class = 3

    PRINT @sql + CHAR(13) + CHAR(13)

    SET @sql = ''

    --=========================================================

    --script Application roles from the database

    --=========================================================

    IF (SELECT COUNT(*) FROM sys.database_principals WHERE type = 'A') = 0

    BEGIN

    SELECT @sql = @sql + '/*No application roles found*/'

    PRINT @sql + CHAR(13) + CHAR(13)

    END

    ELSE

    BEGIN

    SELECT @sql = '/*Scripting all application roles' + CHAR(10) +

    '===================================================================================*/' + CHAR(13)

    SELECT @sql = @sql + 'CREATE APPLICATION ROLE ' + dp.name + ' WITH DEFAULT_SCHEMA = ' +

    QUOTENAME(dp.default_schema_name) + ', PASSWORD = N''P@ssw0rd1''' + CHAR(10)

    FROM sys.database_principals dp

    WHERE dp.type = 'A' AND dp.is_fixed_role <> 1 AND dp.principal_id > 4

    PRINT @sql + CHAR(13) + CHAR(13)

    END

    SET @sql = ''

    --===============================================================

    --got the roles so now we need to get any nested role permissions

    --===============================================================

    IF (SELECT COUNT(*) from sys.database_principals dp inner join sys.database_role_members drm

    ON dp.principal_id = drm.member_principal_id inner join sys.database_principals dp2

    ON drm.role_principal_id = dp2.principal_id WHERE dp.type = 'R') = 0

    BEGIN

    SELECT @sql = + '/*No nested roles found*/'

    PRINT @sql + CHAR(13) + CHAR(13)

    END

    ELSE

    BEGIN

    SELECT @sql = '/*Scripting all nested roles' + CHAR(10) +

    '===================================================================================*/' + CHAR(13)

    SELECT @sql = @sql + 'EXEC sp_addrolemember ''' + dp2.name + ''', ''' + dp.name + '''' + CHAR(10)

    FROM sys.database_principals dp

    INNER JOIN sys.database_role_members drm

    ON dp.principal_id = drm.member_principal_id

    INNER JOIN sys.database_principals dp2

    ON drm.role_principal_id = dp2.principal_id

    WHERE dp.type = 'R'

    PRINT @sql + CHAR(13) + CHAR(13)

    END

    SET @sql = ''

    --================================================================

    --Scripting all user connection grants

    --================================================================

    IF(SELECT COUNT(*) FROM sys.database_permissions dpm INNER JOIN sys.database_principals dp

    ON dpm.grantee_principal_id = dp.principal_id WHERE dp.principal_id > 4 AND dpm.class = 0 AND dpm.type = 'CO') = 0

    BEGIN

    SELECT @sql = + '/*No database connection GRANTS found*/'

    PRINT @sql + CHAR(13) + CHAR(13)

    END

    ELSE

    BEGIN

    SELECT @sql = '/*Scripting all database and connection GRANTS' + CHAR(10) +

    '===================================================================================*/' + CHAR(13)

    SELECT @sql = @sql + dpm.state_desc COLLATE Latin1_General_CI_AS + ' ' +

    dpm.permission_name COLLATE Latin1_General_CI_AS + ' TO ' + QUOTENAME(dp.name) + CHAR(13)

    FROM sys.database_permissions dpm INNER JOIN sys.database_principals dp

    ON dpm.grantee_principal_id = dp.principal_id

    WHERE dp.principal_id > 4 AND dpm.class = 0 --AND dpm.type = 'CO'

    PRINT @sql + CHAR(13) + CHAR(13)

    END

    SET @sql = ''

    --=================================================================

    --Now all the object level permissions

    --=================================================================

    IF(SELECTCOUNT(*) FROM sys.database_permissions dbpe INNER JOIN sys.database_principals dbpr

    ON dbpr.principal_id = dbpe.grantee_principal_id INNER JOIN sys.objects obj

    ON dbpe.major_id = obj.object_id WHERE obj.type NOT IN ('IT','S','X')) = 0

    BEGIN

    SELECT @sql = + '/*No database user object GRANTS found*/'

    PRINT @sql + CHAR(13) + CHAR(13)

    END

    ELSE

    BEGIN

    SELECT @sql = '/*Scripting all database user object GRANTS' + CHAR(10) +

    '===================================================================================*/' + CHAR(13)

    PRINT @sql --+ CHAR(10)

    SET @sql = ''

    IF OBJECT_ID('tempdb..#objgrants') IS NOT NULL

    BEGIN

    DROP TABLE #objgrants

    END

    CREATE TABLE #objgrants(

    state_descVARCHAR(60)

    , perm_nameNVARCHAR(128)

    , sch_nameNVARCHAR(128)

    , maj_IDNVARCHAR(128)

    , nameNVARCHAR(128)

    , pr_nameNVARCHAR(128)

    )

    DECLARE @state_desc VARCHAR(60)

    DECLARE @perm_name NVARCHAR(128), @sch_name NVARCHAR(128), @maj_ID NVARCHAR(128)

    DECLARE @name NVARCHAR(128), @pr_name NVARCHAR(128)

    INSERT INTO #objgrants

    SELECT CASE dbpe.[state] WHEN 'W' THEN 'GRANT'

    ELSE dbpe.state_desc COLLATE Latin1_General_CI_AS

    END AS [state_desc]

    , dbpe.permission_name COLLATE Latin1_General_CI_AS AS perm_name

    , sch.name AS sch_name

    , OBJECT_NAME(dbpe.major_id) AS maj_ID

    , dbpr.name AS name

    , CASE dbpe.[state] WHEN 'W' THEN '] WITH GRANT OPTION'

    ELSE ']' END AS pr_name

    FROM sys.database_permissions dbpe INNER JOIN sys.database_principals dbpr

    ON dbpr.principal_id = dbpe.grantee_principal_id

    INNER JOIN sys.objects obj ON dbpe.major_id = obj.object_id

    INNER JOIN sys.schemas sch ON obj.schema_id = sch.schema_id

    WHERE obj.type NOT IN ('IT','S','X')

    ORDER BY dbpr.name, obj.name

    WHILE (SELECT COUNT(*) FROM #objgrants) > 0

    BEGIN

    SELECT TOP 1 @state_desc = state_desc, @perm_name = perm_name, @sch_name = sch_name,

    @maj_ID = maj_ID, @name = name, @pr_name = pr_name FROM #objgrants

    SELECT @sql = @sql + @state_desc + ' ' + @perm_name +

    ' ON [' + @sch_name + '].[' + @maj_ID + '] TO [' + @name + @pr_name

    PRINT @sql

    SET @sql = ''

    DELETE FROM #objgrants WHERE state_desc = @state_desc AND perm_name = @perm_name

    AND sch_name = @sch_name AND maj_ID = @maj_ID AND name = @name AND pr_name = @pr_name

    END

    PRINT CHAR(13)

    DROP TABLE #objgrants

    END

    SET @sql = ''

    --=================================================================

    --Script any user grants to certificates

    --=================================================================

    IF(SELECTCOUNT(*) FROM sys.database_permissions dbpe

    WHERE class = 25) <= 0

    BEGIN

    SELECT @sql = + '/*No certificate user object GRANTS found*/'

    PRINT @sql + CHAR(13) + CHAR(13)

    END

    ELSE

    BEGIN

    SELECT @sql = '/*Scripting all certificate user object GRANTS' + CHAR(10) +

    '===================================================================================*/' + CHAR(13)

    PRINT @sql --+ CHAR(10)

    SET @sql = ''

    IF OBJECT_ID('tempdb..#certgrants') IS NOT NULL

    BEGIN

    DROP TABLE #certgrants

    END

    CREATE TABLE #certgrants(

    state_descVARCHAR(60)

    , perm_nameNVARCHAR(128)

    , class_descNVARCHAR(128)

    , cert_nameNVARCHAR(128)

    , nameNVARCHAR(128)

    , pr_nameNVARCHAR(128)

    )

    DECLARE @state_desc2 VARCHAR(60)

    DECLARE @perm_name2 NVARCHAR(128), @c_name2 NVARCHAR(128)

    DECLARE @name2 NVARCHAR(128), @pr_name2 NVARCHAR(128), @c_desc2 NVARCHAR(128)

    INSERT INTO #certgrants

    SELECT CASE dbpe.[state] WHEN 'W' THEN 'GRANT'

    ELSE dbpe.state_desc COLLATE Latin1_General_CI_AS

    END as state_desc

    , dbpe.permission_name COLLATE Latin1_General_CI_AS AS perm_name

    , dbpe.class_desc AS class_desc

    , c.name AS cert_name

    , dbpr.name AS name

    ,CASE dbpe.[state] WHEN 'W' THEN '] WITH GRANT OPTION'

    ELSE ']' END AS pr_name

    FROM sys.database_permissions dbpe INNER JOIN sys.database_principals dbpr

    ON dbpr.principal_id = dbpe.grantee_principal_id

    INNER JOIN sys.certificates c ON dbpe.major_id = c.certificate_id

    WHERE dbpe.class = 25

    ORDER BY dbpr.name

    WHILE (SELECT COUNT(*) FROM #certgrants) > 0

    BEGIN

    SELECT TOP 1 @state_desc2 = state_desc, @c_name2 = cert_name, @perm_name2 = perm_name,

    @c_desc2 = class_desc, @name2 = name, @pr_name2 = pr_name FROM #certgrants

    SELECT @sql = @sql + @state_desc2 + ' ' + @perm_name2 +

    ' ON ' + @c_desc2 + '::[' + @c_name2 + '] TO [' + @name2 + @pr_name2

    PRINT @sql

    SET @sql = ''

    DELETE FROM #certgrants WHERE state_desc = @state_desc2 AND cert_name = @c_name2

    AND perm_name = @perm_name2 AND class_desc = @c_desc2

    AND name = @name2 AND pr_name = @pr_name2

    END

    PRINT CHAR(13)

    DROP TABLE #certgrants

    END

    SET @sql = ''

    --=================================================================

    --Now script all the database roles the user have permissions to

    --=================================================================

    IF(SELECT COUNT(*) FROM sys.database_principals dp

    INNER JOIN sys.database_role_members drm ON dp.principal_id = drm.role_principal_id

    INNER JOIN sys.database_principals dp2 ON drm.member_principal_id = dp2.principal_id

    WHERE dp2.principal_id > 4 AND dp2.type <> 'R') = 0

    BEGIN

    SELECT @sql = + '/*No database user role GRANTS found*/'

    PRINT @sql + CHAR(13) + CHAR(13)

    END

    ELSE

    BEGIN

    SELECT @sql = '/*Scripting all database user role permissions' + CHAR(10) +

    '===================================================================================*/' + CHAR(13)

    SELECT @sql = @sql + 'EXEC sp_addrolemember ''' + dp.name + ''', ''' + dp2.name + '''' + CHAR(13)

    FROM sys.database_principals dp

    INNER JOIN sys.database_role_members drm ON dp.principal_id = drm.role_principal_id

    INNER JOIN sys.database_principals dp2 ON drm.member_principal_id = dp2.principal_id

    WHERE dp2.principal_id > 4 AND dp2.type <> 'R'

    PRINT @sql + CHAR(13) + CHAR(13)

    END

    SET @sql = ''

    SELECT @sql = '--Finished!' + CHAR(13) + '--Please ensure you check the script output before executing' +

    CHAR(13) + '--against your target database.'

    PRINT @sql

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Thanks a lot!

  • Hi Perry,

    Could you please modify to generate scripts for Symmetric/Asymmetric keys as well.

    Many thanks!

  • Hi Perry,

    Unfortunately its not scripting out all users. script end like this. (missing script)

    EXEC sp_addrolememb

    --Finished!

    --Please ensure you check the script output before executing

    --against your target database.

  • Awesome script Perry! Is there an easy way to run this on multiple databases?

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply