I have a sql server user that our application is connecting as. The user has been granted db_datareader and db_datawriter as well as Grant Execute on each stored procedure that it needs to use.
These are the permissions I see when I veiw the usermapping on the Login and when I view the user under the database.
Given these permissions I don't think the user should be able to alter a procedure; however, when my developer logs in as that user they are able to update the stored procedures.
Am I wrong about what these permissions allow? Is there someplace I can look to find out about permissions that may not being showing up in the UI? Or do I need to explicitly deny alter on the stored procedures for that user?
Best Answer
Do you see anything that has been granted or role membership that shouldn't exist?
It is possible these developers are in the
db_owner
role without your knowledge or have been grantedALL
on thedbo
or another schema.Make sure you execute these statements in the right database, and also validate that your users are logging in as who they say they are logging in as. Perhaps they have the sa password and you don't know it. Finally, you should make sure that the database principal maps to the correct server-level login.