Managing server logins and database users between developers workstations and other deployment environments using Visual Studio Team System Database Edition
Hello people,
Looking at upgarding from 'Visual Studio Team System 2008 Database Edition' to Visual Studio Team System 2008 Database Edition GDR R2.
Parametrisation in vstsdb is generally weak and we had to apply workarounds to get the DB projects to deploy locally on each developers workstation for them to work on the application database when they deployed it locally conforming to the sandbox principle. Basicly, back at the beginning, when I imported the application database using the vstsdb import 2005 wizard poject type, i simply removed the user scripts it generated, and had to add a logins pre-deployment script that was driven by project variables, the SQLCMD variables can only be used within this special script location :( ...
It dealt with both the logins and the database users, then linked them up.
Looked something like this..
BEGIN TRY
BEGIN TRANSACTION
IF UPPER('$(DeployToDev)') = 'FALSE'
BEGIN
-- SMRBtsService
IF EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = N'$(DomainAppUserAccount)')
DROP LOGIN [$(DomainAppUserAccount)]
CREATE LOGIN [$(DomainAppUserAccount)] FROM WINDOWS
IF EXISTS (SELECT * FROM sys.database_principals WHERE NAME = 'SMRBtsService')
DROP USER [SMRBtsService]
CREATE USER [SMRBTSService] FOR LOGIN [$(DomainAppUserAccount)] WITH DEFAULT_SCHEMA=[dbo]
-- WebUser
IF EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = N'$(DomainWebUserAccount)')
DROP LOGIN [$(DomainWebUserAccount)]
CREATE LOGIN [$(DomainWebUserAccount)] FROM WINDOWS
IF EXISTS (SELECT * FROM sys.database_principals WHERE NAME = 'WebUser')
DROP USER [WebUser]
CREATE USER [WebUser] FOR LOGIN [$(DomainWebUserAccount)] WITH DEFAULT_SCHEMA=[dbo]
END
ELSE IF UPPER('$(DeployToDev)') = 'TRUE'
BEGIN
-- BTSSERVICE
IF EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = N'$(COMPUTERNAME)\BTSSERVICE')
DROP LOGIN [$(COMPUTERNAME)\BTSSERVICE]
CREATE LOGIN [$(COMPUTERNAME)\BTSSERVICE] FROM WINDOWS
IF EXISTS (SELECT * FROM sys.database_principals WHERE NAME = 'SMRBtsService')
DROP USER [SMRBtsService]
CREATE USER [SMRBTSService] FOR LOGIN [$(COMPUTERNAME)\BTSSERVICE] WITH DEFAULT_SCHEMA=[dbo]
-- WebUser
IF EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = N'$(COMPUTERNAME)\WebUser')
DROP LOGIN [$(COMPUTERNAME)\WebUser]
CREATE LOGIN [$(COMPUTERNAME)\WebUser] FROM WINDOWS
IF EXISTS (SELECT * FROM sys.database_principals WHERE NAME = 'WebUser')
DROP USER [WebUser]
CREATE USER [WebUser] FOR LOGIN [$(COMPUTERNAME)\WebUser] WITH DEFAULT_SCHEMA=[dbo]
END
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF (XACT_STATE()) <> 0
ROLLBACK TRANSACTION
DECLARE @ErrorMessage NVARCHAR(4000),
@ErrorNumber INT,
@ErrorSeverity INT,
@ErrorState INT,
@ErrorLine INT,
@ErrorProcedure NVARCHAR(200) ;
SELECT @ErrorNumber = ERROR_NUMBER(), @ErrorSeverity = 20,
@ErrorState = ERROR_STATE(), @ErrorLine = ERROR_LINE(),
@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-') ;
SELECT @ErrorMessage = N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' +
'Message: ' + ERROR_MESSAGE() ;
RAISERROR (@ErrorMessage, @ErrorSeverity, 1, @ErrorNumber, -- parameter: original error number.
@ErrorSeverity, -- parameter: 20, we want to stop the entire deploy process.
@ErrorState, -- parameter: original error state.
@ErrorProcedure, -- parameter: original error procedure name.
@ErrorLine-- parameter: original error line number.
) ;
END CATCH
Question;
How would one achieve this sort of functionality using the GDR edition, I see that it supports the concept of linking projects in a hierarchy of references. And you have two project types, a Database and a Server project. But yet you still need to create a script like this to achieve the same dynamic ability for the project to deploy on any machine in dev mode?
Creating a server project type just adds server objects in addition to all the objects (scripts) of the Database project type.
Documentation talks of composite project structures... But still i don't see an obvious solution.