Appendix A – Scripts

Version: 10.5

User credential script for database deployment

The following script is an example of how to specify user credentials when you deploy a database.

You must update the script to match the database.


:SETVAR DatabasePrefix xp0
:SETVAR UserName messaginguser
:SETVAR Password Test12345
:SETVAR MessagingSuffix _Messaging
GO

IF(SUSER_ID('$(UserName)') IS NULL)
BEGIN 
       CREATE LOGIN [$(UserName)] WITH PASSWORD = '$(Password)'; END;
GO

USE [$(DatabasePrefix)$(MessagingSuffix)]  
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'$(UserName)')
BEGIN
    CREATE USER [$(UserName)] FOR LOGIN [$(UserName)] END;
GO

EXEC sp_addrolemember 'db_datareader', '$(UserName)'
EXEC sp_addrolemember 'db_datawriter', '$(UserName)'
GO

If you have suggestions for improving this article, let us know!