February 13, 2008 at 12:31 pm
I am trying to clean up the Logins on the server that we don't need and I see that there is a Login devoted for database backups. I looked under Maintenace plans and found that this Login is being used for the backup jobs that are executed.
I would like to get rid of the Login if possible but I would need to give the database backup another Login to execute under. I see that there is an (server)\SqlServer2005AgentUser$(server) default Login that Agent uses.
Should I use this login for database backups? Should I keep a single login solely devoted for backups? Should this login only have backup fixed server role? Should it be Windows or SQL Server authentication?
I'm not sure what the best practices are for executing Maintenance Plans / Agent Jobs are as far as what their connections (logins) should be. Help would be greatly appreciated, thanks!
February 14, 2008 at 6:13 am
Subject to your company policy if you are using mixmode security you can assign the 'SA' for the maintenance jobs.
Do not forget to check your company policy; why they have seperate login for the backup?
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
February 14, 2008 at 10:14 am
Just a guess, that login may have rights to another area that the database files are being sent to. Just make sure you trace the entire backup to ensure the login is not needed to keep all the moving parts moving. My suggestion would be to create a new login and run the job utilzing that log on to see if it hangs anywhere. If not, then delete the login in question and the new one you created. if not, look for access rights somewhere down line.
Marvin Dillard
Senior Consultant
Claraview Inc
February 14, 2008 at 3:58 pm
Use your agent login itself to be used for the Maintenance plans.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
February 14, 2008 at 5:49 pm
While having the logfiles compile and accumulate the important information in the logs about the database server activities; I would say it would be a very good idea to have separate "service account"s for different backkup processes or tasks. Thus, you would easily recognize the ill-performing task / job whether in SSMS or the event logs.
By the way, the information in the event logs are also retrievable by the front-end and of course by the back-end applications or software.
So, having dedicated service accounts may be a good thing.
I have been wrong before... 🙂
thx
John Esraelo
Cheers,
John Esraelo
February 15, 2008 at 2:21 pm
The specific id doesn't matter in as much as it either has rights to do the backups or it doesn't. The backups themselves run under the main SQL service account. If you want to be able to track who is doing the backups then the rights should be granted to individual accounts, whether windows or SQL. If you only use the agent to do backups, then you really don't need any other accounts to have backup rights.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply