Revoked Permissions in SQL Server

I am using SQL Server 2008. Where are the revoked permissions stored in the database? I found the grant permissions in sys.database_permissions table. But the permissions are still there for a stored procedure in sys.database_permissions even after I removed it using revoke EXECUTE ON StoredProcedure to USER

Where can see all the revoked permissions for a Stored Procedure or Function ?
Haren MorseAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Máté FarkasDatabase Developer and AdministratorCommented:
Only granted permission are stored. Revoked permissions are not stored because they are deleted from database.
Mark WillsTopic AdvisorCommented:
You can GRANT (allow access)
You can DENY (prevent access)

And REVOKE will remove an entry from Privileges / Permissions.

So, you cannot see REVOKED as such, but can see GRANT or DENY permissions.

You can see them...
SELECT user_name(grantee_principal_id) as [USER_NAME], STATE_DESC, OBJECT_NAME(major_id) as [OBJECT_NAME],*
FROM sys.database_permissions
-- and add a where clause to zero in
WHERE grantee_principal_id = USER_ID('SomeUserID')
-- or use major_id for an object_id
--or state_desc for GRANT or DENY

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Haren MorseAuthor Commented:
I can still see the stored procedure with grant permission in the sys.database_permissions table. But when I check it through the GUI it's not there. Is the grant permissions deleted from some other table ?
5 Ways Acronis Skyrockets Your Data Protection

Risks to data security are risks to business continuity. Businesses need to know what these risks look like – and where they can turn for help.
Check our newest E-Book and learn how you can differentiate your data protection business with advanced cloud solutions Acronis delivers

What you seeing in the table is the sp, the determination whether the specific user has rights as an additional joined query to user.
Mark WillsTopic AdvisorCommented:
Permissions can happen at a number of different levels, might be inherited, applied to a role and so on.

Using the GUI you would need to check / select user or role or app roles corresponding to what you can see in the table. And even then it might be generic like 'guest' or 'public'

Unfortunately it can get a bit cryptic....
Mark WillsTopic AdvisorCommented:

How are you going ?
Haren MorseAuthor Commented:
Hi All, It was my mistake and I was seeing a similarly named stored procedure. The stored procedure was not in the sys.database_permissions  table. Thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.