question

danielzhou-7000 avatar image
0 Votes"
danielzhou-7000 asked danielzhou-7000 commented

a question about sql server 2012

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-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered danielzhou-7000 commented

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?

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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.

0 Votes 0 ·

What permissions are granted on the table?

0 Votes 0 ·

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?

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ErlandSommarskog edited

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')
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Criszhan-msft avatar image
0 Votes"
Criszhan-msft answered danielzhou-7000 commented

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.

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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.

0 Votes 0 ·

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

0 Votes 0 ·

Hi,
Is there any update on this case?

0 Votes 0 ·

No,still in the problem

0 Votes 0 ·