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?
 
Máté FarkasDatabase Developer and AdministratorCommented:
Only granted permission are stored. Revoked permissions are not stored because they are deleted from database.
0
 
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

0
 
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 ?
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
arnoldCommented:
What you seeing in the table is the sp, the determination whether the specific user has rights as an additional joined query to user.
0
 
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....
0
 
Mark WillsTopic AdvisorCommented:
Hi,

How are you going ?
0
 
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
0
All Courses

From novice to tech pro — start learning today.