February 2, 2016 at 10:01 am
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!
February 2, 2016 at 10:47 am
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"
February 2, 2016 at 11:59 am
February 2, 2016 at 3:14 pm
Thanks a lot Perry! Appreciate your help!!
February 2, 2016 at 3:15 pm
Thanks Steve!
February 2, 2016 at 3:37 pm
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"
February 2, 2016 at 3:38 pm
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"
February 2, 2016 at 3:52 pm
Hi Perry,
I'm not able to script out the permissions for Symmetric keys/Certificate.
Thanks!
February 3, 2016 at 1:40 am
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"
February 3, 2016 at 5:02 am
I would like to script out permissions assigned to a user for symmetric key/certificate created for column level encryption. Thanks.
February 3, 2016 at 7:19 am
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"
February 3, 2016 at 10:31 am
Thanks a lot!
February 4, 2016 at 8:12 am
Hi Perry,
Could you please modify to generate scripts for Symmetric/Asymmetric keys as well.
Many thanks!
March 23, 2016 at 10:44 am
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.
September 1, 2016 at 12:01 pm
Awesome script Perry! Is there an easy way to run this on multiple databases?
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy