Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2001

    Unanswered: How to assign DB owner roles to all databases in a SQL Server

    Is there a simple SQL/stored procedure to assign a user to be db_owner on all databases in a SQL server?

    P.S. in our env., we have SQL Server 2000, 2005 and 2008


  2. #2
    Join Date
    Jan 2003
    Provided Answers: 11
    There is no simple way of doing this (unless you want to grant sysadmin rights, which would be bad). You will have to either do this with dynamic SQL, or write a query to output commands like:
    USE dbname; exec sp_adduser loginname, username; exec sp_addrolemember db_owner username;
    I am not sure if sp_adduser is still supported in SQL2008, so you may need to play around with the query to generate those commands. If you know powershell, you can probably come up with a better more flexible solution.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts