April 26, 2013 at 10:13 am
I'm going over my server security to remove our previous DBA. One of the odd things a found on our security server, among other things, is that when I check the properties of the database user dbo the login name mapped to it is our previous DBA windows domain account. So I have a couple of questions:
1- is this right? Shouldn't the dbo database user be mapped to sa?
2- how got it mapped like that? Any ideas?
3- if it's wrong, how do I change it and to what?
Security seems to be running out of whack here and we need to keep it under control. I'm trying to make the best out of it and to make sense of it as well. Any ideas would be very appreciated.
Thanks.
April 26, 2013 at 11:46 am
It might be the UI design causing confusion, or being plain wrong.
What does this say?
USE YourDatabaseName;
GO
SELECT sp.name
FROM sys.database_principals dp
JOIN sys.server_principals sp ON dp.principal_id = sp.principal_id
WHERE dp.name = 'dbo';
GO
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 27, 2013 at 12:24 am
A user maps to dbo when they are the owner of the database. Open the database properties and check\change the account who owns it.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 29, 2013 at 7:12 am
USE YourDatabaseName;
GO
SELECT sp.name
FROM sys.database_principals dp
JOIN sys.server_principals sp ON dp.principal_id = sp.principal_id
WHERE dp.name = 'dbo';
GO
The script above gives me 'domain\username'
April 29, 2013 at 7:39 am
The user that I'm trying to change is in fact the owner of the database. Would the best practice be change the owner of the database to the sa account using exec changedbowner 'sa'?
April 29, 2013 at 7:47 am
Ok, that confirms that the person owns the database. Chances are they created the database initially since SQL Server will make the database creator the database owner and many people do not know this or do not bother to change it.
SELECT SUSER_SNAME(owner_sid) as owner_name
FROM sys.databases
WHERE name ='YourDatabaseName';
You can use ALTER AUTHORIZATION to change the owner to sa if you wish. Some precautions should be taken if cross-database ownership chaining is being used, or if the Login in question needs access to the database going forward and is not in the sysadmin Group or does not have a stand-alone User in the database.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply