Wednesday, 1 May 2019

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

No comments:

Post a Comment