Rozwiązywanie problemów z uprawnieniami podczas przenoszenia bazy danych MSDB między różnymi wystąpieniami

Ten artykuł ułatwia rozwiązywanie problemów z uprawnieniami występujących podczas przenoszenia bazy danych MSDB między różnymi wystąpieniami.

Oryginalna wersja produktu: Microsoft SQL Server
Oryginalny numer KB: 2000274

Symptomy

Rozważmy następujący scenariusz:

Bazę danych msdb można przenieść z jednego wystąpienia do innego przy użyciu procesu tworzenia kopii zapasowej i przywracania lub kopiując pliki bazy danych (mdf i ldf). Następnie na serwerze docelowym użytkownik, który nie jest częścią stałej roli Sysadmin w programie SQL Server, próbuje wykonać jedną z następujących operacji:

• Wyświetlanie zadania w programie SQL Server Management Studio.
• Wywołaj procedury składowane związane z agentem SQL (na przykład xp_sqlagent_enum_jobs lub sp_get_composite_job_info) bezpośrednio przy użyciu języka T-SQL.

W tym scenariuszu użytkownik otrzyma komunikat o błędzie podobny do następującego:

Wystąpił wyjątek podczas wykonywania instrukcji języka Transact-SQL lub partii. (Microsoft.SqlServer.ConnectionInfo)
Odmowa uprawnień EXECUTE obiektu "xp_sqlagent_enum_jobs", bazy danych "mssqlsystemresource", schematu "sys". (Microsoft SQL Server, błąd: 229)

Przyczyna

Ten problem występuje, ponieważ certyfikat agenta SQL różni się w różnych wystąpieniach.

Rozwiązanie

Aby rozwiązać problem, należy zastąpić certyfikat w głównym certyfikatem z przywróconej bazy danych msdb przy użyciu następującego skryptu na serwerze docelowym:

use msdb
go
-- Backup the Agent certificate from the remote server to a file
BACKUP CERTIFICATE [##MS_AgentSigningCertificate##] TO FILE = 'MS_AgentSigningCertificate.remote_server.cer'
go
use master
go
-- re-create the agent certificate on master
-- Note: Because we are making these changes using a regular user and not as part of setup, the name
-- cannot include the ## token.
-- Creating a regular certificate in this case should be the equivalent as we only need it to derive a SID
 CREATE CERTIFICATE [MS_AgentSigningCertificate.remote_server] FROM FILE = 'MS_AgentSigningCertificate.remote_server.cer'
go
-- Recreate the user mapped to the cert and grant the same permissions that the regular certificate needs.
CREATE USER [MS_AgentSigningCertificate.remote_server] FROM CERTIFICATE [MS_AgentSigningCertificate.remote_server]
go
GRANT EXECUTE TO [MS_AgentSigningCertificate.remote_server]
go

Uwaga 16.

W scenariuszu, w którym certyfikaty w modelu głównym i modelu są takie same przed uruchomieniem skryptu omówionego w artykule, wykonanie skryptu powoduje wyświetlenie następującego komunikatu o błędzie:

Msg 15232, poziom 16, stan 1, wiersz 7
Certyfikat o nazwie "MS_AgentSigningCertificate.remote_server20009" już istnieje lub ten certyfikat został już dodany do bazy danych.

Jeśli wystąpią objawy omówione w artykule, nawet jeśli certyfikaty są takie same, skontaktuj się z działem pomocy technicznej firmy Microsoft (CSS), aby uzyskać więcej pomocy.