Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 483537 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 8/18/2009 7:37:59 AM
Access Restriction Public



In SQL2005, I used to be able to view (for the user logged in) a list of all fields to which they had permission to select or update using:


I've since installed SQL2008 and the same query returns nothing on the same database (restored from a SQL2000 instance)
Sign in to post a comment.
Posted by Microsoft on 11/16/2009 at 3:06 PM
This works by design and change in behavior in SQL 2008. Here is a detailed explanation of the issue:
According to ANSI, TABLE_PRIVILEGES view should return "privileges on tables defined in this catalog that are available to or granted by a given user." It seems ANSI does not bother return "indirect grant", meaning that role membership other than public is not considered. (SEE below copied from ANSI document)

SQL Server supports DENY permission model which ANSI does not. This view of SQL Server has returned privileges on tables that
are available to current user directly or indirectly using intrinsic permissions(), which reflects role membership, deny, permission hierarchy etc. SQL Server also filters out table privileges where same-type column level privileges is revoked or denied. This seems unnecessary as far as ANSI compliant requirement is concerned.

As of today, TABLE_PRIVILEGES view does not show permissions granted by current user, for a valid reason -- To reflect equivalent information on permissions granted by current user, we have to impersonate the grantee to collect this, and it is difficult and error prone.

Posted by Microsoft on 10/24/2009 at 5:53 PM

We will look into your reported problem and respond shortly.