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

问题描述

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

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

我已经很难尝试创建登录.这是我尝试过的...

创建程序 [MyScheme].[Tenants_InsertTenant]@用户名 nvarchar(2048),@password nvarchar(2048)作为开始-- 添加了 SET NOCOUNT ON 以防止额外的结果集-- 干扰 SELECT 语句.设置无计数；CREATE LOGIN @username WITH PASSWORD = @password结尾

<块引用>

消息 102，级别 15，状态 1，过程 Tenants_InsertTenant，第 16 行'@username' 附近的语法不正确.

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

我意识到这应该很简单，但是当您不熟悉 SQL 并且 SQL 管理器错误对我来说似乎如此神秘时，最好只是寻求帮助:)

谢谢，贾斯汀

解决方案

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

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

添加了引用名称以防止 sql 注入攻击

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

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 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:)

Thanks, Justin

解决方案

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, ''''))

edit: added quotename for safety from sql injection attacks

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