February 13, 2017 at 11:15 pm
Hello
I need to query all accounts in sql server instance who has only read-only access
to all databases in the instance. Can you please help to create me a query? Thanks!
February 14, 2017 at 4:36 am
What do you mean by read-only access? Do you mean members of db_datareader? How about users with VIEW ANY DEFINITION permission? What about those who have SELECT permission on some or all tables and views? How about EXECUTE permission on functions? Would a user that has INSERT, UPDATE or DELETE permissioon on a single object be excluded?
John
February 14, 2017 at 4:42 am
John Mitchell-245523 - Tuesday, February 14, 2017 4:36 AMWhat do you mean by read-only access? Do you mean members of db_datareader? How about users with VIEW ANY DEFINITION permission? What about those who have SELECT permission on some or all tables and views? How about EXECUTE permission on functions? Would a user that has INSERT, UPDATE or DELETE permissioon on a single object be excluded?John
Hi John,
Thanks for your reply. Yes, that includes all those that you mentioned. Yes, excluding insert update delete permissions.
Thanks!
February 14, 2017 at 5:08 am
Those were rhetorical questions, meant to make you think about what you actually require, rather than just answer yes. Never mind - I'll take you at your word. The query below will return users for one database. You'll need to use a technique such as sp_MSForeachDB to run it against all databases, and you'll need to join to sys.server_principals if you want the names of the instance-level logins (which may differ from the names of the database-level users). You'll probably also want to exclude users who are also members of database roles that confer DDL or DML write permissions (but you didn't actually ask for that so I didn't include it
WITH YesPlease AS (
SELECT u.name
FROM sys.database_principals u
JOIN sys.database_role_members m ON u.principal_id = m.member_principal_id
JOIN sys.database_principals r ON m.role_principal_id = r.principal_id
WHERE r.name = 'db_datareader'
UNION
SELECT u.name
FROM sys.database_principals u
JOIN sys.database_permissions p ON u.principal_id = p.grantee_principal_id
JOIN sys.objects o ON p.major_id = o.object_id
WHERE (p.permission_name = 'SELECT'
OR (p.permission_name = 'EXECUTE' AND o.type IN ('FN','FS','FT','IF','TF') )
OR p.permission_name = 'VIEW DEFINITION')
AND p.state_desc = 'GRANT'
)
, NoThanks AS (
SELECT u.name
FROM sys.database_principals u
JOIN sys.database_permissions p ON u.principal_id = p.grantee_principal_id
WHERE p.permission_name IN ('INSERT','DELETE','UPDATE')
)
SELECT name
FROM YesPlease
EXCEPT
SELECT name
FROM NoThanks
John
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply