Menu
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?
786
The project uses multiple database so a stored procedure may call tables in another database.
You can use Dynamic SQL to change the Database Name and run query against that database as shown in below example. You can create this stored procedure in any of the Database you want. Once you run the Stored Procedure, It is going to get the user databases list and then use Cursor to loop through.
Summary. A single stored procedure can be used to select, add, update, and delete data from a database table.
According to Microsoft standard, stored procedures cannot be executed inside the function, but technically it is possible with some tweaks.
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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
