Thursday, November 1, 2007

USP_EffectivePermissions

Procedure to report on server & object permissions for a given user.
Usage : EXEC USP_EffectivePermissions 'username'
Install in master db to use from any db on the server.
CREATE PROC USP_EffectivePermissions
         @User VARCHAR(100)
AS
 BEGIN

   SET NOCOUNT ON
  
   IF OBJECT_ID('tempdb..##ServerLogins') IS NOT NULL
     BEGIN
       DROP TABLE ##SERVERLOGINS
     END
    
   IF OBJECT_ID('tempdb..##DBUsers') IS NOT NULL
     BEGIN
       DROP TABLE ##DBUSERS
     END
    
   IF OBJECT_ID('tempdb..##DBList') IS NOT NULL
     BEGIN
       DROP TABLE ##DBLIST
     END
    
   IF OBJECT_ID('tempdb..##DBObjPermissions') IS NOT NULL
     BEGIN
       DROP TABLE ##DBOBJPERMISSIONS
     END
    
   IF OBJECT_ID('tempdb..##CrossJoinMultiplier') IS NOT NULL
     BEGIN
       DROP TABLE ##CROSSJOINMULTIPLIER
     END
    
   IF OBJECT_ID('tempdb..##Results') IS NOT NULL
     BEGIN
       DROP TABLE ##RESULTS
     END
    
   DECLARE  @DBName VARCHAR(128);   
   DECLARE  @SQLCmd VARCHAR(2000);   
   DECLARE  @NumberOfDBs INT;
  
   -- Get the SQL Server logins
   -- Create login table
   CREATE TABLE ##SERVERLOGINS ([SID] VARBINARY(85) NULL,[LOGIN_NAME] VARCHAR(128) NULL);
  
   -- Populate login table
   INSERT INTO ##SERVERLOGINS
   SELECT SID,CAST(LOGINNAME AS VARCHAR(128))  AS [LOGIN_NAME]
   FROM   MASTER.DBO.SYSLOGINS
   WHERE  LOGINNAME = @User;
  
   -- Create list of databases
   CREATE TABLE ##DBLIST (
  [DBNAME] VARCHAR(128))
  
   -- perform for all dbs on server
   INSERT INTO ##DBLIST
   SELECT   NAME
   FROM     MASTER..SYSDATABASES
   WHERE    DBID > 4
   ORDER BY NAME;
  
   SELECT @NumberOfDBs = COUNT(* )
   FROM   ##DBLIST
         
   -- Create the output table for the Database User ID's
   CREATE TABLE ##DBUSERS (
  [DATABASE_USER_ID] VARCHAR(128),
  [SERVER_LOGIN] VARCHAR(128),
  [DATABASE_ROLE] VARCHAR(128),
  [DATABASE_NAME] VARCHAR(128));
  
   -- Create the output table for Object Level Permissions
   CREATE TABLE ##DBOBJPERMISSIONS (
  [DATABASE_NAME] VARCHAR(128),
  [GRANTOR] VARCHAR(128),
  [GRANTEE] VARCHAR(128),
  [OBJTYPE] VARCHAR(128),
  [OBJECTNAME] VARCHAR(128),
  [PERMISSION_LEVEL] VARCHAR(128),
  [PERMISSION] VARCHAR(128));
     
   -- Declare a cursor to loop through all the databases on the server
   DECLARE CSRDB CURSOR  FOR
  SELECT   DBNAME
  FROM     ##DBLIST
  ORDER BY DBNAME
           
   -- Open the cursor and get the first database name
   OPEN CSRDB
  
   FETCH NEXT FROM CSRDB
   INTO @DBName
       
   -- Loop through the cursor
   WHILE @@FETCH_STATUS = 0
     BEGIN     
       -- populate ##DBUsers table for current db
       SELECT @SQLCmd = 'INSERT ##DBUsers '
   + ' SELECT CAST(su.[name] AS VARCHAR(128)) AS [database_user_id], '
   + ' CAST(COALESCE (u.[login_name], ''* Orphaned *'') AS VARCHAR(128))AS [server_login], '
   + ' CAST(COALESCE (sug.name, ''Public'') AS VARCHAR(128)) AS [database_role],'
   + ' CAST(''' + @DBName + ''' AS VARCHAR(128)) AS [database_name]'
   + ' FROM [' + @DBName + '].[dbo].[sysusers] su'
   + ' LEFT OUTER JOIN ##ServerLogins u' + ' ON su.sid = u.sid'
   + ' LEFT OUTER JOIN ([' + @DBName + '].[dbo].[sysmembers] sm '
   + ' INNER JOIN [' + @DBName + '].[dbo].[sysusers] sug '
   + ' ON sm.groupuid = sug.uid)'
   + ' ON su.uid = sm.memberuid '
   + ' WHERE su.hasdbaccess = 1'
   + ' AND SU.name = ''' + @User + ''''
  -- uncomment to debug      
       -- PRINT @SQLCmd
       EXEC(@SQLCmd)
      
       -- populate ##DBObjPermissions table for current db
       SELECT @SQLCmd = 'use [' + @DBName + '];'
   + 'insert into ##DBObjPermissions '
   + ' select '
   + ' CAST(''' + @DBName + ''' AS VARCHAR(128)) AS [database_name],'
   + ' user_name(sec.grantor) as grantor, '
   + ' user_name(sec.uid) as grantee, '
   + ' case obj.type '
   + ' when ''C'' then ''Check constraint'' '
   + ' when ''D'' then ''Default (constraint or stand-alone)'' '
   + ' when ''F'' then ''Foreign Key'' '
   + ' when ''PK'' then ''Primary Key'' '
   + ' when ''P'' then ''Stored Procedure'' '
   + ' when ''FN'' then ''Function (Scalar)'' '
   + ' when ''IF'' then ''Function (Inline)'' '
   + ' when ''R'' then ''Rule (old-style, stand-alone)'' '
   + ' when ''RF'' then ''Replication-filter-procedure'' '
   + ' when ''S'' then ''System base table'' '
   + ' when ''TA'' then ''Assembly (CLR) DML trigger'' '
   + ' when ''TF'' then ''Function (TableValued)'' '
   + ' when ''U'' then ''Table'' '
   + ' when ''UQ'' then ''Unique constraint'' '
   + ' when ''V'' then ''View'' '
   + ' when ''X'' then ''Extended stored procedure'' '
   + ' end as objtype, '
   + ' stbl.name + ''.'' + obj.name as objectname, '
   + ' protecttype.name permission_level,  ' + ' action.name as permission '
   + ' from ' + '[' + @DBName + '].dbo.sysobjects as obj '
   + ' inner join [' + @DBName + '].dbo.sysusers as stbl on stbl.uid = obj.uid '
   + '        and stbl.name = ''' + @User + ''''
   + ' inner join ##DBUsers on ##DBUsers.[database_user_id] COLLATE SQL_Latin1_General_CP1_CS_AS = stbl.name COLLATE SQL_Latin1_General_CP1_CS_AS'
   + ' inner join [' + @DBName + '].dbo.sysprotects as sec on sec.id = obj.id '
   + ' inner join master.dbo.spt_values as action on sec.action = action.number and action.type = ''t'' '
   + ' inner join master.dbo.spt_values as protecttype on sec.protecttype = protecttype.number and protecttype.type = ''t'' '
   + ' where objectpropertyex(obj.id,''ismsshipped'') = 0 '
                     
  -- uncomment to debug                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
       -- PRINT @SQLCmd
       EXEC(@SQLCmd)
      
       -- Get the next database name
       FETCH NEXT FROM CSRDB
       INTO @DBName
           
     -- End of the cursor loop
     END
    
   -- Close and deallocate the CURSOR
   CLOSE CSRDB
  
   DEALLOCATE CSRDB
  
   CREATE TABLE ##CROSSJOINMULTIPLIER (
     USERNAME VARCHAR(128)   NULL);
  
   IF @NumberOfDBs = 1
     INSERT INTO ##CROSSJOINMULTIPLIER
     SELECT [SERVER_LOGIN]
     FROM   ##DBUSERS -- 1 db, multiply section headers by users
   ELSE
     INSERT INTO ##CROSSJOINMULTIPLIER
     SELECT TOP 1 'dummy'
     FROM   SYSOBJECTS -- multiple dbs,
           
   CREATE TABLE ##RESULTS (
     [HEADER_ROW] INT,[DATABASE_NAME] VARCHAR(128),[SORT_ORDER] INT,[GRANTOR] VARCHAR(128),[GRANTEE] VARCHAR(128),[OBJTYPE] VARCHAR(128),[OBJECTNAME] VARCHAR(128),[USERGRANTEE] VARCHAR(128),[PERMISSION_LEVEL] VARCHAR(128),[PERMISSION] VARCHAR(128));
  
   TRUNCATE TABLE ##RESULTS
  
   --data for logins
   INSERT INTO ##RESULTS
   SELECT
  0 AS HEADER_ROW,
  CAST(SERVERPROPERTY('ServerName') AS VARCHAR(50)) + ' - ' + DATABASE_NAME AS DATABASE_NAME,
  2 AS SORT_ORDER,
  '' AS GRANTOR,
  '' AS GRANTEE,
  MAX(CASE [DATABASE_ROLE] WHEN 'db_datareader' THEN 'db_datareader, ' ELSE '' END) +
  MAX(CASE [DATABASE_ROLE] WHEN 'db_accessadmin' THEN 'db_accessadmin, ' ELSE '' END) +
  MAX(CASE [DATABASE_ROLE] WHEN 'db_backupoperator' THEN 'db_backupoperator, ' ELSE '' END) +
  MAX(CASE [DATABASE_ROLE] WHEN 'db_datawriter' THEN 'db_datawriter, ' ELSE '' END) +
  MAX(CASE [DATABASE_ROLE] WHEN 'db_ddladmin' THEN 'db_ddladmin, ' ELSE '' END) +
  MAX(CASE [DATABASE_ROLE] WHEN 'db_denydatareader' THEN 'db_denydatareader, ' ELSE '' END) +
  MAX(CASE [DATABASE_ROLE] WHEN 'db_denydatawriter' THEN 'db_denydatawriter, ' ELSE '' END) +
  MAX(CASE [DATABASE_ROLE] WHEN 'db_owner' THEN 'db_owner, ' ELSE '' END) +
  MAX(CASE [DATABASE_ROLE] WHEN 'db_securityadmin' THEN 'db_securityadmin, ' ELSE '' END) +
  MAX(CASE [DATABASE_ROLE] WHEN 'public' THEN 'public, ' ELSE '' END) AS ROLE_OBJECTTYPE,
       'database user' AS OBJ_NAME,
  CASE [SERVER_LOGIN] WHEN [DATABASE_USER_ID] THEN [DATABASE_USER_ID] ELSE [DATABASE_USER_ID] + ' (' + [SERVER_LOGIN] + ')' END AS USER_GRANTEE,
  '' AS PERMISSION_LEVEL,
  '' AS PERMISSION
   FROM    
  ##DBUSERS
   GROUP BY
  [DATABASE_NAME],
  [DATABASE_USER_ID],
  [SERVER_LOGIN]
           
   -- data for objects
   INSERT INTO ##RESULTS
   SELECT  0 AS HEADER_ROW,
   CAST(SERVERPROPERTY('ServerName') AS VARCHAR(50)) + ' - ' + DATABASE_NAME AS DATABASE_NAME,
   4 AS SORT_ORDER,
   GRANTOR,
   GRANTEE,
   [OBJTYPE] AS ROLE_OBJECTTYPE,
   [OBJECTNAME] AS OBJ_NAME,
   [GRANTEE] AS USER_GRANTEE,
   [PERMISSION_LEVEL] AS PERMISSION_LEVEL,
   [PERMISSION] AS PERMISSION
   FROM   ##DBOBJPERMISSIONS
         
   INSERT INTO ##RESULTS
   SELECT  1 AS HEADER_ROW,
   CAST(SERVERPROPERTY('ServerName') AS VARCHAR(50)) + ' - ' + DBNAME AS DATABASE_NAME,
   1 AS SORT_ORDER,
   '' AS GRANTOR,
   '' AS GRANTEE,
   'Role' AS ROLE_OBJECTTYPE,
   'Object' AS OBJ_NAME,
   'User' AS USER_GRANTEE,
   ' ' AS PERMISSION_LEVEL,
   ' ' AS PERMISSION
   FROM   ##DBLIST
          INNER JOIN ##RESULTS
            ON ##RESULTS.DATABASE_NAME = CAST(SERVERPROPERTY('ServerName') AS VARCHAR(50)) + ' - ' + ##DBLIST.DBNAME
               AND ##RESULTS.SORT_ORDER = 2
                                         
   -- header rows for objects
   INSERT INTO ##RESULTS
   SELECT  1 AS HEADER_ROW,
   CAST(SERVERPROPERTY('ServerName') AS VARCHAR(50)) + ' - ' + DBNAME AS DATABASE_NAME,
   3 AS SORT_ORDER,
   '' AS GRANTOR,
   '' AS GRANTEE,
   'Object Type' AS ROLE_OBJECTTYPE,
   'Object' AS OBJ_NAME,
   'Grantee' AS USER_GRANTEE,
   'Permission Level' AS PERMISSION_LEVEL,
   'Permission' AS PERMISSION
   FROM   ##DBLIST
          INNER JOIN ##RESULTS
            ON ##RESULTS.DATABASE_NAME = CAST(SERVERPROPERTY('ServerName') AS VARCHAR(50)) + ' - ' + ##DBLIST.DBNAME
               AND ##RESULTS.SORT_ORDER = 4
                                         
   SELECT  
  DATABASE_NAME,
  GRANTOR,
  GRANTEE,
  OBJTYPE,
  OBJECTNAME,
  USERGRANTEE,
  PERMISSION_LEVEL,
  PERMISSION
   FROM    
  ##RESULTS
   ORDER BY
  DATABASE_NAME,
  SORT_ORDER
 END
 go

No comments: