April 8, 2011 at 12:12 pm
We have some issues with after former dba gone, his account was diabled in AD, then some job owners , maintenance plan owners, and SSRS subscription owners in her account not working.
I have to change all back to mine.
Then it comes a good practice question in our company, the network people and business think all these should be run under a system account, maybe create a domain account, and use this account to create maintenace plan, jobs, subscriptions etc.
This sounds reasonable, but it sounds every time a dba need to create something in a server then we have to login to the server using service or system account. and we have 30 servers, and it may setup different account for each computer, and I guess they have to be setup also as sysadmin to do many jobs as a dba, may be local admin too, plus to have to enable remote access for this service account.
What is the best way to do this both for portablity and also easy way for DBA to do everyday jobs?
Also how can easily run SSMS using another account's login, currently we all use windows authenticated mode, so if I login as my self, I don't have an option to input another user's login, can you provide me the way of both for SSMS 2005 on windows 2003, also sql 2005 on windows 2008?
Thanks
April 8, 2011 at 12:25 pm
we make all jobs Etc. Owned by sa. At the time of creation you simply change to owner to sa. That is all that is required.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
April 8, 2011 at 12:34 pm
We have most jobs owned by sa. Rare occasion when it should be owned by something else. When it is, it is a domain account that is unattached to a person.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 8, 2011 at 2:24 pm
On some of our servers, we don't have sa account enabled.
For this purpose then, I need to enable it.
I read for Brad Mcgehee's best practice, never use sa to login to sql server.
If a domain account created only for this purpose, back to my first post question, what rights do you assign it, and how do you login using a different account to ssms when using windows authentication, please tell me for both 2005 and 2008 on windows 2008 or windows 2003.
Thanks
April 8, 2011 at 2:36 pm
In most cases the Job owner I do not beleive would even need any access to any DB's as long as it is a valid SQL login. We use SA as the owner but that is not to say we login to the database as SA. That is not required at all. You can change the owner by simply opening a JOB properties screen and type in a new owner. you could litterly select any valid login including widows autenticated logins from a domain.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
April 8, 2011 at 2:40 pm
Much the same as Dan. Having sa as the Job owner does not require anybody to login to SQL Server with the account nor for anybody to even know that password.
We don't let people log in with the sa account - just bad business to do that.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 8, 2011 at 2:57 pm
I don't know how recommended this would be in large but I have even used the follwoing to mass update every job to have the owner set to sa.
update sysjobs set owner_sid=0x01
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
April 8, 2011 at 3:00 pm
One thing to keep in mind - if you create scheduled maintenance plans, the jobs it creates are owned by the person creating the MP.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 8, 2011 at 3:06 pm
WayneS (4/8/2011)
One thing to keep in mind - if you create scheduled maintenance plans, the jobs it creates are owned by the person creating the MP.
I handle this by updating the table sysdtspackages90 (2005) or sysssispackages (2008) and manually changing the owner to sa. I do this before I schedule the maintenance plans, and that way - the jobs will be owned by sa when they are created.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 8, 2011 at 3:22 pm
Thanks all.
So first I do need to enable sa account, correct?
For maintenance plan, I found that you have to change owner to the plan too, if you only change job owner if somebody changed maintenace plan it will go back.
Besides jobs, have you ever use a system or service account to do the job, for example recently i notice if in ssrs you create a subscription, and if when you ad account is disabled, that subscription will no longer work.
Not sure if it is good to use SA to create subscription for reports.
April 8, 2011 at 3:31 pm
And I see in sql server 2008 there is a sql agent service account, NT AUTHORITY\SYSTEM.
Is it better to change job owner to this account?
April 8, 2011 at 3:31 pm
Jeffrey Williams-493691 (4/8/2011)
WayneS (4/8/2011)
One thing to keep in mind - if you create scheduled maintenance plans, the jobs it creates are owned by the person creating the MP.I handle this by updating the table sysdtspackages90 (2005) or sysssispackages (2008) and manually changing the owner to sa. I do this before I schedule the maintenance plans, and that way - the jobs will be owned by sa when they are created.
And here we have yet another one of those nuggets I'd like to put into a keepers file - but the briefcase here on SSC only handles articles.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 8, 2011 at 3:56 pm
annasql (4/8/2011)
And I see in sql server 2008 there is a sql agent service account, NT AUTHORITY\SYSTEM.Is it better to change job owner to this account?
No.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 8, 2011 at 3:58 pm
WayneS (4/8/2011)
Jeffrey Williams-493691 (4/8/2011)
WayneS (4/8/2011)
One thing to keep in mind - if you create scheduled maintenance plans, the jobs it creates are owned by the person creating the MP.I handle this by updating the table sysdtspackages90 (2005) or sysssispackages (2008) and manually changing the owner to sa. I do this before I schedule the maintenance plans, and that way - the jobs will be owned by sa when they are created.
And here we have yet another one of those nuggets I'd like to put into a keepers file - but the briefcase here on SSC only handles articles.
Interesting thing, I have started dumping these little nuggets into Solutions inside of SSMS. Then the Solution saves the script to a nice orderly file system underneath and I only need to have ssms open. Beyond that, I would copy the solution up to Google Drive.:w00t:
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 8, 2011 at 4:07 pm
Please help me to understand what you said, i found all these little helpful tips and I save it in text files, but it's hard to pull quickly out when I need it.
How do you save this little tips?
When you guys said SSC brief case, solution, and google drive, what do you mean?
Thanks
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply