Post reply

How can a db_datareader database role create a table?

  • September 27, 2019 at 2:10 am

    #3683827

    In SSMS, I created a user and gave that user a database role of db_datareader.  I then logged in as that user and I was able to select any table and view in the database the user was assigned to.  However, to my surprise  I was able to create a new table, even though the user profile has a db_datareader role.

    I thought reading meant simply using SELECT statements and viewing data.  How can somebody with read-only access create a new table?

  • September 27, 2019 at 5:07 am

    #3683842

    You say you were "about" to create a new table.  Did you actually try to finish that task?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • September 27, 2019 at 5:11 am

    #3683846

    Jeff Moden wrote:

    You say you were "about" to create a new table.  Did you actually try to finish that task?

    Thanks for pointing that out.  I meant to say I did create a new table.  I corrected my post.

  • September 27, 2019 at 8:39 am

    #3683888

    In the code below, please replace "UserName" with the name of the user, "LoginName" with the name of the login to which the user is mapped, "DBName" with the name of the database and "TableName" with the name of the table you created.  Please then run and post the results.

    USE DBName;

    SELECT
    permission_name
    ,state_desc
    FROM sys.server_permissions
    WHERE SUSER_NAME(grantee_principal_id) = 'LoginName';

    SELECT
    r.name AS RoleName
    ,SUSER_NAME(m.member_principal_id) AS LoginName
    FROM sys.server_role_members m
    JOIN sys.server_principals r ON m.role_principal_id = r.principal_id
    WHERE SUSER_NAME(m.member_principal_id) = 'LoginName';

    SELECT
    permission_name
    ,state_desc
    FROM sys.database_permissions
    WHERE USER_NAME(grantee_principal_id) = 'UserName';

    SELECT
    r.name AS RoleName
    ,USER_NAME(m.member_principal_id) AS LoginName
    FROM sys.database_role_members m
    JOIN sys.database_principals r ON m.role_principal_id = r.principal_id
    WHERE USER_NAME(m.member_principal_id) = 'UserName';

    SELECT l.name
    FROM sys.databases d
    JOIN sys.server_principals l ON d.owner_sid = l.sid
    WHERE d.name = 'DBName';

    SELECT SCHEMA_NAME(schema_id) 
    FROM sys.tables
    WHERE name = 'TableName';

    John

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply