This scripts all permissions in a DB for a user, including object and role permissions. It can be used to copy permissions to another server or database.

DECLARE    @User VARCHAR(8000)

SET    @User = 'bob'  

SELECT 'CREATE USER [' + @User + '] FOR LOGIN [' + @User +'] WITH DEFAULT_SCHEMA=[dbo]' AS '-- Create User (ignore for roles)'

SELECT    'EXEC sp_addrolemember @rolename =' 
    + SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''') + ', @membername =' + SPACE(1) + QUOTENAME(USER_NAME(rm.member_principal_id), '''') AS '--Role Memberships'
FROM    sys.database_role_members AS rm
WHERE    USER_NAME(rm.member_principal_id) = @User
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 ' + OBJECT_SCHEMA_NAME(obj.object_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(usr.name) 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 = @User
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(usr.name) 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 = @User
    AND    perm.major_id = 0
ORDER BY perm.permission_name ASC, perm.state_desc ASC

Leave a Reply

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