Databases
StackOverflow2013
Postsdbo
PODatabase Project Dropping Permissions On Deploy
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PODatabase Project Dropping Permissions On Deploy
    text
    copied!<p>I have a SQL 2005 Database Project in VS2010. I have a user who is being created and given permissions to the database.</p> <p><strong>Test.user.sql</strong></p> <pre><code>CREATE USER [$(MachineName)\Test] FOR LOGIN [$(MachineName)\Test] WITH DEFAULT_SCHEMA = dbo; GO EXEC sp_addrolemember N'db_datawriter', N'$(MachineName)\Test' GO EXEC sp_addrolemember N'db_datareader', N'$(MachineName)\Test' GO </code></pre> <p>I have a SQLCMDVAR defined for <code>$(MachineName)</code> which holds the name of the machine depending on the selected configuration (Debug (Dev), QA, UAT, Production). This variable is being set correctly at the top of the generated SQL:</p> <pre><code>:setvar MachineName "TheMachineName" </code></pre> <p>When I deploy this project, the user that is created in the script above is dropped and then recreated and the permissions for the user are revoked. During deployment, what is generated regarding this user and its permissions depends on the state of the user in the database.</p> <p>If the user exists with the <code>datareader</code> and <code>datawriter</code> permissions, those permissions are revoked, the user is dropped, then the user is recreated and the permissions are not. Visual studio adds this line where the <code>sp_addrolemember</code> commands should be:</p> <pre><code>PRINT N'Altering &lt;unnamed&gt;...'; GO PRINT N'No script could be generated for operation 2 on object ''&lt;unnamed&gt;''.'; GO PRINT N'Altering &lt;unnamed&gt;...'; GO PRINT N'No script could be generated for operation 2 on object ''&lt;unnamed&gt;''.'; GO </code></pre> <p>If I deploy the project again, the permissions are correctly generated and applied to the user:</p> <pre><code>PRINT N'Creating &lt;unnamed&gt;...'; GO EXECUTE sp_addrolemember @rolename = N'db_datareader', @membername = N'$(MachineName)\Test'; GO PRINT N'Creating &lt;unnamed&gt;...'; GO EXECUTE sp_addrolemember @rolename = N'db_datawriter', @membername = N'$(MachineName)\Test'; GO </code></pre> <p>I can't figure out why this is happening, any suggestions?</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload