Is it possible to convert MyUser, MyPassword and MyDatabase within the following SQL Server script into parameters?
IF NOT EXISTS
(SELECT name
FROM master.sys.server_principals
WHERE name = 'MyUser')
BEGIN
CREATE LOGIN MyUser WITH PASSWORD = 'MyPassword'
END
CREATE DATABASE MyDatabase;
GO
USE MyDatabase;
CREATE USER MyUser FOR LOGIN MyUser;
GO
EXEC sp_addrolemember 'db_owner', 'MyUser'
As you may have found out, you have to use literals for tasks like this. As a result you have to use dynamic SQL and safely inject the values For example, for the login (assuming the parameter values have been set in an SP):
DECLARE @SQL nvarchar(MAX);
IF NOT EXISTS (SELECT 1
FROM sys.syslogins
WHERE name = @MyLogin)
SET @SQL = N'CREATE LOGIN ' + QUOTENAME(@MyLogin) + N' WITH PASSWORD = N' + QUOTENAME(@MyPassword,'''') + N';';
EXEC sp_executesql @SQL;
If the login already exists, then @SQL will have a value of NULL and won't be created.
As a result, your final SP will look something like this:
CREATE PROC dbo.SetupLogin @MyLogin sysname, @MyDatabase sysname, @MyPassword nvarchar(128) AS
BEGIN
DECLARE @SQL nvarchar(MAX);
IF NOT EXISTS (SELECT 1
FROM sys.syslogins
WHERE name = @MyLogin)
SET @SQL = N'CREATE LOGIN ' + QUOTENAME(@MyLogin) + N' WITH PASSWORD = N' + QUOTENAME(@MyPassword,'''') + N';';
EXEC sp_executesql @SQL;
SET @SQL = N'CREATE DATABASE ' + QUOTENAME (@MyDatabase) + N';'
EXEC sp_executesql @SQL;
SET @SQL = N'USE ' + QUOTENAME (@MyDatabase) + N';' + NCHAR(13) + NCHAR(10) +
N'CREATE USER ' + QUOTENAME(@MyLogin) + N' FOR LOGIN ' + QUOTENAME(@MyLogin) + N';' + NCHAR(13) + NCHAR(10) +
N'ALTER ROLE db_owner ADD MEMBER ' + QUOTENAME(@MyLogin) + N';'; --sp_addrolemember is deprecated, stop using it.
EXEC sp_executesql @SQL;
END;
GOis a script command, not a T-SQL keyword. This script can only run inSSMSorsqlcmd. If you want to run this eg during deployment, use Scripting variables and pass their values as parameters to thesqlcmdcommand that executes the script filesp_addrolememberhas been deprecated since SQL Server 2012 (or prior) iirc.