DISCLAIMER: The general rule on SQL Server system stored procedures or undocumented stored procedures is: don't use them - they may change in a future service pack or not be available in a future release of SQL Server. Fair enough? And as always, I don't guarantee my code below will work on your system, let alone produce good results.
I recently found a use for the system stored procedure “sp_msobjectprivs”, which is called by SQL Server itself when you script database objects and ask to include the privileges in your output. I was using it to script permissions on stored procedures in order to generate my own “GRANT EXECUTE ON...”-type statements.
The first thing is to be able to decode the output when calling “sp_msobjectprivs” by putting the results into a temp table:
CREATE TABLE #privs (
[action] INT NOT NULL, --"action" from sysprotects
[column] NVARCHAR(128), --sysname
[uid] INT NOT NULL,
[username] NVARCHAR(256), --user's name to GRANT or DENY/REVOKE
[protecttype] INT NOT NULL, --206 for DENY/REVOKE, 205 for GRANT (from sysprotects)
[name] NVARCHAR(128), --sysname of passed object
[owner] NVARCHAR(256), --owner of the object
[id] INT NOT NULL,
[grantor] NVARCHAR(256)
)
The values for the “action“ and “protecttype“ columns are taken from the sysprotects table. For the appropriate values, see http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sys-p_0837.asp.
To populate the table, you can run a command like:
INSERT #privs EXEC dbo.sp_msobjectprivs '<object name>'
...where “<object name>” is the name of the database object (for me, a stored procedure) to script. Note you have to be in the correct database context for this call to work. You can try it, and look at the results compared to SQL Server's script to see how the columns work.
In my case I was calling the code above on a stored procedure, where the only applicable permissions are EXECUTE (no SELECT, UPDATE, INSERT or DELETEs, obviously). I can generate the exact permissions that SQL Server generates by using a statement like:
SELECT --first get whether this is DENY or GRANT
CASE WHEN [protecttype] = 206 THEN 'DENY ' ELSE 'GRANT ' END +
--get the object (stored procedure name)
'EXECUTE ON [' + [owner] + '].[' + [name] + '] TO ' +
--get the user we're issuing DENY or GRANT to
'[' + [username] + '] ' +
--if PUBLIC, specify "CASCADE"
CASE WHEN LOWER([username]) = 'public' THEN 'CASCADE ' ELSE '' END AS [privs]
FROM #privs
WHERE --get only "EXECUTE" actions
[action] = 224
Finally we need to drop the temporary table with DROP TABLE #privs.
I hope this comes in useful to someone out there, as I could find very little documentation on “sp_msobjectprivs” myself. Happy scripting!