Участник сервера "sa" не может получить доступ к "модели" базы данных в текущем контексте безопасности

Question

Я создал хранимую процедуру, которая выполняет ряд операций, требующих специальных разрешений, например, создание базы данных, восстановление базы данных и т. Д. Я создаю эту хранимую процедуру с

execute as self

... так что он работает как SA. Это потому, что я хочу дать пользователю SQL без каких-либо разрешений возможность выполнять только те команды, которые я определил.

Но когда я запускаю этот сохраненный процесс, я получаю

The server principal "sa" is not able to access the database "model" under the current security context.

Почему SA не может получить доступ к базе данных модели? Я фактически запустил код в хранимой процедуре самостоятельно, под SA, и он работал нормально.

Answer 1

Прочитайте Расширение олицетворения базы данных с помощью EXECUTE AS, прежде чем продолжить.

при олицетворении принципала с помощью оператора EXECUTE AS USER или в модуле с областью базы данных с помощью предложения EXECUTE AS область олицетворения по умолчанию ограничивается базой данных. Это означает, что ссылки на объекты вне области базы данных будут возвращать ошибку.

Вам необходимо использовать подпись модуля. Вот пример.

Answer 2

Для всех, кто приходит сюда в поисках этой информации, вот пример кода, который демонстрирует именно то, что хотел OP (и я) (на основе превосходной информации от Remus выше):

    -- how to sign a stored procedure so it can access other Databases on same server
    -- adapted from this very helpful article 
    -- http://rusanu.com/2006/03/01/signing-an-activated-procedure/

    USE [master]
    GO
    CREATE DATABASE TempDB1
    CREATE DATABASE OtherDB2
    GO
    USE TempDB1
    GO
    -- create a user for TempDB1
    CREATE USER [foo] WITHOUT LOGIN
    GO

    CREATE PROCEDURE TempDB1_SP 
    AS 
    BEGIN 
        CREATE TABLE OtherDB2.dbo.TestTable (ID int NULL)
        IF @@ERROR=0 PRINT 'Successfully created table.' 
    END
    GO

    GRANT EXECUTE ON dbo.TempDB1_SP TO [foo]
    GO

    EXECUTE AS User='foo'
        PRINT 'Try to run an SP that accesses another database:'
        EXECUTE dbo.TempDB1_SP
        GO
    REVERT

    -- Output: Msg 916, Level 14, State 1, Procedure TempDB1_SP, Line 5
    --   [Batch Start Line 14]
    -- The server principal "..." is not able to access the database 
    --    "OtherDB2" under the current security context.

    PRINT ''
    PRINT 'Fix: Try again with signed SP...'

    -- Create cert with private key to sign the SP with. 
    -- Password not important, will drop private key

    USE TempDB1
    GO
    -- create a self-signed cert
    CREATE CERTIFICATE [DB_Cert] 
        ENCRYPTION BY PASSWORD = 'Password1' 
        WITH SUBJECT = 'Signing for cross-DB SPs';
    -- Sign the procedure with the certificate’s private key
    ADD SIGNATURE TO OBJECT::[TempDB1_SP] 
        BY CERTIFICATE [DB_Cert] 
        WITH PASSWORD = 'Password1'
    -- Drop the private key. This way it cannot be used again to sign other procedures.
    ALTER CERTIFICATE [DB_Cert] REMOVE PRIVATE KEY
    -- Copy the public key part of the cert to [master] database 
    -- backup to a file and create cert from file in [master]
    BACKUP CERTIFICATE [DB_Cert] TO FILE = 'C:\Users\Public\DBCert.cer'

    USE [OtherDB2] -- or use [master] = all DBs on server accessible
    GO
    CREATE CERTIFICATE [DB_Cert] FROM FILE = 'C:\Users\Public\DBCert.cer';
    -- the 'certificate user' carries the permissions that are automatically granted 
    -- when the signed SP accesses other Databases
    CREATE USER [DB_CertUser] FROM CERTIFICATE [DB_Cert]
    GRANT CREATE TABLE TO [DB_CertUser] -- or whatever other permissions are needed
    GO

    USE TempDB1
    EXECUTE dbo.TempDB1_SP
    GO
    -- output: 'Successfully created table.'
    -- clean up: everything except the cert file, have to delete that yourself sorry
    USE [master]
    GO
    DROP DATABASE TempDB1
    DROP DATABASE OtherDB2
    GO