SSDT: Create User in a database not being part of the project

Question

I want the user of my SSDT Database Project to be created as a user in another database on the same server and give it permissions to connect and add it to the db_datareader role.

I failed to do so. The PostDeployment Script complains about:

• USE command is not allowed
• not knowing the Create User OtherDB.USERNAME Syntax

How can this be realized? Isn't there a script option where I can place any command I want?

Answer 1

The action described in the question should not be a problem. I was able to create a Post Deployment script and add the following code:

USE [tempdb];
GO
PRINT DB_NAME();
IF (USER_ID(N'g') IS NULL)
BEGIN
 PRINT 'Creating user [g]...';
 CREATE USER [g] WITHOUT LOGIN;
END;
ELSE
BEGIN
 PRINT 'Dropping user [g]...';
 DROP USER [g];
END;

It builds and publishes with no errors, and executes the commands as expected.

Make sure that your SQL script is truly a Post Deployment script. If it is set to Build Script then it probably will get those errors. Look at the properties of the script and make sure that Build Action is set to PostDeploy.

And please make sure that you are using the latest update of SSDT. The most recent version can be found here:

Download Latest SQL Server Data Tools

• Magier: TY. sp_addrolemember works in post deployment. Question: Why do you create the whole user in de PDS instead of making the object part of the project?
• Solomon Rutzky: @Magier The reason I created the user in the PDS was due to it being in a different database, a database that is not part of my solution. That and I was going based on your statement of "PostDeployment Script complains about not knowing the Create User OtherDB.USERNAME Syntax.". But if you can make it part of the project then that is fine too. I was mainly showing that you could indeed use a USE statement in a PDS.
• McNets: @SolomonRutzky should I use a post deployment script to create a role and grant permissions on views and SP's to this role?
• Solomon Rutzky: @McNets You should be able to include a Role as part of the Database Project. For permissions, that might need to be a post deployment script. I am not sure if there is a way to handle permissions via SSDT, and I don't have time to test right now.