Justin
Justin

Reputation: 10907

CREATE LOGIN - can't use @parameter as username

I'd like to create my own Stored Procedure that creates a Tenant in my SaaS database. In order to do this I need to create a new SQL Login for the Tenant and then add it to a predefined SQL Role.

I'm already stumped just trying to create the Login. Here is what I've tried:

CREATE PROCEDURE [MyScheme].[Tenants_InsertTenant]
    @username nvarchar(2048),
    @password nvarchar(2048)

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    CREATE LOGIN @username WITH PASSWORD = @password
END

Msg 102, Level 15, State 1, Procedure Tenants_InsertTenant, Line 16 Incorrect syntax near '@username'.

Msg 319, Level 15, State 1, Procedure Tenants_InsertTenant, Line 16 Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

I realize this should be straightforward but when one is new to SQL and the SQL manager errors are as cryptic as they seem to be to me, it's better just to ask for help :)

Upvotes: 8

Views: 11957

Answers (4)

codeulike
codeulike

Reputation: 23064

Apparently CREATE LOGIN only accepts literals. You could try wrapping it in an exec and building it as a string:

Add quotename for safety from sql injection attacks

DECLARE @sql nvarchar(max) = 'CREATE LOGIN ' + quotename(@username) + ' WITH PASSWORD = ' + quotename(@password, '''');
EXEC(@sql)

Upvotes: 14

bounav
bounav

Reputation: 5046

Building on the answers from @codeulike and @Galkin I ended up doing this:

DECLARE @t nvarchar(4000)
SET @t = N'CREATE LOGIN ' + QUOTENAME(@username) + ' WITH PASSWORD = ' + QUOTENAME(@password, '''') + ', default_database = ' + QUOTENAME(@DatabaseName)
EXEC(@t)

I'm running SQL Server 2019 and combining EXEC(--> with QUOTENAME() inside <--) on the same line does not work.

If I understand the microsoft sql injection documentation using quotename to build a string then executing protects you from SQL injections.

Upvotes: 1

Galkin
Galkin

Reputation: 843

Posible solution:

sp_addlogin @loginame = 'test', @passwd = 'test', @defdb = 'test'

Upvotes: 6

eKek0
eKek0

Reputation: 23309

Try this:

declare @t nvarchar(4000)
set @t = N'CREATE LOGIN ''''' + @username + ''''' WITH PASSWORD = ''''' + @password
exec sys.sp_executesql @t

Upvotes: 1

Related Questions