如何授予用户从 SQL Server 中的存储过程内部访问数据库的权限 - 获取错误:过程中不允许使用数据库语句 [英] How to grant user access to a DB from inside a stored procedure in SQL Server- Getting Error: USE database statement is not allowed in a procedure

问题描述

我需要授予用户从 SQL Server 中的存储过程内部访问数据库的权限，但是当我尝试使用此代码时，正在使用的数据库不会更改，并且当我在不使用 EXEC 的情况下运行代码时，我收到一个错误:

I need to grant the user access to a database from inside a stored procedure in SQL Server, but when I try to use this code, the DB in use does not change and when I run the code without using EXEC, I get an error:

过程中不允许使用数据库语句

USE database statement is not allowed in a procedure

我的代码:

CREATE PROCEDURE [dbo].[usp_AddUserDBToTables] 
    (@UserGroup NCHAR(30),    -- DBdetail, DBxref
     @DBName NCHAR(30))       -- DBdetail, DBxref
AS
BEGIN
    DECLARE @sqlcmd NVARCHAR(1024);

    -- Add the UserGroup to the new UserDB
    SET @sqlcmd = 'USE [' + rtrim(ltrim(@DBName)) + ']'

    PRINT (@sqlcmd)

    EXEC (@sqlcmd)

    PRINT 'now using ' + db_name()

    SET @sqlcmd = 'CREATE USER [' + rtrim(ltrim(@UserGroup)) + '] FOR LOGIN [' + rtrim(ltrim(@UserGroup)) + ']'

    PRINT (@sqlcmd)

    EXEC (@sqlcmd)

    SET @sqlcmd = 'ALTER ROLE [db_owner] ADD MEMBER [' + rtrim(ltrim(@UserGroup)) + ']'

    PRINT (@sqlcmd)

    EXEC (@sqlcmd)

    SET @sqlcmd = 'USE [HOSTED_SUPPORT]'

    PRINT (@sqlcmd)

    EXEC (@sqlcmd)
END

用户组添加到 HOSTED_SUPPORT，而不是@DBName.打印语句生成了这个，但我在使用动态 SQL 时没有收到错误:

The user group is added to HOSTED_SUPPORT, not @DBName. Print statements generated this, but I received no errors when using dynamic SQL:

USE [ZU_1077a]
now usingZMIT_HOSTED_SUPPORT
CREATE USER [ZMCLIENT\1007 FirstBank] FOR LOGIN [ZMCLIENT\1007 FirstBank]
ALTER ROLE [db_owner] ADD MEMBER [ZMCLIENT\1007 FirstBank]
USE [ZMIT_HOSTED_SUPPORT]

如何在存储过程中完成这项工作?

How can I make this work from inside a stored procedure?

推荐答案

您需要将所有命令放在一个动态 SQL 中并运行它，因为当第一个包含 USE 命令的动态 SQL 被执行时，USE DATABASE 将退出.

You need to put all the command in one dynamic SQL and run it because the USE DATABASE will be out of when the first dynamic SQL containing the USE command is executed.

因此，删除除最后一个之外的所有 EXEC，并连接@sqlcmd 中的所有 SQL.运行 EXEC @sqlcmd.

So, remove all the EXEC except the last one, and concat all the SQL in @sqlcmd. Run EXEC the @sqlcmd.

这篇关于如何授予用户从 SQL Server 中的存储过程内部访问数据库的权限 - 获取错误:过程中不允许使用数据库语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！