Run the below script on existing server to extract DB Users, Roles, Object Level Permissions.
SET NOCOUNT ON
SELECT ScriptDateTime,scripts AS 'Scripts'
FROM (SELECT Getdate() AS ScriptDateTime,
'CREATE USER [' + DP.name + '] FOR LOGIN ['
+ SP.name + ']' + CASE WHEN DP.type_desc != 'WINDOWS_GROUP' THEN
' WITH DEFAULT_SCHEMA = ['+Isnull(DP.default_schema_name, 'dbo')+']'
--+ CHAR(13)+CHAR(10)+'GO'
ELSE ''--+ CHAR(13)+CHAR(10)+'GO'
END AS Scripts
FROM sys.database_principals DP, sys.server_principals SP
WHERE SP.sid = DP.sid
AND DP.name NOT IN ( 'DBO', 'GUEST', 'INFORMATION_SCHEMA', 'SYS',
'PUBLIC', 'DB_OWNER', 'DB_ACCESSADMIN', 'DB_SECURITYADMIN',
'DB_DDLADMIN', 'DB_BACKUPOPERATOR', 'DB_DATAREADER' ,
'DB_DATAWRITER', 'DB_DENYDATAREADER', 'DB_DENYDATAWRITER', 'DB_X' )
UNION
--Extracting Database Roles Permissions for the DB USers.
SELECT Getdate() AS ScriptDateTime,
'EXEC sp_addrolemember @rolename ='
+ Space(1)
+ Quotename(User_name(rm.role_principal_id), '''')
+ ', @membername =' + Space(1)
+ Quotename(User_name(rm.member_principal_id), '''')
--+ CHAR(13)+CHAR(10)+'GO'
AS '--Role Memberships'
FROM sys.database_role_members AS rm
WHERE User_name(rm.role_principal_id)
+ User_name(rm.member_principal_id) != 'DB_OWNERDBO'
--ORDER BY rm.role_principal_id ASC
UNION
--Extracting object level permissions
SELECT Getdate() AS ScriptDateTime,
CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END +
Space
(1) +
perm.permission_name + Space(1)
+ 'ON ' + Quotename(User_name(obj.schema_id))
+ '.' + Quotename(obj.name) + CASE WHEN cl.column_id IS NULL THEN Space(
0
) ELSE
'(' + Quotename(cl.name) + ')' END + Space(1) + 'TO'
+ Space(1)
+ Quotename(User_name(usr.principal_id)) COLLATE database_default + CASE
WHEN perm.state <> 'W' THEN Space(0)
ELSE Space(1) + 'WITH GRANT OPTION'
END
--+ CHAR(13)+CHAR(10)+'GO'
AS '--Object Level Permissions'
FROM sys.database_permissions AS perm
INNER JOIN sys.objects AS obj
ON perm.major_id = obj.[object_id]
INNER JOIN sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
LEFT JOIN sys.columns AS cl
ON cl.column_id = perm.minor_id
AND cl.[object_id] = perm.major_id
--ORDER BY perm.permission_name ASC, perm.state_desc ASC
UNION
--Extracting database level permissions
SELECT Getdate() AS ScriptDateTime,
CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END +
Space
(1) +
perm.permission_name + Space(1)
+ Space(1) + 'TO' + Space(1)
+ Quotename(User_name(usr.principal_id)) COLLATE database_default + CASE
WHEN perm.state <> 'W' THEN Space(0)
ELSE Space(1) + 'WITH GRANT OPTION'
END
--+ CHAR(13)+CHAR(10)+'GO'
AS '--Database Level Permissions'
FROM sys.database_permissions AS perm
INNER JOIN sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
WHERE perm.major_id = 0
AND ( permission_name
+ User_name(usr.principal_id) != 'CONNECTDBO' )
--ORDER BY perm.permission_name ASC, perm.state_desc ASC
) AS UserScripts
ORDER BY scripts
SET NOCOUNT ON
SELECT ScriptDateTime,scripts AS 'Scripts'
FROM (SELECT Getdate() AS ScriptDateTime,
'CREATE USER [' + DP.name + '] FOR LOGIN ['
+ SP.name + ']' + CASE WHEN DP.type_desc != 'WINDOWS_GROUP' THEN
' WITH DEFAULT_SCHEMA = ['+Isnull(DP.default_schema_name, 'dbo')+']'
--+ CHAR(13)+CHAR(10)+'GO'
ELSE ''--+ CHAR(13)+CHAR(10)+'GO'
END AS Scripts
FROM sys.database_principals DP, sys.server_principals SP
WHERE SP.sid = DP.sid
AND DP.name NOT IN ( 'DBO', 'GUEST', 'INFORMATION_SCHEMA', 'SYS',
'PUBLIC', 'DB_OWNER', 'DB_ACCESSADMIN', 'DB_SECURITYADMIN',
'DB_DDLADMIN', 'DB_BACKUPOPERATOR', 'DB_DATAREADER' ,
'DB_DATAWRITER', 'DB_DENYDATAREADER', 'DB_DENYDATAWRITER', 'DB_X' )
UNION
--Extracting Database Roles Permissions for the DB USers.
SELECT Getdate() AS ScriptDateTime,
'EXEC sp_addrolemember @rolename ='
+ Space(1)
+ Quotename(User_name(rm.role_principal_id), '''')
+ ', @membername =' + Space(1)
+ Quotename(User_name(rm.member_principal_id), '''')
--+ CHAR(13)+CHAR(10)+'GO'
AS '--Role Memberships'
FROM sys.database_role_members AS rm
WHERE User_name(rm.role_principal_id)
+ User_name(rm.member_principal_id) != 'DB_OWNERDBO'
--ORDER BY rm.role_principal_id ASC
UNION
--Extracting object level permissions
SELECT Getdate() AS ScriptDateTime,
CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END +
Space
(1) +
perm.permission_name + Space(1)
+ 'ON ' + Quotename(User_name(obj.schema_id))
+ '.' + Quotename(obj.name) + CASE WHEN cl.column_id IS NULL THEN Space(
0
) ELSE
'(' + Quotename(cl.name) + ')' END + Space(1) + 'TO'
+ Space(1)
+ Quotename(User_name(usr.principal_id)) COLLATE database_default + CASE
WHEN perm.state <> 'W' THEN Space(0)
ELSE Space(1) + 'WITH GRANT OPTION'
END
--+ CHAR(13)+CHAR(10)+'GO'
AS '--Object Level Permissions'
FROM sys.database_permissions AS perm
INNER JOIN sys.objects AS obj
ON perm.major_id = obj.[object_id]
INNER JOIN sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
LEFT JOIN sys.columns AS cl
ON cl.column_id = perm.minor_id
AND cl.[object_id] = perm.major_id
--ORDER BY perm.permission_name ASC, perm.state_desc ASC
UNION
--Extracting database level permissions
SELECT Getdate() AS ScriptDateTime,
CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END +
Space
(1) +
perm.permission_name + Space(1)
+ Space(1) + 'TO' + Space(1)
+ Quotename(User_name(usr.principal_id)) COLLATE database_default + CASE
WHEN perm.state <> 'W' THEN Space(0)
ELSE Space(1) + 'WITH GRANT OPTION'
END
--+ CHAR(13)+CHAR(10)+'GO'
AS '--Database Level Permissions'
FROM sys.database_permissions AS perm
INNER JOIN sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
WHERE perm.major_id = 0
AND ( permission_name
+ User_name(usr.principal_id) != 'CONNECTDBO' )
--ORDER BY perm.permission_name ASC, perm.state_desc ASC
) AS UserScripts
ORDER BY scripts
No comments:
Post a Comment