Is there a way for me to dynamically write a script to add a domain+user to Microsoft SQL server in a stored procedure?

Question

I have to use this exact script below:

ALTER PROCEDURE [dbo].[sp_CheckIfSQLLoginExistsAndCreateLogin] 
    @SearchDomain NVARCHAR(MAX),
    @SearchUsername NVARCHAR(MAX)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
    
    DECLARE @sql NVARCHAR(max);
    DECLARE @params NVARCHAR(MAX);

    IF @SearchUsername != ''
    BEGIN
        SET @sql = N'IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE [name] =  @Domain\@Username) CREATE LOGIN [@Domain\@Username] FROM WINDOWS';
        SET @params = N'@Username NVARCHAR(MAX), @Domain NVARCHAR(MAX)';
    END
            
    exec sp_executesql @sql, @params, @Username=@SearchUsername, @Domain=@SearchDomain      
END

The problem I am having is that every time this SP is called I get the following error:

Using this data: @SearchDomain = OFFICE and @SearchUsername = BJackson

Answer 1

The problem here is you think SQL is a scripting language; it is not. For example '@Domain' means the literal string '@Domain' not replace the literal string '@Domain' with the value in the variable @Domain.

If you have EXEC sys.sp_executesql N'SELECT '@V1 + @V2';', N'@V1 varchar(30), @V2 varchar(30)','This', 'works'; you don't get the value 'Thisworks' you get the value '@V1 + @V2' Why? Because they are literals.

What you need to do is safely inject your parameters for the object names, and properly parametrise your WHERE:

ALTER PROCEDURE [dbo].[CheckIfSQLLoginExistsAndCreateLogin] --removed prefix
    @SearchDomain sysname, --Corrected datatype
    @SearchUsername sysname  --Corrected datatype
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    
    DECLARE @sql NVARCHAR(max);
    DECLARE @Login sysname = CONCAT(@SearchDomain,'\',@SearchUsername);

    IF @Login != ''
    BEGIN
        SET @sql = N'IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE [name] = @Login) CREATE LOGIN ' + QUOTENAME(Login) + N' FROM WINDOWS';
    END
            
    EXEC sys.sp_executesql @sql, N'@Login sysname', @Login;
END;