a question about sql server 2012

daniel zhou 21 Reputation points
2021-05-05T09:21:22.503+00:00

hi,someone,thanks your view frist.
i have create a new account,"readonly",belong db._datareader,but i find i can "insert table () values ()",it is vaild still.who can tell me why and how to ?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,321 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Olaf Helper 39,181 Reputation points
    2021-05-05T09:38:49.177+00:00

    In SSMS do a right-mouse click on the database user => Properties => Secruables => Effective to see all rights of the user.
    If there are no further permissions then check his server role memberships; may be it's in SysAdmin role?


  2. Erland Sommarskog 99,546 Reputation points MVP
    2021-05-05T21:53:29.617+00:00

    The fact it has readonly in the name, will not make readonly just because of that.

    One way to investigate this is to run:

    EXECUTE AS USER = 'this_readonly_user'
    go
    SELECT * FROM sys.user_token
    go
    REVERT
    

    This will list all database roles the user is a member of, directly or indirectly. Maybe one of these have INSERT permission on the table.

    Also check what permissions that have been granted on the table:

    SELECT * FROM sys.database_permissions WHERE major_id = object_id('thistable')
    
    0 comments No comments

  3. Cris Zhan-MSFT 6,601 Reputation points
    2021-05-06T06:28:43.043+00:00

    Hi,

    Please check the permissions of the login and database user "readonly" respectively(SSMS> Logins/Users> Properties> Secruables > Effective ).

    If you simply create a login in SSMS and default to a member of the public server role, map it to a database user(member of db_datareader role) in a database, and it is not granted Any additional permissions. Using this login connect to SQL Server, It should not have insert permission to the table in this database.

    You can try to recreate a login and map it to a database user in the same way. Then use that login to connect to SQL Server through SSMS and try the insert commands.