SQL Server Scripts to Copy User Permission

I have the need to copy user permissions from one user to another.
 
I have two SQL Server database. The structure of both database are the same.  The first db is a source database with permission for set for a specific user (User1).  The second database has a new user (User2) with no permissions.  I want user2 have the same permission of user1.  The following links/scripts allowed me to copy user permissions.
 

/* ********************************************
******************************************** */

SET

NOCOUNT ON

DECLARE

@OldUser sysname, @NewUser sysname

SET

@OldUser = ‘User1

SET

@NewUser = ‘User2’

SELECT

‘USE’ + SPACE(1) + QUOTENAME(DB_NAME()) AS ‘–Database Context’

 

SELECT

‘–Cloning permissions from’ + SPACE(1) + QUOTENAME(@OldUser) + SPACE(1) + ‘to’ + SPACE(1) + QUOTENAME(@NewUser) AS ‘–Comment’

 
SELECT

‘EXEC sp_addrolemember @rolename =’

+ SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), ””) + ‘, @membername =’ + SPACE(1) + QUOTENAME(@NewUser, ””) AS ‘–Role Memberships’

FROM

sys.database_role_members AS rm

WHERE

USER_NAME(rm.member_principal_id) = @OldUser

ORDER

BY rm.role_principal_id ASC

 
SELECT

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’

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

WHERE

usr.name = @OldUser

ORDER

BY perm.permission_name ASC, perm.state_desc ASC

 
SELECT

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’

FROM

sys.database_permissions AS perm

INNER JOIN

sys.database_principals AS usr

ON perm.grantee_principal_id = usr.principal_id

WHERE

usr.name = @OldUser

AND

perm.major_id = 0

ORDER

BY perm.permission_name ASC, perm.state_desc ASC

 
 

/* ********************************************
******************************************** */

SELECT
CASE

protecttype WHEN 204 THEN ‘GRANT_W_GRANT ‘

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 + ‘ ON ‘

+

so.name + ‘ TO <Your Role/User on Target Server>’

from

sysprotects sp

inner

join sysobjects so on (so.id=sp.id)

inner

join sysusers s on (sp.uid=s.uid)

WHERE

s.[name] in (‘User1’)

 
 
 

/* ******************************************************
The following Script just provides a select of the previous script
****************************************************** */

SELECT
CASE protecttype WHEN 204 THEN ‘GRANT_W_GRANT ‘

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

,*

from

sysprotects sp

inner

join sysobjects so on (so.id=sp.id)

inner

join sysusers s on (sp.uid=s.uid)

WHERE

s.[name] in (‘User1’)

 
 

Leave a Reply

Your email address will not be published. Required fields are marked *