Use database inside a stored procedure

问题:

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.

How can I do this?

回答1:

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

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:

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:

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:

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:

Using sp_executesql seems to work, for more info see 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:

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

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.