a question about sql server 2012

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

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.
8,462 questions
No comments
{count} votes

3 answers

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

    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. answered 2021-05-05T21:53:29.617+00:00
    Erland Sommarskog 67,481 Reputation points Microsoft MVP

    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')
    
    No comments

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

    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.