2

TL;DR: Is it possible to exclude permissions granted on a schema when doing a schema comparison in Visual Studio 2013?

I have a user account that is only present on production servers, and an identical user that is only on non-production servers. These users are granted execute at the database schema level.

I run a schema comparison in Visual Studio 2013 (using SSDT version 12.0.50927.0) and when presented with the differences I exclude all of them:

VS2013 schema comparision

But the change script generated always wants to force a grant/revoke on these users:

GO
PRINT N'Dropping Permission...';

GO
REVOKE EXECUTE
ON SCHEMA::[dbo] TO [NonProductionUser] CASCADE;

GO
PRINT N'Creating Permission...';

GO
GRANT EXECUTE
ON SCHEMA::[dbo] TO [ProductionUser];

After doing some research, I found that it was possible to exclude these statements from the update script in previous versions. When running the same schema comparison in Visual Studio 2010 (using SSDT version 10.3.31009.2), I'm presented with the option of excluding differences on the schema:

VS2010 schema Comparision

When I exclude the schema differences, the script generated does not include the REVOKE/GRANT statements. Saving the schema comparison and opening the scmp file shows the following XML has been generated:

<ExcludedSourceElements>
    <SelectedItem Type="Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlPermissionStatement, Microsoft.Data.Tools.Schema.Sql, Version=10.3.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a">
      <Name>Grant.Execute.Schema</Name>
      <Name>ProductionUser</Name>
      <Name>dbo</Name>
      <Name>dbo</Name>
    </SelectedItem>
</ExcludedSourceElements>
<ExcludedTargetElements>
    <SelectedItem Type="Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlPermissionStatement, Microsoft.Data.Tools.Schema.Sql, Version=10.3.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a">
      <Name>Grant.Execute.Schema</Name>
      <Name>NonProductionUser</Name>
      <Name>dbo</Name>
      <Name>dbo</Name>
    </SelectedItem>
</ExcludedTargetElements>

I can take this scmp file and open it in VS2013 with no problem, but the result is the same: The option to exclude permissions granted on the schema is not available. What's really strange is that if I save the scmp file using VS2013, the value listed for "Version" is changed from 10.3.0.0 to 12.0.0.0. It's as if SSDT is claiming that this feature is supported, but it appears that it isn't. Or am I missing something?

nicedream
  • 21
  • 1
  • 3
  • Would it make sense to create a role that assigns those permissions and add your users to that role? You should be able to exclude the roles and role members from comparison. – Peter Schott Oct 13 '15 at 21:25

2 Answers2

1

You can use my deployment contributor to do it:

https://the.agilesql.club/Blogs/Ed-Elliott/HOWTO-Filter-Dacpac-Deployments

Use the filter IgnoreSecurity or you can filter on the specific object.

Ed

Ed Elliott
  • 6,666
  • 17
  • 32
  • Thanks! I did stumble across your project a little while ago, and I am having a look at it. I did have one question: In one of your previous comments on StackOverflow you said "If you are using the publish option in SSDT (inside visual studio) then add them to the publish profile.xml". Could you point me to any documentation or examples on how exactly to reference the dll within the xml file? – nicedream Oct 13 '15 at 18:58
1

In the schema compare results, the differences in these permissions will appear under Database Options > Permissions (and not under the user). You should be able to exclude these changes by unchecking those items under Database Options.

Alternately you can disable comparison of permissions by unchecking Database Options, Permissions and Extended Properties in the Object Types tab of the Schema Compare Options dialog.

Steven Green
  • 3,387
  • 14
  • 17
  • 1
    I'm using VS 2017 and don't see the ability to uncheck Database Options > Permissions. I see "GRANT ALTER" and "GRANT CONTROL" statements in the script output and need to exclude these. – user2966445 May 14 '19 at 16:15