August 21, 2002 at 11:24 am
Hello Everyone, we have a job that has been in place for a few years and has been running just fine. This week, it fails and gives this message:
"Unable to perform a SETUSER to the requested username 'dbo' because the username is invalid for database 'ALTADATA'. "
There are no database changes that I made that could cause this. It seems like it cannot find the user dbo, even though it is showing in EM. Any ideas? Thanks.
August 21, 2002 at 3:50 pm
Can someone help me with this please? I am getting ready to start a fire ceremony and burn the damn server in a few minutes.
August 21, 2002 at 4:48 pm
run profiler and see what is being run by the job.
Are you sure the job hasn't changed? what does the job do?
Steve Jones
August 21, 2002 at 4:59 pm
I am not sure what options I should use in Profiler and I don't have a book that explains it. Can you help with this one?
My job has 3 steps:
1. FTP a file and unzip it.
2. Restore the database.
3. Add users and give permissions.
I am sure that nothing has changed. I copied the same procedures that are called and created a database on my own machine and ran the job on my machine and got the same SETUSER error.
August 21, 2002 at 5:01 pm
Aha!
If you are restoring the database, pehaps the source database was changed. Where does the job fail? On the restore? On the add users?
Steve Jones
August 21, 2002 at 5:33 pm
The problems with the job started yesterday morning. First it could not restore because there was a user in the database. So the job failed. Then it was able to restore, but step 3 (add members and give permissions) failed with the error "Unable to connect to SQL Server '(local)'." I tried running all the steps several times and always the step 3 would fail with the above error. But at some point yesterday step 3 failed with the SETUSER error. And after that even step 1 fails with the SETUSER error. I think somehow it doesn't recognize the dbo user. Thanks for your help Steve.
August 22, 2002 at 1:35 am
I'm willing to be that the dbo user does not have an associated login.
Use sp_ChangeDbOwner '<<whatever login you want>>' within the database and the dbo user should be fine.
I often have to take copies of client databases and restore them on our machines so I have to do this quite a lot.
August 22, 2002 at 7:42 am
It appears to me as the broken login problem. Once you restore a database, the database users and logins are probably not been mapped properly.
Can you tell me what is the login name that you are using for the 'dbo' user?
August 22, 2002 at 9:04 am
I have this problem every now and then on new and restored databases. Have yet to figure out what is causing it. The user 'dbo' is still in the database, but it is a broken login. I have to go in and update the sysusers table for the database and reset 'dbo' and a valid user.
The error message I get is:
Server: Msg 4604, Level 16, State 3, Line 1
There is no such user or group 'dbo'.
Reset 'dbo':
sp_configure 'allow updates',1
reconfigure with override
update sysusers
set status = 2
,sid = 0x01
where name = 'dbo'
sp_configure 'allow updates',0
reconfigure with override
You might give it a try.
August 23, 2002 at 12:38 pm
Just to update everyone,
Randy's script worked. I was able to run the job on my computer which I used to test. I ran the script on the server and the next time the job is due to run on Tuesday morning. I'll update everyone about the result. Thanks to everyone for their help!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply