CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
Lamelas84
Lamelas84

Reputation: 1002

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?

Upvotes: 36

Views: 69124

Answers (7)

Emrah Saglam
Emrah Saglam

Reputation: 145

If you're writing dynamic SQL with EXEC sp_executesql ('query1') or EXEC ('query2') this will return correct db which you want. If you're writing static SQL or your query outside of dynamic SQL quotes or parantheses it will work on master (where you create stored procedure(default is master)).

CREATE PROCEDURE master.dbo.mysp1
AS

    EXEC ('USE model; SELECT DB_NAME()') -- or sp_executesql N'USE model; SELECT DB_NAME()' 
    --this returns 'model'

GO


CREATE PROCEDURE master.dbo.mysp2
AS

    EXEC ('USE model;') -- or sp_executesql N'USE model;'
    SELECT DB_NAME() 
    -- this returns 'master'

GO

Upvotes: 0

Stevie Gray
Stevie Gray

Reputation: 113

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.

Upvotes: 0

Lokesh
Lokesh

Reputation: 129

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'

Upvotes: 2

jram
jram

Reputation: 11

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.

Upvotes: 1

XIVSolutions
XIVSolutions

Reputation: 4502

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

Upvotes: 7

gbn
gbn

Reputation: 432667

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

Upvotes: 36

Purplegoldfish
Purplegoldfish

Reputation: 5294

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

Upvotes: -2

Related Questions