Database Role

Last Post 25 Jan 2012 01:33 PM by sql-tips. 5 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
sql-tips
New Member
New Member

--
07 Dec 2011 11:07 AM
I have database role and assigned select permissions to the tables, most of the tables works fine. But there are some tables missed to assign this role. How do I query the database to get a list of tables which are not in the database role?
russellb
New Member
New Member

--
07 Dec 2011 12:35 PM
SELECT t.name, schema_name(t.schema_id) [ObjectSchema]
FROM sys.tables t
LEFT JOIN (

SELECT o.name AS [ObjectName],
o.schema_id,
schema_name(o.schema_id) AS [ObjectSchema]
FROM sys.database_permissions AS p
JOIN sys.all_objects AS o ON o.object_id = p.major_id and p.class = 1
JOIN sys.database_prin***ls AS gp ON gp.prin***l_id = p.grantee_prin***l_id
WHERE gp.name = 'Your Role Here'
And o.type = 'U'
And p.state = 'G'
) x
On x.ObjectName = t.name
And x.schema_id = t.schema_id
WHERE x.ObjectName is null
russellb
New Member
New Member

--
07 Dec 2011 12:38 PM
I don't know why the formatting is messed up. should be 
  
P R I N C I P AL _ I D
russellb
New Member
New Member

--
07 Dec 2011 12:39 PM
without the spaces
russellb
New Member
New Member

--
07 Dec 2011 01:25 PM
Easiest thing probably is to execute this (with output to text) then copy/paste the results back in and execute them

SELECT 'GRANT SELECT On [' + schema_name(schema_id) + '].[' + name + '] to [YOUR ROLE HERE];'
FROM sys.tables
WHERE is_ms_shipped = 0;
sql-tips
New Member
New Member

--
25 Jan 2012 01:33 PM
Thanks russellb that works..


Acceptable Use Policy
---