Permissions by user

I have a script that I have tweeked and tweeked over the years that offers me a quick view into what objects have specific permissions on them per user. I have used this oft times to audit systems and prove that things are the way they should be, or to possibly generate work when they are not.

The output of this little script is the servername, databasename, username, grantor, object type, object name, and the permissions (like grant select, or deny execute).

I have used this script in the past to generate a source and destination table, and then run compares on the two outputs to see what is different between two systems that should be identical. But this version below is just a quick and raw output.

I hope that this helps.





DECLARE @MyDBName sysname
 SET @MyDBName = '#Source'
 
 IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE ID = Object_ID('tempdb..#permissions') )
  DROP TABLE #permissions
 
 CREATE TABLE #permissions
 (
  [qid] [int] IDENTITY (1,1) NOT NULL,
  [ServerName] varchar(150) NOT NULL,
  [DatabaseName] varchar(150) NOT NULL,
  [UserName] sysname NOT NULL,
  [Grantor] sysname NOT NULL,
  [ObjectType] varchar(60) null,
  [ObjectName] sysname null,
  [PermissionState] varchar(60) null,
  [PermissionName] varchar(128) null
 )
 
 
 
 set NoCount on
 
 declare
  @result int,
  @ErrorMsg varchar(500),
  @execStr nvarchar(4000),
  @Version int
 
   --cycle thru the databases in this server, and retrieve the dbnames of each
   declare GetDataCursor insensitive cursor
    for
        select
       name
         from sys.sysdatabases
 --      where name not in ('Audit', 'tempdb')
 --         and status not in ( 48, 528)
 --      where name in ('Master', 'MSDB')
       Order by name
 
   if @@Error <> 0 goto ErrorProc
   --try
 
    declare
     @DBName varchar(200)
 
    open GetDataCursor
    if @@Error <> 0 goto ErrorGetDataCursor
    while 0 = 0
    begin
     fetch next from GetDataCursor into
      @DBName
 
     if @@Error <> 0 goto ErrorGetDataCursor
     if (@@Fetch_Status = 0)
     begin
 
      Set @execStr = '
       INSERT INTO #permissions ( [ServerName],[DatabaseName],[UserName],[Grantor],[ObjectType],[ObjectName],[PermissionState],[PermissionName])
        SELECT
          @@ServerName AS [ServerName],
          --DB_NAME() AS [DatabaseName],
          ''' + @DBName + ''' AS [DatabaseName],
          u.name AS [UserName],
          u2.name AS [Grantor],
          CASE
           WHEN major_id > 0 THEN o.type_desc
           ELSE ''System Object''
          END AS [ObjectType],
          CASE
           WHEN major_id > 0 THEN o.name COLLATE DATABASE_DEFAULT
           ELSE o2.name
          END AS [ObjectName],
          dp.state_desc AS [PermissionState],
          permission_name as [PermissionName]
         FROM [' + @DBName + '].sys.database_permissions dp
          LEFT OUTER join [' + @DBName + '].sys.database_principals u on dp.grantee_principal_id = u.principal_id
          LEFT OUTER join [' + @DBName + '].sys.database_principals u2 on dp.grantor_principal_id = u2.principal_id
          LEFT OUTER JOIN [' + @DBName + '].sys.objects o ON o.object_id = dp.major_id
          LEFT OUTER JOIN master.sys.sysobjects o2 ON o2.id = dp.major_id
         WHERE class = 1
          AND dp.grantee_principal_id > 0
         ORDER BY [UserName], [ObjectType], [ObjectName], [PermissionState], [PermissionName]'
 
 --      Print @execStr
      exec @Result = sp_executesql @execStr
      if @@Error <> 0 or @Result <> 0
      begin
       set @ErrorMsg = 'Error occurred retrieving data for 1st query'
       goto ErrorGetDataCursor
      end
 
     end
     else
      break
    end
    goto SuccessGetDataCursor
 
   --except
    ErrorGetDataCursor:
    deallocate GetDataCursor
    goto ErrorProc
 
   --finally
    SuccessGetDataCursor:
    deallocate GetDataCursor
   --end
 
   SELECT * FROM #permissions
 
  --finally
   SuccessProc:
 
  --except
   ErrorProc: