I’ve come across a few occasions where we need to find out permissions for our username on a given table in the database. An easy way of doing this is by means of using the system stored procedure sp_table_privileges. The usage syntax is as follows
sp_table_privileges @table_name ,
@table_owner ,
@table_qualifier ,
@fUsePattern
this will return you list of permissions which are present for the owner (default to current user if passed as NULL). the last parameter designates if pattern matching is to be enabled using wildcards. The result set will be as follows
TABLE_QUALIFIER TABLE_OWNER TABLE_NAME GRANTOR GRANTEE PRIVILEGE IS_GRANTABLE
————————————————————————————————-
VisakhTest dbo Destination dbo dbo DELETE YES
VisakhTest dbo Destination dbo dbo DELETE YES
VisakhTest dbo Destination dbo dbo INSERT YES
VisakhTest dbo Destination dbo dbo REFERENCES YES
VisakhTest dbo Destination dbo dbo SELECT YES
VisakhTest dbo Destination dbo TestSQLLogin SELECT NO
VisakhTest dbo Destination dbo dbo UPDATE YES
As you see from resultset it provides details of all the permissions available on this table for different users. This is because of I’ve executed this from sysadmin account. If you execute this from any other account it shows only permissions available under that account
There’s also an extension of this sp called sp_table_privileges_ex which shows permissions in linked server.
