SA account in windows authentication mode.

Question

SA account in windows authentication mode.

Duran

SSChampion

Points: 13259
More actions
July 22, 2010 at 7:34 am

#139923

Hello I have a quick question for somebody, I was wondering what should own the system and user databases in SQL server when the server is in Windows Authentication mode, as sa is disabled?
Can the sa account still own dbs? Is it better to still have the sa own the system db's and a dedicated windows account to own the user db's?
Thank you in advance for your replies.
D.

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Adiga One Orange Chip Points: 27284 More actions · Answer 1

Using Windows Authentication only controls the access to the instance. Authentication level will not have any impact Ownership. Having SA as the owner even in this case will not have any impacts.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

Sa is administrator, is has rights of everything. Some companies disable the "sa" account and let users handles everything with windows auth. So windows auth can be restricted to some databases or areas as per requirement.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Duran SSChampion Points: 13259 More actions · Answer 3

Thanks for getting back guys, preumably this would be different if you wanted the sa account to be the owner of scheduled agent jobs, I'd imagine they would fail if the sa account was disabled and they owned the jobs?

Thank you,

D.

Sudeepta SSCommitted Points: 1696 More actions · Answer 4

It is preferable to set the job owner for critical jobs as sa. If the sa account is disabled and the instance is running using Windows Authentication, still the job will execute.

Thanks & Regards,
Sudeepta.
http://twitter.com/skganguly

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 5

Sudeepta (7/23/2010)
If the sa account is disabled and the instance is running using Windows Authentication, still the job will execute.

this is new to me. are you sure for it ?

additionally , i would say create a service account( Windows Auth. ) and use that in all jobs .

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Perry Whittle SSC Guru Points: 234064 More actions · Answer 6

Bhuvnesh (7/26/2010)
this is new to me. are you sure for it ?

Yes!!

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 7

Perry Whittle (7/26/2010)
Bhuvnesh (7/26/2010)
this is new to me. are you sure for it ?
Yes!!

then what are the task/operations can be performed with disabled "sa" and with enabled "sa".?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Sudeepta SSCommitted Points: 1696 More actions · Answer 8

additionally , i would say create a service account( Windows Auth. ) and use that in all jobs .

Normally, the windows accounts bound with a security policy to changes the password at a certain intervals. We don't want to stop the critical jobs because of a password expired. Its an additional measure to run the job continuously along with a disable sa account so that the security will not be compromised.

Hope, I didn't confuse you..:-)

Thanks & Regards,
Sudeepta.
http://twitter.com/skganguly