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 ?
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 ?
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?
i have check the server role,exsts bulkadmin,dbcreator,diskadmin,processadmin,public,secutityadmin,serveradmin,setupadmin,sysadmin.
there is "readonly" in the login_name list,and it in public role ,not in sysadmin role.
no any setup about permission on the table , acount "readonly" face all of table and views,not one or a litters. i can not setup every table for permissions,right?
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')
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.
This is why i am here ,i am so strange too.i have check "readonly"'s permissions again and again ,but still not find where the wrong setup is.
Hi,
Have you tried a new test login.
Also check if there are orphaned users in the database.
USE databasename;
sp_change_users_login @Action='Report';
13 people are following this question.