Home Dashboard Directory Help
Search

INFORMATION_SCHEMA.COLUMN_PRIVILEGES by Mr_Anderson1


Status: 

Closed
 as By Design Help for as By Design


1
0
Sign in
to vote
Type: Bug
ID: 483537
Opened: 8/18/2009 7:37:59 AM
Access Restriction: Public
0
Workaround(s)
view
0
User(s) can reproduce this bug

Description

Hi,

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:

select * from INFORMATION_SCHEMA.COLUMN_PRIVILEGES

I've since installed SQL2008 and the same query returns nothing on the same database (restored from a SQL2000 instance)
Details
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
Hi,

We will look into your reported problem and respond shortly.

Il-Sung.
Sign in to post a workaround.