Difference of Rights for SA user and Other users with SA privileges

Question

Difference of Rights for SA user and Other users with SA privileges

ankur_libra

Hall of Fame

Points: 3999
More actions
December 29, 2010 at 7:43 am

#141980

Hi All,
Please clarify about the specific special Privileges which are possessed by SA user and not by other users with system administrator rights in SQl Server.
Thanks in advance.
With regards,
Ankur

Viewing 13 posts - 1 through 12 (of 12 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

They're the same. sa is just a default sysadmin login. Nothing more nor less.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

ankur_libra Hall of Fame Points: 3999 More actions · Answer 2

Thanks GSquared for your response. 🙂

Sometimes our clients are resisting to provide us the SA user password, whereas they used to create an another user (say 'x') with System Administrator rights and allow us to connect using 'X'.

So, kindly tell is there any problem especially in Disaster Recovery scenarios, if SA user password is not available to us and we are unable to use 'SA' user?

With regards,

Ankur

GSquared SSC Guru Points: 260824 More actions · Answer 3

If the master database goes down, and takes the other accounts with it, you may not be able to restore it. On the other hand, that's where the sa password is also stored, so having it wouldn't help much. You'd have no access to the server till master was rebuilt/restored anyway.

Outside of that (which is very, very rare), it should have no effect.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

ankur_libra (12/29/2010)
So, kindly tell is there any problem especially in Disaster Recovery scenarios, if SA user password is not available to us and we are unable to use 'SA' user?

No. General recommendations are to disable the sa login completely and never use it.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ankur_libra Hall of Fame Points: 3999 More actions · Answer 5

Hi,

Thanks to both the Masters for their response.

But Gail you have written your response as 'No'. Request to please calirfy it further.

So, it means there is no issue, if we donot have SA user password and can use the other user (with system administrator rights) in place of it, in every disaster scenario?

With regards,

Ankur

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

ankur_libra (12/29/2010)
But Gail you have written your response as 'No'. Request to please calirfy it further.

What clarification do you want? You asked if there's any problems with having a sysadmin login that's not 'sa'. The answer to that is 'No'. I'm not sure what you're looking for.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ankur_libra Hall of Fame Points: 3999 More actions · Answer 7

Hi Gila,

Thanks for the feedback

As I mentioned already in prevoius post that

"Sometimes our clients are resisting to provide us the SA user password, whereas they used to create an another user (say 'x') with System Administrator rights and allow us to connect using 'X'. "

So, if we donot have SA user password, would it be impacting at any moment especially in disaster recovery scenarios.

In other words, can we perform all actions using the other system admin accounts?

With regards,

Ankur

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

ankur_libra (12/30/2010)
Sometimes our clients are resisting to provide us the SA user password

Good for them. That's exactly what they should be doing. You shouldn't even be asking for the sa password to be quite honest.

In other words, can we perform all actions using the other system admin accounts?

As already stated in this thread:

GSquared (12/29/2010)
sa is just a default sysadmin login. Nothing more nor less.

GilaMonster (12/29/2010)
General recommendations are to disable the sa login completely and never use it.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ankur_libra Hall of Fame Points: 3999 More actions · Answer 9

Hi All,

Thanks for the clarifying the facts and bearing with me. 🙂

With regards,

Ankur

wodom Hall of Fame Points: 3813 More actions · Answer 10

GilaMonster (12/29/2010)
General recommendations are to disable the sa login completely and never use it.

Disable, or delete? (And create another login with same permissions for cases where you need it.)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

wodom (6/17/2016)
GilaMonster (12/29/2010)
General recommendations are to disable the sa login completely and never use it.
Disable, or delete? (And create another login with same permissions for cases where you need it.)

You can't delete 'sa'.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

wodom Hall of Fame Points: 3813 More actions · Answer 12

GilaMonster (6/17/2016)
You can't delete 'sa'.

Oh, OK -- I thought I saw an article or forum entry where someone said he did, but maybe either he or I misunderstood something. I can't find the original, but I did find a forum entry by you, saying we can rename sa. That achieves the same goal as deleting it and adding a different one; and more easily. So that's what I'm doing.

Thanks!