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

Database owner Expand / Collapse
Author
Message
Posted Wednesday, June 02, 2010 4:25 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, September 20, 2013 11:45 AM
Points: 63, Visits: 255
I have lots of databases that were created by several people - so they ended up being 'owned' by each of the creators. I would like (if possible) to have them all "owned" by the local admin account - not an individual. What should I look out for before I change the owner of these databases? I don't want anything to stop working in the name of conformity. Thanks!!
Post #931741
Posted Wednesday, June 02, 2010 6:45 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:55 AM
Points: 6,544, Visits: 8,754
What you're really talking about is changing what schema the tables belong to.

What you have to watch out for is any query that uses > 1 part naming convention:
<server>.<database>.<schema>.<table>

So, if the table belongs to schema xyz, and you reassign it to the dbo schema, then any query that references xyz.table specifically will fail.

If, like many queries, there isn't a schema qualifier, then when that user tries to run the schema, sql will determine that the table doesn't exist in that schema, and will go up the schema-chain and will find it in the dbo schema.

i.e.:
select * from xyz.table will fail, while
select * from table will not fail


Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #931764
Posted Wednesday, June 02, 2010 8:55 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, May 24, 2013 3:49 AM
Points: 69, Visits: 134
shairal_t (6/2/2010)
I have lots of databases that were created by several people - so they ended up being 'owned' by each of the creators. I would like (if possible) to have them all "owned" by the local admin account - not an individual. What should I look out for before I change the owner of these databases? I don't want anything to stop working in the name of conformity. Thanks!!


As you mentioned,
I assume that you want to change the "owner" of database, not the db_owner role, don't you?
As far as i know, sql server have two "owner", one is the "owner" which is created when you create database and can not change, another one is the "db_owner" role which is assigned to user of sql server.
So, if i'am right, i don't know how you can change the "owner" of database?

Regards,

Sol
Post #931802
Posted Thursday, June 03, 2010 12:17 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:48 AM
Points: 2,507, Visits: 3,645
U CHANGE THE OWNER BY

use master
GO
EXEC [DB_NAME].dbo.sp_changedbowner @loginame = N'LOGIN_NEW', @map = false
GO
Post #931856
Posted Friday, June 04, 2010 8:49 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, September 20, 2013 11:45 AM
Points: 63, Visits: 255
Yes - I would like to change the owner by using sp_changedbowner not change the schema - sorry for the confusion. So my question would be, what problems would I run into if I make them all the same? If they were all created by admins, does it even matter if they're the same?
Post #932873
Posted Monday, June 07, 2010 1:39 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:48 AM
Points: 2,507, Visits: 3,645
Post #933354
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse