Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm using MS SQL Server 2008, and I am trying to get a complete listing of all privileges assigned to all grantees by running this query:

select * from INFORMATION_SCHEMA.TABLE_PRIVILEGES

However, right now I'm only getting back records associated with the "public" grantee. I've tried adding the db_owner role to this user account, and I've also run this query within the database:

GRANT VIEW DEFINITION TO myuser

Neither of these actions has changed the response from the above query - still only getting public grantee records. What is the minimum setting I need to do to grant the user the rights to all the records?

Thanks

share|improve this question

1 Answer 1

up vote 2 down vote accepted

This information_schema view (my bold)

Returns one row for each table privilege that is granted to or granted by the current user in the current database.

This doesn't take into account roles etc: it all works on DATABASE_PRINCIPAL_ID which can be seen if you look at the definition of INFORMATION_SCHEMA.TABLE_PRIVILEGES

Personally, I'd just use sys.database_permissions and not bother with the information_schema rubbish. This relies solely on Meta data visibility to filter rows, so you'll see the actual permissions unfiltered by the INFORMATION_SCHEMA.TABLE_PRIVILEGES view which adds a further filter that bollixes you.

For completeness, here is the view. Note the implicit "old style" JOIN :-)

CREATE VIEW INFORMATION_SCHEMA.TABLE_PRIVILEGES
AS
SELECT  
    USER_NAME(p.grantor_principal_id)   AS GRANTOR,
    USER_NAME(p.grantee_principal_id)   AS GRANTEE,
    DB_NAME()                       AS TABLE_CATALOG,
    SCHEMA_NAME(o.schema_id)            AS TABLE_SCHEMA,
    o.name                          AS TABLE_NAME,
    convert(varchar(10), CASE p.type
        WHEN 'RF' THEN 'REFERENCES'
        WHEN 'SL' THEN 'SELECT'
        WHEN 'IN' THEN 'INSERT'
        WHEN 'DL' THEN 'DELETE'
        WHEN 'UP' THEN 'UPDATE'
        END)                        AS PRIVILEGE_TYPE,
    convert(varchar(3), CASE p.state
        WHEN 'G' THEN 'NO'
        WHEN 'W' THEN 'YES'
        END)                        AS IS_GRANTABLE
FROM
    sys.objects o,
    sys.database_permissions p
WHERE
    o.type IN ('U', 'V')
    AND p.class = 1
    AND p.major_id = o.object_id
    AND p.minor_id = 0  -- all columns
    AND p.type IN ('RF','IN','SL','UP','DL')
    AND p.state IN ('W','G')
    AND (p.grantee_principal_id = 0
        OR p.grantee_principal_id = DATABASE_PRINCIPAL_ID()
        OR p.grantor_principal_id = DATABASE_PRINCIPAL_ID())
share|improve this answer
    
I don't understand - are you saying the user must be specifically granted permission to the "information_schema.table_privileges" view? –  Jake Feasel Dec 13 '11 at 20:55
    
No, see my update. It doesn't take into account role membership because it's stupid. Don't use it. –  gbn Dec 13 '11 at 21:01
    
I was able to change my code to use the sys views, and it works well. Thanks for your help. –  Jake Feasel Dec 13 '11 at 22:23

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.