x

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

21bhuvan gravatar image

21bhuvan
0 1 2 3

(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.

CREATE LOGIN [DOMAIN\username] FROM WINDOWS
GO
EXEC sp_addsrvrolemember 'DOMAIN\username', 'sysadmin'
GO
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

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

(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

ASHOK 1 gravatar image

ASHOK 1
92 1 1 2

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x86
x31
x8

asked: Jun 08, 2012 at 11:41 AM

Seen: 3710 times

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