How do I get SSDT Publish to not generate REVOKE CONNECT scripts

Question

We have started using SSDT to manage our script deployments. Initially we used Schema Compare and unchecked the parts we did not want to deploy. We now have a need to use Pre and Post-Deploy scripts which only get included with the Publish functionality and we are also trying to clean up the bits we were excluding.

There are 2 differences added in the Publish that were not in the Schema Compare that I can't seem to figure out even though the settings are as close as I can make them.

The first is that the Publish script wants to drop statistics for a table that do not exist in the dacpac even though DropStatisticsNotInSource is set to false. I was able to work around this based on the DeploymentFilterContributor answer in SSDT/SqlPackage drops statistics.

The second continues to stump me though. Most application security is via grants to roles and various system accounts are made members depending on enironment however there are also users not in roles in the dev databases (often admins but also utility who have various permissions). We have different users in different environments. I have a long term goal of only having users in roles but we are not there yet. In the meantime we need to exclude the users from getting dropped. I started by setting Exclude Users, ExcludeLogins, and ExcludeRoleMembership which is what we used in the Schema Compare and it worked great. However in the Publish script we get REVOKE CONNECT scripts (but not DROP LOGIN or DROP USER).

I have read Prevent dropping of users when publishing a DACPAC using SqlPackage.exe and Create User in dacpac deployed by SqlPackage.exe leads to login failed SqlException (this one seems like a similar script result with different aims and also has no answer). I have tried to use the AgileSqlClub.DeploymentFilterContributor with IgnoreType(Login), IgnoreType(User), IgnoreType(RoleMembership), as well as IgnoreType(Permissions), IgnoreSecurity. The first 3 had no effect. The last 2 (I also tried through the equivalent SSDT config options) also removed GRANT scripts that I want to include against the db roles.

I feel like I should be able to do this without creating a custom DeploymentContributor and without scripting all of these users post-deploy. How can I get rid of these REVOKE CONNECT calls?

Also if someone knows if Visual Studio is actually calling the SqlPackage.exe in its path or is using a library instead I would appreciate the info...I can't seem to get any of the 5 SqlPackage.exes on my machine to work with the built .dacpac and .publish.xml file that VS uses.

I am including the full Publish Profile for reference:

<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="14.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
    <AdditionalDeploymentContributors>AgileSqlClub.DeploymentFilterContributor</AdditionalDeploymentContributors>
    <!--AdditionalDeploymentContributorArguments>SqlPackageFilter0=IgnoreType(Statistics);SqlPackageFilter1=IgnoreType(Login);SqlPackageFilter2=IgnoreType(User);SqlPackageFilter3=IgnoreType(RoleMembership);SqlPackageFilter4=IgnoreSchema(SEQUENCES)</AdditionalDeploymentContributorArguments-->
    <!--AdditionalDeploymentContributorArguments>SqlPackageFilter4=IgnoreSchema(SEQUENCES)</AdditionalDeploymentContributorArguments-->
    <AdditionalDeploymentContributorArguments>SqlPackageFilter0=IgnoreType(Statistics);SqlPackageFilter1=IgnoreSecurity</AdditionalDeploymentContributorArguments>
    <IncludeCompositeObjects>True</IncludeCompositeObjects>
    <TargetDatabaseName>BRM</TargetDatabaseName>
    <DeployScriptFileName>BRM.sql</DeployScriptFileName>
    <TargetConnectionString>Data Source=mydb;Integrated Security=True;Persist Security Info=False;Pooling=False;MultipleActiveResultSets=False;Connect Timeout=60;Encrypt=False;TrustServerCertificate=True</TargetConnectionString>
    <BlockOnPossibleDataLoss>True</BlockOnPossibleDataLoss>
    <ExcludeAggregates>False</ExcludeAggregates>
    <ExcludeApplicationRoles>False</ExcludeApplicationRoles>
    <ExcludeAssemblies>True</ExcludeAssemblies>
    <ExcludeAsymmetricKeys>True</ExcludeAsymmetricKeys>
    <ExcludeBrokerPriorities>True</ExcludeBrokerPriorities>
    <ExcludeCertificates>True</ExcludeCertificates>
    <ExcludeExternalDataSources>True</ExcludeExternalDataSources>
    <ExcludeExternalFileFormats>True</ExcludeExternalFileFormats>
    <ExcludeExternalTables>True</ExcludeExternalTables>
    <ExcludeFilegroups>True</ExcludeFilegroups>
    <ExcludeFileTables>True</ExcludeFileTables>
    <ExcludeFullTextCatalogs>True</ExcludeFullTextCatalogs>
    <ExcludeFullTextStoplists>True</ExcludeFullTextStoplists>
    <ExcludePartitionFunctions>True</ExcludePartitionFunctions>
    <ExcludePartitionSchemes>True</ExcludePartitionSchemes>
    <ExcludeQueues>True</ExcludeQueues>
    <ExcludeRemoteServiceBindings>True</ExcludeRemoteServiceBindings>
    <IgnoreRoleMembership>True</IgnoreRoleMembership>
    <ExcludeRules>True</ExcludeRules>
    <ExcludeSecurityPolicies>True</ExcludeSecurityPolicies>
    <ExcludeServices>True</ExcludeServices>
    <ExcludeSignatures>True</ExcludeSignatures>
    <ExcludeUserDefinedDataTypes>True</ExcludeUserDefinedDataTypes>
    <ExcludeUserDefinedTableTypes>True</ExcludeUserDefinedTableTypes>
    <ExcludeUsers>True</ExcludeUsers>
    <ExcludeXmlSchemaCollections>True</ExcludeXmlSchemaCollections>
    <DropObjectsNotInSource>True</DropObjectsNotInSource>
    <DropPermissionsNotInSource>False</DropPermissionsNotInSource>
    <DropRoleMembersNotInSource>False</DropRoleMembersNotInSource>
    <DisableAndReenableDdlTriggers>False</DisableAndReenableDdlTriggers>
    <IncludeTransactionalScripts>True</IncludeTransactionalScripts>
    <ProfileVersionNumber>1</ProfileVersionNumber>
    <DropStatisticsNotInSource>False</DropStatisticsNotInSource>
    <ExcludeLogins>True</ExcludeLogins>
    <ExcludeAudits>True</ExcludeAudits>
    <ExcludeClrUserDefinedTypes>True</ExcludeClrUserDefinedTypes>
    <ExcludeCredentials>True</ExcludeCredentials>
    <ExcludeCryptographicProviders>True</ExcludeCryptographicProviders>
    <ExcludeDatabaseScopedCredentials>True</ExcludeDatabaseScopedCredentials>
    <ExcludeDatabaseAuditSpecifications>True</ExcludeDatabaseAuditSpecifications>
    <ExcludeEndpoints>True</ExcludeEndpoints>
    <ExcludeErrorMessages>True</ExcludeErrorMessages>
    <ExcludeEventSessions>True</ExcludeEventSessions>
    <ExcludeLinkedServerLogins>True</ExcludeLinkedServerLogins>
    <ExcludeLinkedServers>True</ExcludeLinkedServers>
    <ExcludeRoutes>True</ExcludeRoutes>
    <ExcludeSearchPropertyLists>True</ExcludeSearchPropertyLists>
    <ExcludeServerAuditSpecifications>True</ExcludeServerAuditSpecifications>
    <ExcludeServerRoleMembership>True</ExcludeServerRoleMembership>
    <ExcludeServerRoles>True</ExcludeServerRoles>
    <ExcludeServerTriggers>True</ExcludeServerTriggers>
    <DoNotDropLogins>True</DoNotDropLogins>
    <DoNotDropUsers>True</DoNotDropUsers>
    <DoNotDropRoleMembership>True</DoNotDropRoleMembership>
    <DoNotDropServerRoleMembership>True</DoNotDropServerRoleMembership>
  </PropertyGroup>
  <ItemGroup>
    <SqlCmdVariable Include="mscrm">
      <Value>mscrm</Value>
    </SqlCmdVariable>
    <SqlCmdVariable Include="sysdb">
      <Value>sysdb</Value>
    </SqlCmdVariable>
  </ItemGroup>
</Project>

VS2015 Update 3 and SSDT 14.0.61712.050 (latest I think).

Villarreal

Hi @SMM, did you ever manage to resolve this issue? I am encountering the same problem. – Lewandowski

@Lewandowski I have posted a partial answer that might help you along. – Villarreal

It may not have been available at the time you originally posted, but did you try ExcludeObjectTypes? /p:ExcludeObjectTypes=Users;Logins;RoleMembership;Permissions;Credentials;DatabaseScopedCredentials – Micrometer

Answer 1

So, as a follow up, I have not satisfactorily resolved this and have been pulled away from it for some time. I'm not even sure it is still an issue in the latest build. However, for anyone still looking into this, here is the path that I started following with mixed results. I will leave it up to someone else to continue.

I decided to try directly calling sqlpackage.exe and it turns out that this seems to resolve this particular issue as I recall, but then I ran into other oddities. Some of the issues revolved around which version of sqlpackage to use with which SQL server. Anyways you can try this yourself and if you have look reply in here or post a more complete answer.

1. Create a publish XML the way you think it should be from Visual Studio. I think I right-clicked|Publish on the database and from the dialog I was able to save the publish XML file.
2. Have a look through the XML file and make sure everything is as needed (or at least as expected :-)).
3. Call with a batch file: "<path to sqlpackage>\sqlpackage.exe" /a:script /pr:"MyDB.Publish.xml" /sf:"MyDB.dacpac" /dsp:"c:\temp\sqlpackage test\deploy.sql" /p:DropStatisticsNotInSource=False

Again, I am offering this as is but feel free to edit the answer or post your own more complete answer if you get something working nicely.

Villarreal

Is this a bug or some expected behaviour? When I want to add new users and deploy them in the next environment the publish scripts generates the create user and right after a revoke connect which is damaging everthing. I added a post deployment script with the grants again and it works, but it should also work in another way. – Complot

I feel like it is a bug. We never did get this working satisfactorily. I'm not sure about the users as we omit users from the list of objects and assign grants to roles instead (different users are assigned to those roles in different environments). I was called away to other more pressing projects. I believe the person who deals with the deployments still uses the Visual Studio schema compare to generate scripts instead of publishing dacpacs. – Villarreal

Answer 2

Add this to the publish profile

<ExcludeUsers>True</ExcludeUsers>
<ExcludeLogins>True</ExcludeLogins>
<IgnorePermissions>True</IgnorePermissions>
<IgnoreRoleMembership>True</IgnoreRoleMembership>

Maryland

3 of the 4 were already there. IgnorePermissions can't be set because the permissions need to be set appropriately for the roles. – Villarreal

Answer 3

You could add a "GRANT CONNECT TO [YourUser]" after your create user statement in SSDT? It will override the revoke connect execution.

CREATE USER [YourUser] FOR EXTERNAL PROVIDER
GO
GRANT CONNECT TO [YourUser]
GO

Ohmage

Please avoid asking questions in answer (even rhetoric ones). It risks being misunderstood and flagged as "not an answer". – Ridings

Thanks for the suggestion. Because we have different users in different pre-production databases, we don't actually script most of the users in SSDT and we use the ExcludeUsers, etc. flags mentioned in the question to make sure that they are ignored. And generally they are (user not dropped, etc.) but we do get these REVOKE CONNECTs - and only when using Publish (not when doing a VS schema compare). – Villarreal