Can I ignore logins when publishing a Visual Studi

Question

We have a SQL Server that uses SQL Server Authentication, with users that can deploy, and others that can read, for the sake of simplicity, I'll call them "deploy" and "web".

I'm having difficulty setting permissions up using a Visual Studio (2012) Database project, as the "deploy" user does not have sufficient permissions to create new server logins.

I can add scripts to do things like:

GRANT SELECT ON foo.bar TO [web]

This then sulks (with "SQL71501: Permission has an unresolved reference to object [web].") until I add:

CREATE USER [web] FOR LOGIN [web];

This then sulks (with "SQL71501: User: [web] has an unresolved reference to Login [web].") until I add:

CREATE LOGIN [web] WITH PASSWORD = '******';

This then fails to publish with:

Dropping Permission...
Dropping Permission...
Creating [webuser]...
(67,1): SQL72014: .Net SqlClient Data Provider: Msg 15247, Level 16, State 1, Line 1 User does not have permission to perform this action.
(67,0): SQL72045: Script execution error. The executed script:
CREATE LOGIN [webuser]
WITH PASSWORD = '**';

An error occurred while the batch was being executed.

This doesn't make sense, as the user already exists, so shouldn't need creating

How can I allow publishing via the deployment user without trying to (re)create the login each time? Or, is it possible to reference the externally created user, without having to publish it?

Answer 1

As the user already exists, I suspect that the user that is used to perform the deploy doesn't have rights to view it. The below should be all you need to resolve this.

GRANT VIEW DEFINITION ON LOGIN::web TO deploy