Change all of your database owners

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by E Hendrix » Change all of your database owners

Change all of your database owners

Rate Topic

Display Mode

Topic Options

Author

Message

@STLSQLGuy

Posted Tuesday, September 07, 2010 9:05 PM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, April 02, 2013 4:17 PM
Points: 80, Visits: 203

Comments posted to this topic are about the item Change all of your database owners

thanks, ERH

Post #982012

drwhitaker

Posted Wednesday, September 08, 2010 9:00 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, January 22, 2013 2:07 PM
Points: 16, Visits: 85

Worked Perfect !!!

Post #982381

Randall_M.

Posted Wednesday, September 08, 2010 10:12 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, February 21, 2011 10:31 AM
Points: 8, Visits: 30

I appreciate your script, but I've seen problems in SQL 2000 where the login that was the DB owner lost it's access rights after doing this and took down applications as a result. Have anyone had similar experiences and could you please comment on how you resolved it?
Thanks to all for your responses. Smile

Drive it like you stole it[size="4"][/size] w00t

Post #982456

JC-3113

Posted Thursday, September 09, 2010 9:24 AM

SSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 8:43 AM
Points: 442, Visits: 620

Hi E. Hendrix

I am not sure just what this script does. Does it generate the sql to make the change or does it actually make the change ?

Do you have sample output ?

Thanks
jim

Post #983229

drwhitaker

Posted Thursday, September 09, 2010 9:42 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, January 22, 2013 2:07 PM
Points: 16, Visits: 85

This executes. I executed on a SQL Server 2005 database server with 5 databases and all "user" databases where changed to "sa". Smile

Post #983242

@STLSQLGuy

Posted Thursday, September 09, 2010 1:53 PM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, April 02, 2013 4:17 PM
Points: 80, Visits: 203

I agree you should know your application before you do this. In most cases this should not cause issues especially using SA. That is unless you have SQL Auth turned off then I'd suggest a service account that you use to start the SQL Server Service and/or Agent.
I do know that in older versions of sharepoint you may run into issues if sharepoint expects the database owner to be the same account that runs the sharepoint farm. In that case though you may have bigger issues since that would mean the person who left your company also owns your sharepoint web servers in the farm so good luck with that.

thanks, ERH

Post #983408

@STLSQLGuy

Posted Thursday, September 09, 2010 1:55 PM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, April 02, 2013 4:17 PM
Points: 80, Visits: 203

This one walks through each database, non-system, and changes the owner. You should simply see a list of your databases it executed against in your output.

I would suggest you run 'sp_helpdb' before you run this to capture the current owners....just in case you need to go back.

thanks, ERH

Post #983410

lee.kleckner 43077

Posted Monday, November 08, 2010 9:37 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, October 12, 2011 10:24 AM
Points: 5, Visits: 58

There are plans to remove sp_changedbowner. you can replace with
alter authorization on Database::"dbname" to "username"

Post #1017275

« Prev Topic | Next Topic »

Permissions