Changing Database Ownership

  • Article

In today's version it is not possible to model the ownership of the database you are deploying towards. The ownership is determined based on the person who creates the initial database, SQL Server inherits the information from the security context of the session that invokes CREATE DATABASE.

So if you execute CREATE DATABASE from a sessions under the security context of a Windows authenticated user, say MyDomain\MyUser, the database is owned by this user; if you execute the command under the context of the standard SQL Server security context of 'sa' the database will be owned by 'sa'.

There is no way to set the ownership as part of the CREATE or ALTER DATABASE statement, which is why it was not modeled in the Database project (.dbproj) properties.

If you want to enforce certain ownership you can achieve this by adding a post deployment script which calls sp_changedbowner. These are the step you have to follow.

  1. In Solution Explorer, right click on the Scripts->Post-Deployment node and choose Add->Script...

  2. Name the script ChangeDBOwner.sql

  3. Change the content of the ChangeDBOwner.sql file to:

    -- =============================================
    -- Change Database Owner
    -- =============================================
    if (db_name() = '$(DatabaseName)')
    begin
        print 'Change Database Owner'
        exec sp_changedbowner 'sa'
    end

  4. From Solution Explorer, open the Script.PostDeployment.sql file, this is the default post deployment master file, which contains the include statements to all files you want to include after the build portion of the script.

  5. Add the following line to include the ChangeDBOwner.sql file

    :r .\ChangeDBOwner.sql

  6. Now build and deploy your project, in the output window you should see something like this:

    ------ Build started: Project: Database2, Configuration: Default Any CPU ------
    ------ Deploy started: Project: Database2, Configuration: Default Any CPU ------
            Deploying script C:\data\Visual Studio 2005\Projects\Database2\Database2\sql\Database2.sql to server (local)\SQL90
            Change Database Owner
            C:\data\Visual Studio 2005\Projects\Database2\Database2\sql\Database2.sql --> Server:"(local)\SQL90", Database:"Database2"
    ========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ==========
    ========== Deploy: 1 succeeded, 0 failed, 0 skipped ==========

  7. Make sure to check in your changes including the new file when your project lives under source code control.

If you want to see this modeled in the project, please add your comments to https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=286032

-GertD