Ignore users  
Author Message
Rafet





PostPosted: Visual Studio Team System - Database Professionals, Ignore users Top

I have created a project from the Production DB, in each DB, QA, staging and development different set of users may exist.

How do I set the VS to ignore users when building and deploying.

How do I set VS to drop and re-create all SP Views and Function on change.



Visual Studio Team System44  
 
 
Jon Liperi





PostPosted: Visual Studio Team System - Database Professionals, Ignore users Top

When you build your database project, it compares the existing schema of the deployed database with the schema in the project and generates the necessary deployment script to make the live db match the project. [By default, the script is created in a "sql" subfolder of the project.] Therefore, when you make a change to a sproc, view, or function in your project, VS will actually generate an ALTER statement in the deployment script to bring the objects up to speed (i.e., rather than a CREATE statement). Therefore, that scenario should already be handled for you. Please let me know if it is not updating the objects.

As for ignoring users, this is an interesting scenario. I assume you do not want the users tracked in the database project because you do not want to deploy them (as you mentioned, different users exist in each environment). Is this the case If so, you can create the users in each environment and make sure to uncheck the box next to build property "Generate DROP statements that are in the target database but are not in the database project". This will keep VS from dropping the users it does not find in the project on deployment. Of course, you can always customize the generated SQL script and pre/post deployment scripts as well.

Thanks for your questions!

-Jon



 
 
Rafet





PostPosted: Visual Studio Team System - Database Professionals, Ignore users Top

We have used the if exists drop to refresh the Date Created in SQL Server 2000, as you know the alter will not change that. But we an live with the ALTER.

As for users my setting for Generate DROP statements that are in the target database but are not in the database project is OFF. And the user doensn't exist in the Production DB but exists in the QA. When running the build the server DROPS this user from the QA DB:

PRINT N'Altering members of role BizTalkUser'

GO

sp_droprolemember N'BizTalkUser', N'SQLdummy'

GO

sp_droprolemember N'BizTalkUser', N'BizTalkSVC'

and then when it creates it back it only leaves on user that exists in production.

sp_addrolemember N'BizTalkUser', N'SQLdummy'

I tried to add this user to the users script as

if like 'dev%' or like 'QA%' or like 'STG%'

EXEC sp_grantdbaccess 'domain\BizTalkSVC', 'BizTalkSVC'

however I don't see this ever included in the change script. Also why does the script create droprolemember and addrolemember for each user even thouhg nothing chaged

This is very confusing.


 
 
Rafet





PostPosted: Visual Studio Team System - Database Professionals, Ignore users Top

So the resulution is that the if you have a user in you let's say QA enviroment but not in production you need to grant EXPLICIT permssion to this use in 2 places:

If the user is not part of the role, you have to grant explict permissions to this user in the post deloment > permissions

If the user is part of the a role, you have to explictly add this user back to the role by moifying the scipt and testing if login exist, and if so then add the user to the role in the Script.PostDeployment.sql :

if exists (SELECT name FROM master.dbo.sysxlogins WHERE Name LIKE N'%BizTalkSVC')

exec sp_addRoleMember N'BizTalkUser', 'BizTalkSVC'

Rafet