Removed all members from SysAdmin

[pkkell0]

Aloha

I had a faulty script that unfortunately removed all members from the SysAdmin role. Unfortunately, I cannot reinstall the instance and I only have short windows of opportunity for downtime.

I understand if I can get the database into single user mode and log in I can add a new local account to the database with the sysadmin role, however I can't seem to get logged into the single user mode. It keeps saying "Login failed for user '<MYUSERACCOUNT>'. Reason: Server is in single user mode. Only one administrator can connect at this time.

I have the SQL agent shut down, I am not running enterprise manager and, as far as I can tell, there are no other admins connecting to the system. I know with newer SQL versions one can limit the single user mode to only accept SQLCMD. Of course, 2000 doesn't have SQLCMD but has OSQL. Any thoughts on how I can limit the access during this maintenance? I've even tried to remove both named pipes and TCP/IP from the accepted connection types but that didn't seem to make a difference.

Thoughts? Please?

[skhanal]

Did you try login using sa?

[rmiao]

Did you try connect with osql? Ensure no one opens enterprise manager.

[rmiao]

Another thing to try, logon windows with sql service account then connect in osql.