SQL Server Home
as By Design
8/18/2009 7:37:59 AM
User(s) can reproduce this bug
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)
SQL Server 2008 - Enterprise Edition
Tools (SSMS, Agent, Profiler, etc.)
Windows XP Professional
Operating System Language
Steps to Reproduce
do the following first for SQL2005 and then for a SQL2008 instance:
1 in a test database (as "sa"), create a user demoTest
sp_addlogin 'demoTest', 'demoTest12'
2 Create a table:
create table dbo.my_table(id integer primary key identity(1,1), someField varchar(100) null)
3 Assign permissions on the table to the new user:
grant select, update, insert, delete on my_table to demoTest
4 Log in as demoTest and check the INFORMATION_SCHEMA.COLUMN_PRIVILEGES, by running
select * from INFORMATION_SCHEMA.COLUMN_PRIVILEGES where table_name = 'my_table'
Please advise on whether this is a bug or if there is a workaround?
Additional notes (on 19/08/2009):
when I run
execute sp_helptext "information_schema.COLUMN_PRIVILEGES"
on a sql2005 instance, and then copy the results from the above query into a sql2008 and run the query that is generated, I get back the results that I expect.
In SQL2005, I get back what I expect which is data.
In SQL2008, I get back no records at all, when I would expect a list of the fields from the table.
I've tried changing the query to be:
select * from MYDATABASENAME.INFORMATION_SCHEMA.COLUMN_PRIVILEGES where table_name = 'my_table'
in line with http://msdn.microsoft.com/en-us/library/ms186778.aspx but this does not work.
see Actual results.
to post a comment.
Please enter a comment.
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.
on 10/24/2009 at 5:53 PM
We will look into your reported problem and respond shortly.
to post a workaround.
Please enter a workaround.
© 2014 Microsoft