list stored procedures that can be called by each user

Question

Hi,

i need a query to list every stored procedure that can be executed by a specific user and This for each user of my mssql server 2005.

output example:

sa:

sp_MSrepl_startup

sp_MScleanupmergepublisher

administrator:

xp_foo

sp_bar

Any help will be appreciated

phillipe

Answer 1

Easiest would be sp_helprotect but it's legacy

This is SQL Server 2005+, but the code here quite basic and would need expanded as needed:

SELECT
    SUSER_SNAME(u.sid), OBJECT_NAME(p.major_id)
FROM
    sys.database_permissions p
    JOIN
    sys.database_principals u ON p.grantee_principal_id = u.principal_id

If the DB user has rights on the code without a role, or nested roles, this can get ugly quickly...