how do i enable "sa" login. There are no other id's which have sysadmin privileges.

I have sql server 2005 installed, "sa" is disable and an alternate id "abc" is used instead of "sa". The sysadmin rights of "abc" were revoked somehow and now i cant grant back the rights. How to enable "sa" or grant back rights to "abc".

Need Urgent help.

more ▼

asked Jun 08, 2012 at 11:41 AM in Default

avatar image

0 2 2 5

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

@Ashok Nayak shows you one way of doing it.

But I believe, it will only work if the "BUILTIN\Administrators" group is already a member of the sysadmin server role (Not the case in most security enhanced environments).

And If the "BUILTIN\Administrators" group is already the member, then you do not have to create a windows user (Again, creating a new server administrator may not be permissible in most of the secured environments and would require certain policies to be followed).

Either way, you just have to be logged in with a server admin user (could be a domain account). Start the SQL Server in single user mode. If the "BUILTIN\Administrators" group is not the member of sysadmin role, and if the organization's policy allows, then add the group to sysadmin role. Otherwise, you can create a new login from a domain user and add that login to sysadmin server role. For e.g.

 EXEC sp_addsrvrolemember 'DOMAIN\username', 'sysadmin'

Please be careful in such scenario, so that you may not breach any policy. Better would be to have the System Administrator on board and test it in a controlled environment before implementing it in production.

more ▼

answered Jun 11, 2012 at 12:22 PM

avatar image

Usman Butt
14.9k 6 13 21

(comments are locked)
10|1200 characters needed characters left

--create user goto computer management--group--administrator-create a new user

--create account 'test' at os level with admin privileged --login with the id test with OS level --stop sql server 2005 using with command -- netstop mssqlserver

--start sql server 2005 in single user mode using this command netstart mssqlserer /m

--login into sql server 2005 using the the id test --after connect ted to sql server in single user mode, open a new query alter login sa enable

--now you stop the sql server , and start it normaly netstart mssqlserver

more ▼

answered Jun 08, 2012 at 12:54 PM

avatar image

92 1 3 3

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jun 08, 2012 at 11:41 AM

Seen: 6063 times

Last Updated: Jun 14, 2012 at 06:32 AM

Copyright 2018 Redgate Software. Privacy Policy