July 30, 2003 at 1:37 pm
I didn’t know if this should be posted in the Administration or Security topics, but here goes: We have problem on one of our SQL Server instances in our active/active cluster. In both instances of the SQL Server cluster, we set up identical logins to be the owner of and run our differential backup jobs. These logins are users in the db_datareader and db_backupoperator roles in databases that have nightly differential backups scheduled. We created these logins so a non-system administrator login could be used in VB to call stored procedures that use sp_start_job to start the differential backup jobs. These stored procedures start the jobs correctly on both instances using these logins. On the first instance, the jobs run and create the differential backups. On the second instance, the differential backup step fails with the following error in job history:
Unable to perform a SETUSER to the requested username '' because the username is invalid for database 'master'. The step failed.
When we run the jobs with a login that has system administrator privileges, the differential backup step will run. We have checked everything we can think of and the logins look the same and the servers seem to be set up the same. Both are SQL Server 2000 SP 3 with no other patches installed. I don’t know if this could be the problem, but late in May SQL Server was inoperative after a power failure on this second instance and to recover we rebuilt the master and restored msdb and the user databases.
Any ideas?
July 30, 2003 at 2:04 pm
quote:
These logins are users in the db_datareader and db_backupoperator roles in databases that have nightly differential backups scheduled. We created these logins so a non-system administrator login could be used in VB to call stored procedures that use sp_start_job to start the differential backup jobs. These stored procedures start the jobs correctly on both instances using these logins. On the first instance, the jobs run and create the differential backups. On the second instance, the differential backup step fails with the following error in job history:
I don't think the login with only db_datareader and db_backupoperator roles can run sp_start_job.
quote:
Unable to perform a SETUSER to the requested username '' because the username is invalid for database 'master'. The step failed.
SETUSER permissions default to members of the sysadmin fixed server role and are not transferable.
Check whether login in both instance has same access to master and msdb databases. It looks like the login in first instance is the member of ServerTargetRole in msdb.
Edited by - allen_cui on 07/30/2003 2:07:28 PM
July 30, 2003 at 2:14 pm
Allen,
The sp_start_job is working correctly on both servers, because the jobs all start. On instance one, the jobs run the differential backups and on instance 2, I get the error after the job starts on step 1, the differential backup step. The step does not perform a setuser statement, just a TSQL backup statement with this syntax:
BACKUP DATABASE [XXX] TO [DV_XXX_BACKUP_DIFF] WITH INIT, DIFFERENTIAL, SKIP
July 30, 2003 at 2:20 pm
Allen,
Also, I did not give any special access on instance 1 because the jobs were working without it. I did make the login a user in master and msdb on instance 2 and that did not help. I also tried making it a member of the TargetServersRole in msdb on instance 2, but then I could not execute sp_start_job. I looked and saw a Deny permission on sp_start_job procedure for that role, so I took the user out of that role.
Thanks,
Linda
July 30, 2003 at 2:25 pm
quote:
The sp_start_job is working correctly on both servers, because the jobs all start.
Does the user own the job? If it is, the user should be able to start the job by running sp_start_job. I guess it is in your case.
Edited by - allen_cui on 07/30/2003 2:25:03 PM
July 30, 2003 at 2:29 pm
Allen,
Yes the user owns the jobs on both instances.
Linda
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply