sql server - Use database inside a stored procedure

Question

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?

Answer 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