question about username dbo

Question

Post reply

question about username dbo

cphite

SSCarpal Tunnel

Points: 4272
More actions
September 21, 2021 at 3:31 pm

#3931531
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?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16014 More actions · Answer 1

first step is to change the owner of the database to be either a lowlevel SQL Login created just for that or to SA

ALTER AUTHORIZATION ON database::testdb TO DisabledLogin;

then create the users as needed and revoke perms

cphite SSCarpal Tunnel Points: 4272 More actions · Answer 2

Thanks.

One additional question... if I deny these permissions, is it possible to grant them on specific tables, or does the deny override that?

For example, we have a couple of queue tables where a developer might go in and update some information - can I grant access to that specific table even with the DENY UPDATE in place?

frederico_fonseca SSCoach Points: 16014 More actions · Answer 3

no

what you need to do is not add them to any role that allows data changes (or ddl changes) - e.g. do not add them to db_datawritter or db_ddladmin - and then grant perms on specific objects.

for that matter you can even not add them to ANY default db role - and create a role with just the specific permissions they need to that db.