Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

dbo user in the database has a domain login account Expand / Collapse
Author
Message
Posted Friday, April 26, 2013 10:13 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 8:29 AM
Points: 163, Visits: 470
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.
Post #1447084
Posted Friday, April 26, 2013 11:46 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:34 AM
Points: 7,098, Visits: 12,606
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
Post #1447114
Posted Saturday, April 27, 2013 12:24 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:41 PM
Points: 6,318, Visits: 13,623
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"
Post #1447226
Posted Monday, April 29, 2013 7:12 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 8:29 AM
Points: 163, Visits: 470
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'
Post #1447494
Posted Monday, April 29, 2013 7:39 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 8:29 AM
Points: 163, Visits: 470
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'?
Post #1447519
Posted Monday, April 29, 2013 7:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:34 AM
Points: 7,098, Visits: 12,606
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
Post #1447527
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse