0

I need to inspect the permissions in SQL on some built-in stored procedures. In Management Studio I see the permissions for custom SPs, but not the System SP. Additionally, there are a lot of SP's that aren't shown in the 'System Stored Procedures' as viewed from Management Studio's Object Explorer. I can either navigate to a particular place in SQL Management Studio, or a particular SQL query; whatever is sensible.

Question: How can I inspect the permissions applied to a stored procedure such as "xp_msver"? Specifically 'Execute' permissions.

P.S. - I'm not an SQL guru if you couldn't tell. Hopefully the question makes sense.

mechgt
  • 41
  • 1
  • 9

1 Answers1

1

You may use the following script.

USE master
SELECT CASE dp.state_desc
  WHEN 'GRANT_WITH_GRANT_OPTION'
   THEN 'GRANT'
  ELSE dp.state_desc
  END + ' ' + dp.permission_name + ' ON ' + CASE dp.class
  WHEN 0
   THEN 'DATABASE::[' + DB_NAME() + ']'
  WHEN 1
   THEN 'OBJECT::[' + SCHEMA_NAME(o.schema_id) + '].[' + o.[name] + ']'
  WHEN 3
   THEN 'SCHEMA::[' + SCHEMA_NAME(dp.major_id) + ']'
  END + ' TO [' + USER_NAME(grantee_principal_id) + ']' + CASE dp.state_desc
  WHEN 'GRANT_WITH_GRANT_OPTION'
   THEN ' WITH GRANT OPTION;'
  ELSE ';'
  END + CHAR(10) + 'GO' COLLATE DATABASE_DEFAULT
FROM sys.database_permissions dp
LEFT JOIN sys.all_objects o ON dp.major_id = o.OBJECT_ID
WHERE dp.class < 4
 -- AND major_id >= 0 
 AND grantee_principal_id <> 1
 AND o.name = 'xp_msver';

To get permissions for all system objects, you may comment out the last line "AND o.name = 'xp_msver'". To get permissions only on non system objects, uncomment "AND major_id >= 0 " line.

Suneel
  • 26
  • 1