SQL CREATE LOGON-不能使用@parameter作为用户名 [英] SQL CREATE LOGON - can't use @parameter as username

问题描述

我是一名开发人员，对SQL很烂:)请在这里帮助我.

I'm a developer and I suck at SQL:) Please help me out here.

我想创建自己的存储过程，以便在SaaS数据库中创建一个租户.为此，我需要为租户创建一个新的SQL登录名，然后将其添加到预定义的SQL角色中.

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

第102层，状态1，过程1租户_InsertTenant，第16行 "@用户名"附近的语法不正确.

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

第159条消息，状态15，状态1，过程租户_InsertTenant，第16行 关键字"with"附近的语法不正确.如果此语句是公用表表达式，xmlnamespaces子句或更改跟踪上下文子句，则前一条语句必须以分号终止.

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.

我意识到这应该很简单，但是当您对SQL的新知识和SQL管理器错误像我看来一样神秘时，最好还是寻求帮助:)

I realize this should be straightforward but when your new to SQL and the SQL manager errors are as cryptic as they seem to be to me its better just to ask for help:)

谢谢， 贾斯汀

推荐答案

显然，CREATE LOGIN仅接受文字. 您可以尝试将其包装在exec中并将其构建为字符串:

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

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

为安全起见添加了引号，以防止sql注入攻击

edit: added quotename for safety from sql injection attacks

这篇关于SQL CREATE LOGON-不能使用@parameter作为用户名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！