CODEWITHSUNDEEP
sql-serverpermissions
928-5.0
928-5.0

Reputation: 58

Getting generic permissions in SQL Server

I created some roles in SQL Server using SSMS, and I have not found any code to search the system tables for generic permissions, so I can write a script to generate t-sql to create it. For example, if I create a stored stored procedure and grant permissions to it:

grant execute on sp_blah to testrole

However, if I create a generic permission like

grant execute to testrole

I have had not luck hunting down where to find those permissions so I can script them out, and they don't display in SSMS. How do I find generic permissions so I can generate a transact sql script?

Upvotes: 0

Views: 78

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89091

Those are database permissions.

select user_name(grantee_principal_id) grantee, 
       class_desc, 
       permission_name, 
       state_desc 
from sys.database_permissions
where class_desc = 'DATABASE'

Upvotes: 1

Related Questions