Inspect SQL permissions on built-in stored procedures (e.g. xp_msver)

Question

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.

Answer 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.