Results 1 to 4 of 4

Thread: Removed all members from SysAdmin

  1. #1
    Join Date
    Jun 2014
    Posts
    1

    Removed all members from SysAdmin

    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?

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932

  3. #3
    Join Date
    Sep 2002
    Posts
    5,938
    Did you try connect with osql? Ensure no one opens enterprise manager.

  4. #4
    Join Date
    Sep 2002
    Posts
    5,938
    Another thing to try, logon windows with sql service account then connect in osql.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •