Search code examples
sql-serversql-server-data-toolsdatabase-project

SSDT - Do not alter user permissions, but alter table permissions?

When publishing a database project, I would like to grant/revoke permissions to an object like a view or a table only, objects that are in-build definition. I do not want SSDT to take into consideration user permissions.

For example when removing a permission in a table file, I would want it to alter permission on objects in build only, but it also alters permissions on users too.

I would like: 

REVOKE SELECT
ON OBJECT::[dbo].[myTable] TO [myRole] CASCADE
AS [dbo];

And I do not want:

REVOKE CONNECT TO [Domain\MyUser] CASCADE;

When I tick [x] Do not drop permissions, it takes away both, and when I include it in, it includes both types.

I have also ticked [x] Exclude users, but with no success.

Is there a way to do it or a possible workaround?

Thank you.

Solution

  • Consider using a post-deploy script to get the exact behavior you describe.