I need to make a stored procedure which creates a user in more than one database. Something like this:
我需要创建一个存储过程,在多个数据库中创建一个用户。像这样的东西:
USE [database1]
CREATE USER [userLogin] FOR LOGIN [userLogin]
USE [database2]
CREATE USER [userLogin] FOR LOGIN [userLogin]
Since the CREATE USER
statement does his job in the current database I need to use the USE
statement to change between databases, but it can't be used inside stored procedures.
由于CREATE USER语句在当前数据库中完成其工作,因此我需要使用USE语句在数据库之间进行更改,但不能在存储过程中使用它。
How can I do this?
我怎样才能做到这一点?
6 个解决方案
#1
21
Dynamic SQL
动态SQL
CREATE PROCEDURE spTestProc
AS
EXEC ('USE [database1]; CREATE USER [userLogin] FOR LOGIN [userLogin]')
EXEC ('USE [database2]; CREATE USER [userLogin] FOR LOGIN [userLogin]')
GO
#2
5
SQL Server gives us a system stored procedure to do this. My understanding is that the recommended method would be to use sys.sp_grantdbaccess:
SQL Server为我们提供了一个系统存储过程来完成此任务。我的理解是推荐的方法是使用sys.sp_grantdbaccess:
CREATE PROCEDURE usp_CreateTwoUSers
AS
BEGIN
-- Create a user for a login in the current DB:
Exec sp_grantdbaccess [userLogin], [name_in_db];
-- Create a user for a login in an external DB:
Exec ExternalDatabaseName.sys.sp_grantdbaccess [userLogin], [name_in_db];
END
#3
1
CREATE PROCEDURE spTestProc
AS
BEGIN
EXECUTE sp_executesql N'USE DB1 SELECT * FROM TABLE1'
EXECUTE sp_executesql N'USE DB2 SELECT * FROM Table2'
END
exec spTestProc
now it is worked.
现在它已经奏效了。
#4
1
I did it like below:
我这样做如下:
Alter Procedure testProc
@dbName varchar(50)
As
declare @var varchar(100)
set @var = 'Exec(''create table tableName(name varchar(50))'')'
Exec('Use '+ @dbName + ';' + @var)
Exec testProc 'test_db'
#5
0
Using sp_executesql
seems to work, for more info see http://msdn.microsoft.com/en-us/library/ms175170.aspx
使用sp_executesql似乎有效,有关更多信息,请参阅http://msdn.microsoft.com/en-us/library/ms175170.aspx
I tested it using this and it worked fine:
我用它测试了它并且工作正常:
CREATE PROCEDURE spTestProc
AS
BEGIN
EXECUTE sp_executesql N'USE DB1;'
SELECT * FROM TABLE1
EXECUTE sp_executesql N'USE DB2;'
SELECT * FROM Table2
END
exec spTestProc
#6
0
It should be noted that if you want to use single quotes within a EXEC command, you will need to double the amount of single quotes
应该注意的是,如果要在EXEC命令中使用单引号,则需要将单引号的数量加倍
e.g.
例如
EXEC ('USE [database1]; select * from Authors where name = ''John'' ')
In this example, John has 2 single quotes before and after it. You cannot use double quotes for this type of query.
在这个例子中,John在它之前和之后有2个单引号。您不能对此类查询使用双引号。
#1
21
Dynamic SQL
动态SQL
CREATE PROCEDURE spTestProc
AS
EXEC ('USE [database1]; CREATE USER [userLogin] FOR LOGIN [userLogin]')
EXEC ('USE [database2]; CREATE USER [userLogin] FOR LOGIN [userLogin]')
GO
#2
5
SQL Server gives us a system stored procedure to do this. My understanding is that the recommended method would be to use sys.sp_grantdbaccess:
SQL Server为我们提供了一个系统存储过程来完成此任务。我的理解是推荐的方法是使用sys.sp_grantdbaccess:
CREATE PROCEDURE usp_CreateTwoUSers
AS
BEGIN
-- Create a user for a login in the current DB:
Exec sp_grantdbaccess [userLogin], [name_in_db];
-- Create a user for a login in an external DB:
Exec ExternalDatabaseName.sys.sp_grantdbaccess [userLogin], [name_in_db];
END
#3
1
CREATE PROCEDURE spTestProc
AS
BEGIN
EXECUTE sp_executesql N'USE DB1 SELECT * FROM TABLE1'
EXECUTE sp_executesql N'USE DB2 SELECT * FROM Table2'
END
exec spTestProc
now it is worked.
现在它已经奏效了。
#4
1
I did it like below:
我这样做如下:
Alter Procedure testProc
@dbName varchar(50)
As
declare @var varchar(100)
set @var = 'Exec(''create table tableName(name varchar(50))'')'
Exec('Use '+ @dbName + ';' + @var)
Exec testProc 'test_db'
#5
0
Using sp_executesql
seems to work, for more info see http://msdn.microsoft.com/en-us/library/ms175170.aspx
使用sp_executesql似乎有效,有关更多信息,请参阅http://msdn.microsoft.com/en-us/library/ms175170.aspx
I tested it using this and it worked fine:
我用它测试了它并且工作正常:
CREATE PROCEDURE spTestProc
AS
BEGIN
EXECUTE sp_executesql N'USE DB1;'
SELECT * FROM TABLE1
EXECUTE sp_executesql N'USE DB2;'
SELECT * FROM Table2
END
exec spTestProc
#6
0
It should be noted that if you want to use single quotes within a EXEC command, you will need to double the amount of single quotes
应该注意的是,如果要在EXEC命令中使用单引号,则需要将单引号的数量加倍
e.g.
例如
EXEC ('USE [database1]; select * from Authors where name = ''John'' ')
In this example, John has 2 single quotes before and after it. You cannot use double quotes for this type of query.
在这个例子中,John在它之前和之后有2个单引号。您不能对此类查询使用双引号。