Labels

admin (1) aix (1) alert (1) always-on (2) Architecture (1) aws (3) Azure (1) backup (3) BI-DWH (10) Binary (3) Boolean (1) C# (1) cache (1) casting (3) cdc (1) certificate (1) checks (1) cloud (3) cluster (1) cmd (7) collation (1) columns (1) compilation (1) configurations (7) Connection-String (2) connections (6) constraint (6) copypaste (2) cpu (2) csv (3) CTE (1) data-types (1) datetime (23) db (547) DB2 (1) deadlock (2) Denali (7) device (6) dotNet (5) dynamicSQL (11) email (5) encoding (1) encryption (4) errors (124) excel (1) ExecutionPlan (10) extended events (1) files (7) FIPS (1) foreign key (1) fragmentation (1) functions (1) GCP (2) gMSA (2) google (2) HADR (1) hashing (3) in-memory (1) index (3) indexedViews (2) insert (3) install (10) IO (1) isql (6) javascript (1) jobs (11) join (2) LDAP (2) LinkedServers (8) Linux (15) log (6) login (1) maintenance (3) mariadb (1) memory (4) merge (3) monitoring (4) MSA (2) mssql (444) mssql2005 (5) mssql2008R2 (20) mssql2012 (2) mysql (36) MySQL Shell (5) network (1) NoSQL (1) null (2) numeric (9) object-oriented (1) offline (1) openssl (1) Operating System (4) oracle (7) ORDBMS (1) ordering (2) Outer Apply (1) Outlook (1) page (1) parameters (2) partition (1) password (1) Performance (103) permissions (10) pivot (3) PLE (1) port (4) PostgreSQL (14) profiler (1) RDS (3) read (1) Replication (12) restore (4) root (1) RPO (1) RTO (1) SAP ASE (48) SAP RS (20) SCC (4) scema (1) script (8) security (10) segment (1) server (1) service broker (2) services (4) settings (75) SQL (74) SSAS (1) SSIS (19) SSL (8) SSMS (4) SSRS (6) storage (1) String (35) sybase (57) telnet (2) tempdb (1) Theory (2) tips (120) tools (3) training (1) transaction (6) trigger (2) Tuple (2) TVP (1) unix (8) users (3) vb.net (4) versioning (1) windows (14) xml (10) XSD (1) zip (1)

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;

No comments:

Post a Comment