question

sqlqa avatar image
sqlqa asked

Login have db_datareader privileges but that login still able to insert/updat/delete on table why?

Hi, Recently I see that login have db_datareader permission on database and object but still it can able to update on tables SP Code: create proc update_EmployeeRough as begin update EmployeeRough set salary=salary+8000 where substring(empname,1,1)='e' end see the sp permission screen shot and user permission screen shot Can u anyone tell me how to solve this?
usersdata-reader
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

This site runs off of votes. Please mark all helpful answers by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate that by clicking on the check mark next to that answer.
1 Like 1 ·
eghetto avatar image
eghetto answered
The reason for this is the [Ownership Chain][1]. As PSR has EXECUTE permission, he can execute the SP (dbo schema). Although he has no permission on dbo.EmployeeRough, SQL Server does not evaluate permission on it for PSR because it is owned by the same owner who owns dbo.EmployeeRough. [1]: http://technet.microsoft.com/en-us/library/ms188676(v=sql.105).aspx
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
the clue is in your question... [`db_datareader` documentation][1] says > Members of the db_datareader fixed > database role can run a SELECT > statement against any table or view in > the database. It does not grant permissions to modify the data. For that, you need to use the [`db_datawriter`][2] role. Note that `db_datawriter` does not include permissions to read the data that you're writing. You need to use both. Also note that that doesn't include permissions to execute stored procedures. You'll need to GRANT EXECUTE separately. If you're doing this for multiple users, it can get a bit... messy. This is a good argument for setting up your own database role, and assigning users (be they SQL server logins, Windows logins, or Active Directory Groups) to that role. [1]: http://technet.microsoft.com/en-us/library/ms188629(v=sql.90).aspx [2]: http://technet.microsoft.com/en-US/library/ms175039(v=sql.90).aspx
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I hope so: Grant execution permission on the update-SP to PSW user, not PSR user (Revoke his permission). Good luck!
1 Like 1 ·
Hi eghetto So how to solve this?
0 Likes 0 ·
Do not grant execute permission on the update-SP to your user. Is that an option?
0 Likes 0 ·
Hi eghetto, So PSR-----------db_datareader PSW-----------db_datareader & db_datawriter So I have to grant execute permission on update SP to PSW user. It could solve my problem isnt?
0 Likes 0 ·
Hi eghetto it solve my problem.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.