Find all permissions in all levels on a database

Here is a script that find all permissions in all levels on a database for a specific user (@FromUser) and make scripts of the to another one (@ToUser).

Notes:

1. If you want to get the scripts to the current user, put the same name in both parameters @FromUser and @ToUser.

2. Those queris without the user filter will return all the permissions to all users in the database.

SET NOCOUNT ON

DECLARE       @FromUser sysname, @ToUser sysname

SELECT @FromUser = 'FromUserName', @ToUser = 'ToUserName'

SELECT 'USE' + SPACE(1) + QUOTENAME(DB_NAME()) AS '--Database Context'

--Role Memberships:

SELECT 'EXEC sp_addrolemember @rolename ='

              + SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''') + ', @membername =' + SPACE(1)

              + QUOTENAME(@ToUser, '''') AS '--Role Memberships'

FROM   sys.database_role_members AS rm

WHERE  USER_NAME(rm.member_principal_id) = @FromUser

ORDER BY rm.role_principal_id ASC

--Object Level Permissions:

SELECT CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END

              + SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(SCHEMA_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(@ToUser) 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 perm

JOIN          sys.objects obj                          ON perm.major_id = obj.[object_id]

JOIN          sys.database_principals usr ON perm.grantee_principal_id = usr.principal_id

LEFT JOIN     sys.columns cl                           ON cl.column_id = perm.minor_id AND cl.[object_id] =perm.major_id

WHERE  usr.name = @FromUser

ORDER BY perm.permission_name ASC, perm.state_desc ASC

--Database Level Permissions

--D = Deny, R = Revoke, G = Grant, W = Grant With Grant Option

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(@ToUser) 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 = @FromUser

AND    perm.major_id = 0

ORDER BY perm.permission_name ASC, perm.state_desc ASC

-- Schema Level Permission

SELECT CASE

                     WHEN perm.state <> 'W' THEN perm.state_desc --W=Grant With Grant Option

                     ELSE 'GRANT'

              END

                           + SPACE(1) + perm.permission_name --CONNECT, etc

                           + SPACE(1) + 'ON' + SPACE(1) + class_desc + '::' COLLATE database_default --TO <user name>

                           + QUOTENAME(SCHEMA_NAME(major_id))

                           + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(@ToUser) COLLATE database_default

                           + CASE

                                  WHEN perm.state <> 'W' THEN SPACE(0)

                                  ELSE SPACE(1) + 'WITH GRANT OPTION'

                             END

                     AS '-- Schema Level Permission --'

from sys.database_permissions AS perm

       inner join sys.schemas s

              on perm.major_id = s.schema_id

       inner join sys.database_principals dbprin

              on perm.grantee_principal_id = dbprin.principal_id

WHERE class = 3 --class 3 = schema

and dbprin.name = @FromUser

ORDER BY perm.permission_name ASC, perm.state_desc ASC

--Server Level Permissions

SELECT  server_permissions.state_desc COLLATE SQL_Latin1_General_CP1_CI_AS + ' ' + server_permissions.permission_nameCOLLATE SQL_Latin1_General_CP1_CI_AS

        + ' TO [' + server_principals.name COLLATE SQL_Latin1_General_CP1_CI_AS + ']' AS '--Server Level Permissions'

FROM    sys.server_permissions AS server_permissions WITH (NOLOCK)

        INNER JOIN sys.server_principals AS server_principals WITH (NOLOCK) ON server_permissions.grantee_principal_id =server_principals.principal_id

WHERE   server_principals.type IN ('S', 'U', 'G')

AND           server_principals.name = @FromUser

ORDER BY server_principals.name ,

        server_permissions.state_desc ,

        server_permissions.permission_name;