Хранимая процедура TSQL - создание пользователя в базе данных из другой или основной базы данных

Question

Мне нужно создать хранимую процедуру (1 или более), которую я могу вызвать из главной базы данных (или другой базы данных) в запланированном задании, чтобы восстановить разрешения (user и privs) для базы данных, которая была восстановлена ​​из исходной базы данных. Я не хочу создавать пользователей на уровне исходного db, потому что я хочу ограничить доступ к этому серверу, поэтому sproc необходимо будет выполнить после завершения восстановления, чтобы предоставить доступ. В настоящее время у меня есть:

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

Answer 1

Одним из ограничений хранимых процедур является то, что они не могут содержать use database; заявления. Чтобы выполнить то, что вы ищете, вы можете запустить sp_executesql из master и передать строку tsql, которая выполняет sp_executesql в контексте конкретной базы данных. Что-то вроде этого:

--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;