SA Login

Question

Post reply

SA Login

binu.m.a

SSC Enthusiast

Points: 127
More actions
October 5, 2007 at 11:50 am

#179901

Hi
In which situation we need the 'sa' login necessarry,
Binu

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

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

noeld SSC Guru Points: 96590 More actions · Answer 1

sysadmin is necessary for SERVER wide settings changes.

* Noel

binu.m.a SSC Enthusiast Points: 127 More actions · Answer 2

binu.m.a

SSC Enthusiast

Points: 127

October 5, 2007 at 12:12 pm

#739671

Hi

Thanks. Can U give some examples

Binu

noeld SSC Guru Points: 96590 More actions · Answer 3

binu.ma (10/5/2007)
Hi
Thanks. Can U give some examples
Binu

Examples:

sp_configure -- although you can use serveradmin role I prefer this is only done by sysadmins

sp_cycle_errorlog

sp_procoption

sp_addextendedproc -- although you can use CLR now but Only members of "sysadmin" can

-- create and alter UNSAFE assemblies.

sp_add_log_shipping_primary_database

etc....

* Noel

binu.m.a SSC Enthusiast Points: 127 More actions · Answer 4

hi

Thanks for all

But in an interview i got a question that which is the situation we need sa login other than logins with sysadmin roles.

binu

noeld SSC Guru Points: 96590 More actions · Answer 5

I am not really sure I understand your question.

Those stored procedures can not be executed by non sa users. And they imply cycling the error log, setting up log shipping, adding server assamblies ...

Can you rephrase the "question" ?

* Noel

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 6

One case you will need it is if all other members of the sysadmin fixed server role have been removed. In this situation, only the sa account is left available. Therefore, it's important to set and store the sa account password even if you are planning on using only Windows authentication.

The sa account can be an effective backdoor to a system which you have no other access to. If you are Windows authentication only mode, you can stop SQL Server, make the appropriate registry change, and then restart SQL Server. At that point, you can login as sa and re-establish the security to get in via other means.

K. Brian Kelley
@kbriankelley

binu.m.a SSC Enthusiast Points: 127 More actions · Answer 7

Hi

Thanks

If my SQLSERVER is in windows authentication mode how can I change it to

mixed mode through registry settings.pls give me the details

binu

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 8

I wrote an article that covers how to use the back door:

SQL Server Security: Security Admins[/url]

There are new registry locations in SQL Server 2005, but if you take a look in the registry based on this article, you should be able to find them.

K. Brian Kelley
@kbriankelley

binu.m.a SSC Enthusiast Points: 127 More actions · Answer 9

Thanks

Can u help me how to do this in SQL 2000

Binu

noeld SSC Guru Points: 96590 More actions · Answer 10

binu.ma (10/6/2007)
Thanks
Can u help me how to do this in SQL 2000
Binu

Those Registry keys apply to SQL Server 2000

* Noel

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 11

binu.ma (10/6/2007)

Can u help me how to do this in SQL 2000

As Noel pointed out, the article gives the registry keys for SQL Server 2000. As far as helping you, we've given you all the information you need, but at this point you're going to have to actually go and look for yourself on a computer. If you aren't familiar with the registry, perhaps taking time to learn what it is, how it works, and why you have to be careful with it is where you should focus your efforts, rather than trying to memorize a fact to pass an interview. If you do understand the registry, then the information in that article tells you exactly where to look to find the information. All you need to do is actually do it.

K. Brian Kelley
@kbriankelley