I want to view a users execute permissions on stored procedures

Question

I want to view a users execute permissions on stored procedures

cedbox

Grasshopper

Points: 19
More actions
March 31, 2016 at 7:59 am

#326232

I'm trying get a specific user execute permissions on all the stored procedures he or she has access to. When I run this script the result is empty, please help.
Use [DBname]
GO
SELECT perms.*,
users.name
FROM sys.database_permissions perms
JOIN sys.database_principals users ON perms.grantee_principal_id = users.principal_id
WHERE perms.major_id = OBJECT_ID(N'[dbo].[StoredProcedureName]')
AND user = 'Matt Ryan';

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

That's only going to show the permissions he or she has on [dbo].[StoredProcedureName]. Try removing the first line of your WHERE clause and see what you get then.

Is this a Windows or a SQL user? Do you really have entities with spaces in their names?

John

johnwalker10 SSCrazy Eights Points: 9124 More actions · Answer 2

You can open the properties dialog in Enterprise Manager and click "Permissions..."

cedbox Grasshopper Points: 19 More actions · Answer 3

No, its not a Windows user. I removed the first line in the where clause but nothing was returned

cedbox Grasshopper Points: 19 More actions · Answer 4

I want to be able to gather the information on a user to eventually write a stored procedure. So, I need to know how to get the information using T-SQL.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

Actually, I've just looked at your query again, and it can't be the one you ran. There's no such column as "user" in either of the catalog views that you use, so you'd get an error. Please will you post the exact query that you used?

John