I have been asked to revoke access from a few developers in SQL Server - basically we want them to be able to read anything, but deny them access to alter objects or data.
The users in question are granted access as part of an AD group. So my thought was to create a user for each of them on each database, and then deny access using the following:
create user [domain\user] from login [domain\user]
deny alter on schema :: dbo to [domain\user]
deny insert on schema :: dbo to [domain\user]
deny update on schema :: dbo to [domain\user]
deny delete on schema :: dbo to [domain\user]
The problem I am running into is that a few of the databases were created by one of the developers in question. So, for those databases, the dbo user has the same sid as that user. So when I try to create the user on that database, it throws an error that the login already has an account under a different user name.
So my questions: One, is it possible to change the SID for the DBO user so that this problem goes away?
Two, if not, and if I deny those permissions to domain\user is that effectively the same as denying them to DBO? And won't that cause a bunch of problems?
Three, is this the completely wrong way to be doing this?