NOCOUNT ON
@OldUser sysname, @NewUser sysname
@OldUser = ‘User1
@NewUser = ‘User2’
‘USE’ + SPACE(1) + QUOTENAME(DB_NAME()) AS ‘–Database Context’
‘–Cloning permissions from’ + SPACE(1) + QUOTENAME(@OldUser) + SPACE(1) + ‘to’ + SPACE(1) + QUOTENAME(@NewUser) AS ‘–Comment’
‘EXEC sp_addrolemember @rolename =’
+ SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), ””) + ‘, @membername =’ + SPACE(1) + QUOTENAME(@NewUser, ””) AS ‘–Role Memberships’
sys.database_role_members AS rm
USER_NAME(rm.member_principal_id) = @OldUser
BY rm.role_principal_id ASC
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(@NewUser) COLLATE database_default
+ CASE WHEN perm.state <> ‘W’ THEN SPACE(0) ELSE SPACE(1) + ‘WITH GRANT OPTION’ END AS ‘–Object Level Permissions’
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
usr.name = @OldUser
BY perm.permission_name ASC, perm.state_desc ASC
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(@NewUser) COLLATE database_default
+ CASE WHEN perm.state <> ‘W’ THEN SPACE(0) ELSE SPACE(1) + ‘WITH GRANT OPTION’ END AS ‘–Database Level Permissions’
sys.database_permissions AS perm
INNER JOIN
sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
usr.name = @OldUser
perm.major_id = 0
BY perm.permission_name ASC, perm.state_desc ASC
protecttype WHEN 204 THEN ‘GRANT_W_GRANT ‘
WHEN 205 THEN ‘GRANT ‘
WHEN 206 THEN ‘REVOKE ‘
‘DUMMY GRANT’ END
CASE action WHEN 26 THEN ‘REFERENCES’
WHEN 178 THEN ‘CREATE FUNCTION’
WHEN 193 THEN ‘SELECT’
WHEN 195 THEN ‘INSERT’
WHEN 196 THEN ‘DELETE’
WHEN 197 THEN ‘UPDATE’
WHEN 198 THEN ‘CREATE TABLE’
WHEN 203 THEN ‘CREATE DATABASE’
WHEN 207 THEN ‘CREATE VIEW’
WHEN 222 THEN ‘CREATE PROCEDURE’
WHEN 224 THEN ‘EXECUTE’
WHEN 228 THEN ‘BACKUP DATABASE’
WHEN 233 THEN ‘CREATE DEFAULT’
WHEN 235 THEN ‘BACKUP LOG’
WHEN 236 THEN ‘CREATE RULE’
ELSE ‘ DUMMY ‘ END + ‘ ON ‘
so.name + ‘ TO <Your Role/User on Target Server>’
sysprotects sp
join sysobjects so on (so.id=sp.id)
join sysusers s on (sp.uid=s.uid)
s.[name] in (‘User1’)
WHEN 205 THEN ‘GRANT ‘
WHEN 206 THEN ‘REVOKE ‘
ELSE ‘DUMMY GRANT’
END
,CASE action WHEN 26 THEN ‘REFERENCES’
WHEN 178 THEN ‘CREATE FUNCTION’
WHEN 193 THEN ‘SELECT’
WHEN 195 THEN ‘INSERT’
WHEN 196 THEN ‘DELETE’
WHEN 197 THEN ‘UPDATE’
WHEN 198 THEN ‘CREATE TABLE’
WHEN 203 THEN ‘CREATE DATABASE’
WHEN 207 THEN ‘CREATE VIEW’
WHEN 222 THEN ‘CREATE PROCEDURE’
WHEN 224 THEN ‘EXECUTE’
WHEN 228 THEN ‘BACKUP DATABASE’
WHEN 233 THEN ‘CREATE DEFAULT’
WHEN 235 THEN ‘BACKUP LOG’
WHEN 236 THEN ‘CREATE RULE’
ELSE ‘ DUMMY ‘
END
,*
sysprotects sp
join sysobjects so on (so.id=sp.id)
join sysusers s on (sp.uid=s.uid)
s.[name] in (‘User1’)
