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

Change all of your database owners Expand / Collapse
Author
Message
Posted Tuesday, September 7, 2010 9:05 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, November 14, 2013 11:03 AM
Points: 80, Visits: 204
Comments posted to this topic are about the item Change all of your database owners


thanks, ERH
Post #982012
Posted Wednesday, September 8, 2010 9:00 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 5:13 AM
Points: 16, Visits: 86
Worked Perfect !!!
Post #982381
Posted Wednesday, September 8, 2010 10:12 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, December 18, 2013 9:25 PM
Points: 10, Visits: 50
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.



Drive it like you stole it[size="4"][/size]
Post #982456
Posted Thursday, September 9, 2010 9:24 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 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
Posted Thursday, September 9, 2010 9:42 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 5:13 AM
Points: 16, Visits: 86
This executes. I executed on a SQL Server 2005 database server with 5 databases and all "user" databases where changed to "sa".
Post #983242
Posted Thursday, September 9, 2010 1:53 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, November 14, 2013 11:03 AM
Points: 80, Visits: 204
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
Posted Thursday, September 9, 2010 1:55 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, November 14, 2013 11:03 AM
Points: 80, Visits: 204
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
Posted Monday, November 8, 2010 9:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 »

Add to briefcase

Permissions Expand / Collapse