Scheduled Task Permissions

[dg043]

I maintain a server running SQL Server 6.5 and this SQL Server has several scheduled tasks. I have arranged each database on the server so that there is a user called 'standby' who is aliased to 'dbo'. I have then created a scheduled task (as this user) to backup the relevant database to tape. The command that I run to accomplish this task is:

DUMP DATABASE <database name> TO Tape1 WITH NOUNLOAD , STATS = 10, NOINIT , NOSKIP

This task generates the following error when ran:

Unable to perform a SETUSER to the requested username 'standby'. Either the SQLExecutive service account cannot establish a trusted SA connection, or the username is invalid for database '<database name>'.

The problem is that this task runs without a problem when I create it as user 'sa', but not when I create it as 'standby'. I guess this is a permissions issue, so is it possible to grant 'standby' the required access?

Dan Griffiths
Software Analyst
National Grid Transco (NGT)

 

[Catadmin]

Not having worked in a 6.5 interface, I'm going to take a guess. I hope I help more than I confuse. @=)

Have you checked to see what databases, if any, Standby is allowed into? Checking DBO might not help if Standby is denied access.

Also, I know in 7.0 and 2k, you can assign Server Roles and Database to a user. Do you have those in 6.5? If so, look for one that has permissions to do database backups and restores.

According to Books Online:

SETUSER

Allows a member of the sysadmin fixed server role or db_owner fixed database role to impersonate another user.

Who is Standby trying to impersonate?



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[mrdenny]

You'll need to give the standby user the rights to backup the databases. dbo doesn't have the rights to backup the database. You'll need to make the user standby a backup operator or a system administrator within the SQL Security Manager.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[dg043]

Thanks for your response, Denny. You have undoubtedly pointed me in the right direction but I am still a little confused (I have never even used SQL Security Manager before). How do I add the standby user to anything? The user standby is a SQL Server login and not a Windows NT login; should I therefore create it as a Windows NT login aswell? I think my SQL Server uses Standard security and from what I can understand from the confusing help, this renders SQL Security Manager meaningless. Is that true?

Dan Griffiths
Software Analyst
National Grid Transco (NGT)

 

[mrdenny]

Honestly I don't remember. Your taxing my SQL 6.5 memory. It may end up being easier with a NT account. Why on earth are you still running with SQL 6.5?

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)