SQL CREATE LOGON - cannot use @parameter as username

Question

I am a developer and I suck on SQL :) Please help me here.

I would like to create my own stored procedure that the Tenant will create in my SaaS database. To do this, I need to create a new SQL Login for the Tenant, and then add it to the predefined SQL role.

I'm already a dead end, just trying to create Login. Here is what I 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, Tenants_InsertTenant procedure, line 16 Invalid syntax next to the name "@username".

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

I understand that this should be simple, but when your new SQL and SQL Manager errors are as mysterious as it seems to me, it is best to ask for help :)

Thanks Justin

Answer 1

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

  EXEC ('CREATE LOGIN' + quotename (@username) + 'WITH PASSWORD =' + quotename (@password, '' ''))

edit: added name for security against SQL injection

Answer 2

Positive decision:

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

Answer 3

Try the following:

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