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.
@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.
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.
answered Jun 11, 2012 at 12:22 PM
--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
answered Jun 14, 2012 at 06:32 AM