TSQL Stored Procedure - Create User in database from another or master database

Question

I need to create a stored procedure (1 or more) that I can call from the master db (or other db) in a scheduled job to restore permissions (user and privs) to a database that has been restored from a source db. I do not want to create the users at the source db level because I want to restrict access to that server, so the sproc will need to be executed once the restore is completed to grant access. I currently have:

use (database);

if ( select objectproperty(object_id('dbo.spMaint_RestorePermissions_database'), 'IsProcedure') ) is null
exec ('create procedure dbo.spMaint_RestorePermissions_database as select 1');
go

alter procedure dbo.spMaint_RestorePermissions_database
as
set nocount on;     
    ----    grant user permission to access database
    create user [domain\userid] for login [domain\userid]
    -----   grant user access rights to database
    exec sp_addrolemember 'db_datareader', 'domain\userid'
go

score 2 · Accepted Answer · answered Mar 20 '14 at 17:09

One of the restrictions of stored procs is that they cannot contain use database; statements. To accomplish what you're looking for, you could run sp_executesql from master and pass in a tsql string that executes sp_executesql in the context of a specific database. Something like this:

--This is the tsql statement that gets executed on a specific user db.
--You would use this to build your "create proc", "alter proc", "create user", etc statements.
DECLARE @InnerSql NVARCHAR(MAX) = 'SELECT DB_NAME()'

DECLARE @DB SYSNAME = 'DatabaseName'
DECLARE @Tsql NVARCHAR(MAX) = '[' + @DB + ']..sp_executesql N''' + @InnerSql + ''''

EXEC sp_executesql @Tsql;

TSQL Stored Procedure - Create User in database from another or master database

1 Answers1