question

21bhuvan avatar image
21bhuvan asked

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.
loginusersa
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ASHOK 1 avatar image
ASHOK 1 answered
--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**
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Usman Butt avatar image
Usman Butt answered
@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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

raadee avatar image
raadee answered
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.