Beginning with SEP 11 RU5, a new stored procedure "SEM_GETUSN" has been introduced. This procedure performs USN updates (one of the heaviest functions the SQL server performs normally). Using this new stored procedure should improve performance on the MS SQL 2005 backend server.
In a clean install of RU5 (or newer) the stored procedure is enabled by default. If the SEPMs were upgraded from a previous version, it is not automatically enabled. It can be enabled by granting execute rights to the SEM5 account.
In order to run SEM_GETUSN, the SEM5 account needs execute rights.
To check if these execute rights are in place:
From Microsoft SQL Server Management Studio, log in as sem5 user and type in command below and execute:
If the execution result shows a number, then the execute permission is enabled.
If the execution result shows "The EXECUTE permission was denied on the object 'SEM_GETUSN', database 'sem5', schema 'dbo'", it means sem5 user is not granted EXECUTE permission.
How to grant EXECUTE permission to sem5 user?
Before granting EXECUTE permission, the SEPM administrator must stop all SEPMs connecting with the SQL Server 2005 database servers.
1) Stop all SEPMs connecting with this database server;
2) Login Microsoft SQL Server management Studio as 'sa' or other administrator account;
3) Locate the stored procedure SEM_GETUSN, It is under Databases\[sem5 database]\Programability\Stored Procedures.
4) Select 'Properties' and move to 'Permissions';
5) Click on 'Add' button under "Users or roles:" frame, a new window is shown up;
6) Click 'Browse..." button and a new window is shown up;
7) Check the box for sem5 user and click 'OK' button.
8) Under "Explicit permissions for sem5:" frame, check 'Alter' and 'Execute' permissions under the 'Grant' column;
9) Verify the EXECUTE permission according to steps above "How to verify if sem5 is granted EXECUTE permission for the stored procedure?";
10) Once the EXECUTE permission is confirmed, start all SEPMs connection with this database server.
If Replication is in Use
In replicating environments, the SEPM administrator must grant the execute permission to every database server on each of the sites.