No users with SQL admin rights

Question

This is a local SQL2005 on my WS for DEV. 
I think I ran a script that wiped out almost all user ids, and took away my admin rights, (I know, what an idiot)
I have been able to regain access and get logged in.  I see only 2 logins (sa and my lanid) neither of which have sysadmin rights, and everything I attempt which should change any rights is denied!!!!    ALSO sa is disabled.

Is there anything I can do to increase my rights to sysadmin?

Answer 1

Hi ,

Is your sql server configured with mixed mode authentication ? Are you sure you deleted the logins or just reduced the access level for yourself ?
Try to create a WINDOWS administrator login and login with that windows id and try to connect to sql server with windows authentication . Do this and let us know what is happening ?
If that doesnt work out , we will have to look at the option for rebuilding master.

---

Thanks, Leks

Answer 2

The SQL server is confiured for Windows Authentication only,
While I am able to see that info using SQL managment studio, I am not able to make any modifications.
I also have a second instance \sqlexpress on this same WS.  Can I use that in any way ??

Answer 3

Have you tried this ?

Try to create a WINDOWS administrator login and login with that windows id and try to connect to sql server with windows authentication

---

Thanks, Leks

Answer 4

One of the first things I tried, but LAN group policies prevent local user login.

can I use the Master or the MSDB database from the other instance ??

Answer 5

One of the first things I tried, but LAN group policies prevent local user login.

can I use the Master or the MSDB database from the other instance ??

You can't. You could restore your system databases if you have backup. Easiest would be re-installing SQL Server instance and re-attach all user databases back. You can stop that SQL Server instance and copy all data/log files to another location.

Regards,
Akim

Answer 6

You cannot use master / msdb databases from other instances . And moreover the express instace cannot help you  with this problem.
If you dont have any other options around , try reading about rebuilding master database. There you have option of resetting sa account.

---

Thanks, Leks

Answer 7

There's a backdoor:

Start SQL Server in single user mode (the -m switch, read in Booke Online and on the net about it). This will let local administrators in using windows authentication as sysadmins in SQL Server. Then you can create logins or whatever it is you want to do. Then just start SQL Servernormally again.

---

Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi

Answer 8

... it might just be that the option to use is -f (and not -m), as per Torsten's reply to another post here. I'm not sure whether -m work as well, but -f should work.

---

Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi

Answer 9

-f seems to be a great start, but from that point I am still lost.  sa is disabled and I am not sure of the password even if it were enabled, so I connect through managment studio with my LANid and still have no rights to do anything.
Then I stoped the sql server and started it in normal mode and still not able to do any administrative tasks.  I've been reading the books online, and some things on MSDN technet, but have found nothing that directs me where to go from here...

Can someone provide a bit more direction?

Answer 10

While SQL Server is started using -f, you can:
Create a new login using CREATE LOGIN
Make this new login a sysadmin using sp_addsrvrolemember

---

Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi

Answer 11

can this be done from a command line in the DOS window ?  When I try to connect using management studio I receive an error message telling me that server is in Single user mode and only one admin can be connected....  I'm searching books online for an answer, but not sure where I'll find one...

Answer 12

I figured it out -
I was unable to maintain a connection and an open query to the single-user server and I am unsure of how to open a query to a server that I am not conencted to.  So I opened a query, but it was not connected, I then closed my connection to the server and opened the properties of the disconneted query, and connected to the server.  I was then able to run the sp_addsrvrolemember  on my own login id, stop the single-user session and restart the server....
Thank you all for you time and assistance