-2

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?

HPWD
  • 2,180
  • 4
  • 30
  • 60
  • Never in a million years if I was searching for help with SQL login script would I have clicked on a result with the name of "What is the difference between single and double quotes in SQL". The vote down makes no sense to me. – HPWD Mar 18 '22 at 20:03

1 Answers1

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;
Aaron Bertrand
  • 261,961
  • 36
  • 448
  • 471