August 19, 2013 by Kenneth Fisher
Last week I posted my stored procedure for database permissions, sp_DBPermissions. A couple of days later I was demonstrating it to one of my co-workers and his response was “Where’s the server version?” Unable to come up with an appropriate rude response I’m posting the server version this week, sp_SrvPermissions.
It has the same basic setup. It returns three data sets, a list of principals, a list of role membership, and a list of server level permissions. The information can be narrowed down by passing in a principal and/or role name. Additional details are in the comments of the SP itself. If you create the stored procedure in master you will be able to call it from anywhere on the instance because the name begins with sp_.
NOTE: I’ve removed the code and put in a link to the latest version.