Search code examples
sql-serversql-server-2012msbuildmsbuild-task

Is where a way to create an SQL Server login with MSBUILD?

I have to create a new SQL Server user for our application. To spare me the effort of doing it on each of our installations, I want our MSBUILD script to handle it. I have the login user and password as variables in the build script. The user shall only be data-reader.

Is there a way to do this easily?

Solution

  • You could make use of the SQL Server Command Line Utilities (there are different versions of this package, make sure you pick the one that matches your OS and is compatible with your version of SQL Server).

    The sqlcmd utility lets you enter Transact-SQL statements, system procedures, and script files at the command prompt.

    You could now create a SQL script (e.g. createuser.sql):

    USE [$(DatabaseName)]
GO

-- If the user exists, drop it first
IF EXISTS (select principal_id FROM sys.database_principals where [name] = '$(DatabaseUser)' AND type = 'U')
BEGIN
    DROP USER [$(DatabaseUser)]
END

-- Create it
CREATE USER [$(DatabaseUser)] FOR LOGIN [$(SqlLogin)]

-- And add it to the Role db_datareader
ALTER ROLE db_datareader ADD MEMBER [$(DatabaseUser)]

    Using sqlcmd you can now call this script from the command line like so:

    sqlcmd -v DatabaseName="MyDatabase" DatabaseUser="Pete" SqlLogin="John" -i "createuser.sql" -S "mymachine\mySqlInstance"

    You could now create an MSBuild project file with an Exec task that will call that statement for you:

    <Exec Command="sqlcmd -v ..." ... >
</Exec>

    Please note that the user that is executing the sqlcmd should have permissions in Sql Server to perform the scripted actions. SqlCmd lets you specify a specific username and password.