How do I get the granted permissions for a stored procedure in sql server 2005?
Right click on your procedure and select Properties. You'll get the following window. As shown inthe preceding image, go to Permissions tab and click on Search button. On click you'll get a window to select user and roles, click on Browse to select users that require permission and click OK.
Use SQL Server Management StudioExpand Databases, expand the database in which the procedure belongs, and then expand Programmability. Expand Stored Procedures, right-click the procedure to grant permissions on, and then select Properties. From Stored Procedure Properties, select the Permissions page.
SELECT
OBJECT_NAME(major_id), USER_NAME(grantee_principal_id), permission_name
FROM
sys.database_permissions p
WHERE
OBJECT_NAME(major_id) = 'MyProc'
You can tweak this to join to sys.database_principals
, or sys.objects
if you want too
try (NOTE: works for more than stored procedures):
SELECT
dp.NAME AS principal_name
,dp.type_desc AS principal_type_desc
,o.NAME AS object_name
,o.type_desc
,p.permission_name
,p.state_desc AS permission_state_desc
FROM sys.all_objects o
INNER JOIN sys.database_permissions p ON o.OBJECT_ID=p.major_id
LEFT OUTER JOIN sys.database_principals dp ON p.grantee_principal_id = dp.principal_id
WHERE o.NAME = 'YourProcedureName'
Kind of off topic, but ... you could enable you development db to "remember" the permissions it has had on different objects and keep them during development time regardless of how-many times you drop and create an object ...
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With