November 17, 2014 at 12:27 pm
Recently, a developer at my office rebuilt our data warehouse on new hardware and with a better file/disk layout. We migrated all the data from our old data warehouse to the new and it is now our production environment.
The user that built the server had their permissions scaled back so that they were no longer a sysadmin and only had read only access granted. However, this user has discovered inadvertently that they can still make changes to the database. I've found when looking a their user mappings, they are connecting to these databases as dbo. When I look at dbo on the affected databases, the User Type is Windows User, the User Name is dbo, and the login name is the developers AD account. This user does not exist under the database Users and should only have access through the db_datareader role. At the server level, this user is only assigned to the Public server role.
I'm obviously missing something but I dont know what it is. It seems odd to me that the dbo user in the affected databases shows this user as it's login. My research on this has not led me to any solutions so I'm hoping someone here can offer insight on how I can set this user to no longer login as dbo on the databases.
November 17, 2014 at 2:02 pm
Sounds like the users AD login is the database owner and thus dbo. Run the following to change the database owner to sa and then grant the user's AD account db_datareader access to each of the databases.
ALTER AUTHORIZATION ON DATABASE::your_db_name TO sa
GO
November 17, 2014 at 2:33 pm
I checked the database properties and it has one of our administrators as the owner rather than the user being addressed.
November 18, 2014 at 2:16 am
the User Type is Windows User, the User Name is dbo, and the login name is the developers AD account. This user does not exist under the database Users and should only have access through the db_datareader role. At the server level, this user is only assigned to the Public server role.
I would go to Security --> [Your login] --> right-click Properties --> User Mapping, then highlight the database name, and in the Database role membership field at the bottom un-tick any database roles that are selected, and tick only the db_datareader one.
Unless you've already tried that?
November 20, 2014 at 2:36 pm
Thanks for the suggestion. I gave it a shot and it unfortunately did not work. When selecting OK to make the change (removing user from db_owner role), an error message is thrown. It says:
Drop member failed for DatabaseRole 'db_owner'. (Microsoft.SqlServer.Smo)
An exception occured while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
Cannot user the special principal 'dbo'. (Microsoft SQL Server, Error: 15405)
The user is not the DB owner, so I'm not certain what the hang up is.
November 21, 2014 at 6:00 am
was this database originally a sql server 2000 database?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 21, 2014 at 7:50 am
Drop & re-create the user ?
November 21, 2014 at 10:02 am
No, it's a 2014 database created from scratch. I haven't tried dropping and recreating the user. I'll give that a shot.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply