Change all of your database owners

  • @STLSQLGuy

    Default port

    Points: 1476

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


    thanks, ERH
  • drwhitaker

    SSC-Addicted

    Points: 404

    Worked Perfect !!!

  • Randall_M.

    SSC Veteran

    Points: 258

    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] :w00t:

  • JC-3113

    SSCrazy Eights

    Points: 8366

    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

  • drwhitaker

    SSC-Addicted

    Points: 404

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

  • @STLSQLGuy

    Default port

    Points: 1476

    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
  • @STLSQLGuy

    Default port

    Points: 1476

    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
  • lee.kleckner 43077

    SSC Rookie

    Points: 33

    There are plans to remove sp_changedbowner. you can replace with

    alter authorization on Database::"dbname" to "username"

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the script.

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply