How to Perform Required Post-Replication Setup Tasks

After you successfully complete the SQL Server site database replication process, you must grant the necessary permissions to site systems to access the site database replica data.

Note
It is not recommended to grant site system computers database owner (dbo) rights to the replicated SQL Server site database.

You can use the following procedures to configure remote site systems permissions to access replicated site database information from the SQL Server site database replica:

Create required database roles.
Grant the necessary security permissions to database roles to access database objects.
Create database user accounts for site systems to access the site database replica.
Add site system database user accounts to the appropriate database roles.

Create Required Database Roles

During Configuration Manager 2007 primary site setup, SQL Server 2005 database roles are created to secure site database access. When you configure a SQL Server database to host a replica of the site database, you must create these database roles manually.

For more information about SQL Server database roles that are created by setup, see About the Database Roles for Configuration Manager.

To create required database roles on the site database replica

In the SQL Server 2005 SQL Server Management Studio console, navigate to <site database replica name>.
Right-click <site database replica name>, and then click New Query to open a new query editor window.
In the new query editor window, enter the following commands, and then click Execute to create the required database roles on the site database replica:
Copy Code
CREATE ROLE [smsdbrole_MP] AUTHORIZATION [dbo] CREATE ROLE [smsdbrole_DMP] AUTHORIZATION [dbo] CREATE ROLE [smsdbrole_SLP] AUTHORIZATION [dbo]

	Copy Code
CREATE ROLE [smsdbrole_MP] AUTHORIZATION [dbo] CREATE ROLE [smsdbrole_DMP] AUTHORIZATION [dbo] CREATE ROLE [smsdbrole_SLP] AUTHORIZATION [dbo]

Grant the Necessary Security Permissions to Database Roles to Access Database Objects

During Configuration Manager 2007 primary site setup, SQL Server 2005 database roles are granted the necessary security permissions to access site database information. When you configure a SQL Server database to host a replica of the site database, you must grant these security permissions manually.

To configure security permissions for site systems to access site database replica information

In the SQL Server 2005 SQL Server Management Studio console, navigate to <site database replica name>.
Right-click <site database replica name>, and then click New Query to open a new query editor window.

In the new query editor window, enter the following commands, and then click Execute to configure security permissions to database objects for Configuration Manager 2007 database roles:

	Copy Code
GRANT SELECT ON Sites TO [smsdbrole_SLP] GRANT SELECT ON SysResList TO [smsdbrole_SLP] GRANT SELECT ON RoamingBoundaryIPSubnet TO [smsdbrole_SLP] GRANT SELECT ON RoamingBoundaryIPv6Prefix TO [smsdbrole_SLP] GRANT SELECT ON RoamingBoundaryIPRange TO [smsdbrole_SLP] GRANT SELECT ON RoamingBoundaryADSite TO [smsdbrole_SLP] GRANT EXECUTE ON MP_GetAllInventoryClasses TO [smsdbrole_MP] GRANT EXECUTE ON MP_GetClientIDFromMacAddress TO [smsdbrole_MP] GRANT EXECUTE ON MP_GetClientIDFromSmbiosID TO [smsdbrole_MP] GRANT EXECUTE ON MP_GetInventoryClassProperties TO [smsdbrole_MP] GRANT EXECUTE ON MP_GetSiteInfoFromADSite TO [smsdbrole_MP] GRANT EXECUTE ON MP_GetSiteInfoFromIPAddress TO [smsdbrole_MP] GRANT EXECUTE ON MP_GetSiteInfoFromIPv6Prefix TO [smsdbrole_MP] GRANT EXECUTE ON MP_GetSiteInfoUnified TO [smsdbrole_MP] GRANT EXECUTE ON MP_GetContentDPInfoProtected TO [smsdbrole_MP] GRANT EXECUTE ON MP_GetContentDPInfoUnprotected TO [smsdbrole_MP] GRANT EXECUTE ON MP_GetProtectedSMPSites TO [smsdbrole_MP] GRANT EXECUTE ON MP_GetUnprotectedSMPSites TO [smsdbrole_MP] GRANT EXECUTE ON MP_GetStateMigClientInfo TO [smsdbrole_MP] GRANT EXECUTE ON MP_GetStateMigAssocInfo TO [smsdbrole_MP] GRANT EXECUTE ON MP_GetMigrationInfoForRestoreClient TO [smsdbrole_MP] GRANT EXECUTE ON MP_GetMigrationInfoUsersForRestoreClient TO [smsdbrole_MP] GRANT EXECUTE ON MP_GetSelectiveDownloadMap TO [smsdbrole_MP] GRANT EXECUTE ON MP_GetPeerDPList TO [smsdbrole_MP] GRANT EXECUTE ON MP_GetWSUSServerLocations TO [smsdbrole_MP] GRANT EXECUTE ON MP_GetPendingPackagesForBranchDP TO [smsdbrole_MP] GRANT EXECUTE ON MP_GetPolicyBody TO [smsdbrole_MP] GRANT EXECUTE ON MP_GetPolicyBodyAfterAuthorization TO [smsdbrole_MP] GRANT EXECUTE ON MP_IsPolicyBodyAuthorized TO [smsdbrole_MP] GRANT EXECUTE ON MP_IsClientRegistered TO [smsdbrole_MP] GRANT EXECUTE ON sp_GetPublicKeyForSMSID TO [smsdbrole_MP] GRANT EXECUTE ON MP_GetEncryptionCertificateForSMSID TO [smsdbrole_MP] GRANT EXECUTE ON MP_GetProvisioningModePolicyAssignments TO [smsdbrole_MP] GRANT EXECUTE ON MP_GetMachinePolicyAssignments TO [smsdbrole_MP] GRANT EXECUTE ON MP_GetUserAndUserGroupPolicyAssignments TO [smsdbrole_MP] GRANT EXECUTE ON MP_GetListOfMPsInSite TO [smsdbrole_MP] GRANT EXECUTE ON MP_GetHINVLastUpdateTime TO [smsdbrole_MP] GRANT EXECUTE ON MP_GetLocalSitesFromAssignedSite TO [smsdbrole_MP] GRANT EXECUTE ON MP_GetMPSitesFromAssignedSite TO [smsdbrole_MP] GRANT EXECUTE ON MP_GetMPListForSite TO [smsdbrole_MP] GRANT EXECUTE ON MP_GetSdmPackageBody TO [smsdbrole_MP] GRANT EXECUTE ON MP_MatchDrivers TO [smsdbrole_MP] GRANT EXECUTE ON sp_GetPublicKeySMSUID TO [smsdbrole_MP] GRANT EXECUTE ON DMP_GetMachinePolicies TO [smsdbrole_DMP] GRANT EXECUTE ON DMP_GetPackageVersion TO [smsdbrole_DMP] GRANT EXECUTE ON DMP_GetSettings TO [smsdbrole_DMP] GRANT EXECUTE ON DMP_GetSoftwareDistBody TO [smsdbrole_DMP] GRANT EXECUTE ON DMP_GetSoftwareDistIDs TO [smsdbrole_DMP] GRANT EXECUTE ON DMP_GetHinvTranslations TO [smsdbrole_DMP] GRANT EXECUTE ON DMP_GetDiscoveryTranslations TO [smsdbrole_DMP]

Copy Code

GRANT SELECT ON Sites TO [smsdbrole_SLP]
GRANT SELECT ON SysResList TO [smsdbrole_SLP]
GRANT SELECT ON RoamingBoundaryIPSubnet TO [smsdbrole_SLP]
GRANT SELECT ON RoamingBoundaryIPv6Prefix TO [smsdbrole_SLP]
GRANT SELECT ON RoamingBoundaryIPRange TO [smsdbrole_SLP]
GRANT SELECT ON RoamingBoundaryADSite TO [smsdbrole_SLP]
GRANT EXECUTE ON MP_GetAllInventoryClasses TO [smsdbrole_MP]
GRANT EXECUTE ON MP_GetClientIDFromMacAddress TO [smsdbrole_MP]
GRANT EXECUTE ON MP_GetClientIDFromSmbiosID TO [smsdbrole_MP]
GRANT EXECUTE ON MP_GetInventoryClassProperties TO [smsdbrole_MP]
GRANT EXECUTE ON MP_GetSiteInfoFromADSite TO [smsdbrole_MP]
GRANT EXECUTE ON MP_GetSiteInfoFromIPAddress TO [smsdbrole_MP]
GRANT EXECUTE ON MP_GetSiteInfoFromIPv6Prefix TO [smsdbrole_MP]
GRANT EXECUTE ON MP_GetSiteInfoUnified TO [smsdbrole_MP]
GRANT EXECUTE ON MP_GetContentDPInfoProtected TO [smsdbrole_MP]
GRANT EXECUTE ON MP_GetContentDPInfoUnprotected TO [smsdbrole_MP]
GRANT EXECUTE ON MP_GetProtectedSMPSites TO [smsdbrole_MP]
GRANT EXECUTE ON MP_GetUnprotectedSMPSites TO [smsdbrole_MP]
GRANT EXECUTE ON MP_GetStateMigClientInfo TO [smsdbrole_MP]
GRANT EXECUTE ON MP_GetStateMigAssocInfo TO [smsdbrole_MP]
GRANT EXECUTE ON MP_GetMigrationInfoForRestoreClient TO [smsdbrole_MP]
GRANT EXECUTE ON MP_GetMigrationInfoUsersForRestoreClient TO [smsdbrole_MP]
GRANT EXECUTE ON MP_GetSelectiveDownloadMap TO [smsdbrole_MP]
GRANT EXECUTE ON MP_GetPeerDPList TO [smsdbrole_MP]
GRANT EXECUTE ON MP_GetWSUSServerLocations TO [smsdbrole_MP]
GRANT EXECUTE ON MP_GetPendingPackagesForBranchDP TO [smsdbrole_MP]
GRANT EXECUTE ON MP_GetPolicyBody TO [smsdbrole_MP]
GRANT EXECUTE ON MP_GetPolicyBodyAfterAuthorization TO [smsdbrole_MP]
GRANT EXECUTE ON MP_IsPolicyBodyAuthorized TO [smsdbrole_MP]
GRANT EXECUTE ON MP_IsClientRegistered TO [smsdbrole_MP]
GRANT EXECUTE ON sp_GetPublicKeyForSMSID TO [smsdbrole_MP]
GRANT EXECUTE ON MP_GetEncryptionCertificateForSMSID TO [smsdbrole_MP]
GRANT EXECUTE ON MP_GetProvisioningModePolicyAssignments TO [smsdbrole_MP]
GRANT EXECUTE ON MP_GetMachinePolicyAssignments TO [smsdbrole_MP]
GRANT EXECUTE ON MP_GetUserAndUserGroupPolicyAssignments TO [smsdbrole_MP]
GRANT EXECUTE ON MP_GetListOfMPsInSite TO [smsdbrole_MP]
GRANT EXECUTE ON MP_GetHINVLastUpdateTime TO [smsdbrole_MP]
GRANT EXECUTE ON MP_GetLocalSitesFromAssignedSite TO [smsdbrole_MP]
GRANT EXECUTE ON MP_GetMPSitesFromAssignedSite TO [smsdbrole_MP]
GRANT EXECUTE ON MP_GetMPListForSite TO [smsdbrole_MP]
GRANT EXECUTE ON MP_GetSdmPackageBody TO [smsdbrole_MP]
GRANT EXECUTE ON MP_MatchDrivers TO [smsdbrole_MP]
GRANT EXECUTE ON sp_GetPublicKeySMSUID TO [smsdbrole_MP]
GRANT EXECUTE ON DMP_GetMachinePolicies TO [smsdbrole_DMP]
GRANT EXECUTE ON DMP_GetPackageVersion TO [smsdbrole_DMP]
GRANT EXECUTE ON DMP_GetSettings TO [smsdbrole_DMP]
GRANT EXECUTE ON DMP_GetSoftwareDistBody TO [smsdbrole_DMP]
GRANT EXECUTE ON DMP_GetSoftwareDistIDs TO [smsdbrole_DMP]
GRANT EXECUTE ON DMP_GetHinvTranslations TO [smsdbrole_DMP]
GRANT EXECUTE ON DMP_GetDiscoveryTranslations TO [smsdbrole_DMP]

Create Database User Accounts for Site Systems to Access the Site Database Replica

Note
When using a database connection account for the management point or server locator point site system roles instead of using the computer account of the site system to access the database replica, you must create a database user account for the domain user account that is configured for the site system database connection account instead of the site system computer account.For more information about the management point database connection account, see About the Management Point Database Connection Account.For more information about the server locator point database connection account, see About the Server Locator Point Database Connection Account.

Note

When using a database connection account for the management point or server locator point site system roles instead of using the computer account of the site system to access the database replica, you must create a database user account for the domain user account that is configured for the site system database connection account instead of the site system computer account.For more information about the management point database connection account, see About the Management Point Database Connection Account.For more information about the server locator point database connection account, see About the Server Locator Point Database Connection Account.

To create database user accounts for site systems to access the site database replica

In the SQL Server 2005 SQL Server Management Studio console, navigate to <site database replica name>.
Right-click <site database replica name>, and then click New Query to open a new query editor window.
In the new query editor window, enter the following command, and then click Execute to create database user accounts for site systems that require access to site database replica information.
Copy Code
CREATE USER [<DOMAIN\COMPUTERNAME$>] FOR LOGIN [<DOMAIN\COMPUTERNAME$>] WITH DEFAULT_SCHEMA=[dbo]
Note

<DOMAIN\COMPUTERNAME> should be replaced by the domain name and computer name of the site server computer account that requires access to the site database replica information.

	Copy Code
CREATE USER [<DOMAIN\COMPUTERNAME$>] FOR LOGIN [<DOMAIN\COMPUTERNAME$>] WITH DEFAULT_SCHEMA=[dbo]

Note
<DOMAIN\COMPUTERNAME> should be replaced by the domain name and computer name of the site server computer account that requires access to the site database replica information.

Add Site System Database User Accounts to the Appropriate Database Roles

During Configuration Manager 2007 primary site setup, and when new site systems are added to a site, the computer account of the site system is automatically added to the appropriate database role if required. When you configure a SQL Server database to host a replica of the site database, you must grant these security permissions manually.

To add site system database user accounts to the appropriate database roles

In the SQL Server 2005 SQL Server Management Studio console, navigate to <site database replica name>.
Right-click <site database replica name>, and then click New Query to open a new query editor window.

In the new query editor window, enter the following command, and then click Execute to add site system database user accounts to the appropriate database role.

	Copy Code
EXEC sp_addrolemember '<dbrole name>' , '<dbuser name>'

Note
Replace <dbrole name> with smsdbrole_MP, smsdbrole_DMP, or smsdbrole_SLP. The <dbuser name> must be the name of the database user name that is created for the site system that hosts the management point, device management point, or server locator point site system role.