Ignoring users when deploying a database project from VS2008

Question

I am currently working on an application that has different permissions/users for the local development environment and test. I want to be able to ignore the users and permissions when deploying to either environment. Under the .sqldeployment file, there seems to only be options for ignoring permissions (IgnorePermissions) and role membership (IgnoreRoleMembership) for a user, but not for ignoring the users themselves. Is this possible?

Answer 1

Sorry, not currently possible in the 2008 version of Visual Studio.

Answer 2

There isn't an obvious way - lots of requests for this feature here. The command-line tool, vsdbcmd.exe, suffers from the same problem.

As a workaround, I use a PowerShell script to remove users and schema authorization from the deployment SQL script. This could be run as a post-deploy step (not tried), or a TeamCity build step etc.

$rxUser = New-Object System.Text.RegularExpressions.Regex "PRINT[^;]*;\s*GO\s*CREATE USER \[[^\]]*](\s*WITH DEFAULT_SCHEMA = \[[^\]]*])?;\s*GO\s*", SingleLine
$rxSchema = New-Object System.Text.RegularExpressions.Regex "PRINT[^;]*;\s*GO\s*CREATE SCHEMA \[[^\]]*]\s*AUTHORIZATION \[[^\]]*];\s*GO\s*", SingleLine

Get-Item "*.sql" | ForEach-Object {
    $input = [System.IO.File]::ReadAllText($_.FullName)
    $input = $rxUser.Replace($input, "")
    $input = $rxSchema.Replace($input, "")

    $output = [System.IO.File]::CreateText($_.FullName)
    $output.Write($input)
    $output.Close()
}

The regex could be modified to ignore DROP USER statements too.