PtiTom

SQL Server - All Permissions

May 23rd, 2020
2,093
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. declare @permission table (
  2. Database_Name sysname,
  3. User_Role_Name sysname,
  4. Account_Type nvarchar(60),
  5. Action_Type nvarchar(128),
  6. Permission nvarchar(60),
  7. ObjectName sysname null,
  8. Object_Type nvarchar(60)
  9. )
  10. declare @dbs table (dbname sysname)
  11. declare @Next sysname
  12. insert into @dbs
  13. --Use this for all databases
  14. select name from sys.databases order by name
  15. --Uncomment and use this for a single DB
  16. --select name from 'YOURDBNAMEHERE' as dbname
  17. select top 1 @Next = dbname from @dbs
  18. while (@@rowcount<>0)
  19. begin
  20. insert into @permission
  21. exec('use [' + @Next + ']
  22. declare @objects table (obj_id int, obj_type char(2))
  23. insert into @objects
  24. select id, xtype from master.sys.sysobjects
  25. insert into @objects
  26. select object_id, type from sys.objects
  27.  
  28. SELECT ''' + @Next + ''', a.name as ''User or Role Name'', a.type_desc as ''Account Type'',
  29. d.permission_name as ''Type of Permission'', d.state_desc as ''State of Permission'',
  30. OBJECT_SCHEMA_NAME(d.major_id) + ''.'' + object_name(d.major_id) as ''Object Name'',
  31. case e.obj_type
  32. when ''AF'' then ''Aggregate function (CLR)''
  33. when ''C'' then ''CHECK constraint''
  34. when ''D'' then ''DEFAULT (constraint or stand-alone)''
  35. when ''F'' then ''FOREIGN KEY constraint''
  36. when ''PK'' then ''PRIMARY KEY constraint''
  37. when ''P'' then ''SQL stored procedure''
  38. when ''PC'' then ''Assembly (CLR) stored procedure''
  39. when ''FN'' then ''SQL scalar function''
  40. when ''FS'' then ''Assembly (CLR) scalar function''
  41. when ''FT'' then ''Assembly (CLR) table-valued function''
  42. when ''R'' then ''Rule (old-style, stand-alone)''
  43. when ''RF'' then ''Replication-filter-procedure''
  44. when ''S'' then ''System base table''
  45. when ''SN'' then ''Synonym''
  46. when ''SQ'' then ''Service queue''
  47. when ''TA'' then ''Assembly (CLR) DML trigger''
  48. when ''TR'' then ''SQL DML trigger''
  49. when ''IF'' then ''SQL inline table-valued function''
  50. when ''TF'' then ''SQL table-valued-function''
  51. when ''U'' then ''Table (user-defined)''
  52. when ''UQ'' then ''UNIQUE constraint''
  53. when ''V'' then ''View''
  54. when ''X'' then ''Extended stored procedure''
  55. when ''IT'' then ''Internal table''
  56. end as ''Object Type''
  57. FROM [' + @Next + '].sys.database_principals a
  58. left join [' + @Next + '].sys.database_permissions d on a.principal_id = d.grantee_principal_id
  59. left join @objects e on d.major_id = e.obj_id
  60. order by a.name, d.class_desc')
  61. delete @dbs where dbname = @Next
  62. select top 1 @Next = dbname from @dbs
  63. end
  64. set nocount off
  65. select * from @permission
RAW Paste Data