a question about sql server 2012

Question

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 ?

Answer 1

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?

Answer 2

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

Answer 3

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.