In SQL Server, how to add new login, new Users with parameters?

Question

Pretty straight forward stuff here or so I thought.

DECLARE @NewUser varchar(100)

set @NewUser = "[domain\first.last]"

USE [master]
GO

/****** Object:  Login [domain\first.last]    Script Date: 3/15/2022 4:51:57 PM ******/
CREATE LOGIN [domain\first.last] FROM WINDOWS WITH DEFAULT_DATABASE=[adventureWorks], DEFAULT_LANGUAGE=[us_english]
GO


USE [adventureWorks]
GO


/****** Object:  User [domain\first.last]    Script Date: 3/15/2022 4:52:10 PM ******/
CREATE USER [domain\first.last] FOR LOGIN [domain\first.last] WITH DEFAULT_SCHEMA=[dbo]
GO

ALTER ROLE db_datareader ADD MEMBER [domain\first.last]
GO

I'm getting an error:

Msg 207, Level 16, State 1, Line 3
Invalid columnn name '[domain\first.last]'.

What am I doing wrong with the variable declaration here?

Answer 1

In T-SQL, double quotes (") are not string delimiters, they're used as identifiers (though most prefer [square brackets]). For string delimiters you need to use single quotes ('):

SET @NewUser = '[domain\first.last]';

But if you expect to be able to use that later, e.g.

DROP LOGIN @NewUser;

...many of those commands don't accept parameters like that. You'll need to construct them using dynamic SQL:

DECLARE @newUser nvarchar(255) = N'domain\first.last';
DECLARE @sql nvarchar(max) = N'DROP LOGIN $l$;';
SET @sql = REPLACE(@sql, N'$l$', QUOTENAME(@newUser));
PRINT @sql;
-- EXEC sys.sp_executesql @sql;