query all user accounts

  • 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!

  • 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

  • John Mitchell-245523 - Tuesday, February 14, 2017 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

    Hi John,

    Thanks for your reply. Yes, that includes all those that you mentioned.  Yes, excluding insert update delete permissions.

    Thanks!

  • 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