SQL Server 2000 w/o the GUI

Okay, not totally, but this process starts without the GUI. I had two SQL servers, one without the GUI tools installed. In a domain environment this is fine because you can just snap to the machine using your domain credentials. If you don’t have a domain and forgot to create a local account (or can’t remember the sa password) before disconnecting from the domain, however, you’re in for a little trouble. Okay, its not that bad really. SQL Server 2000 installs a command line tool that you can use to create a new user and elevate that user as needed. In my case, I wanted to manage both SQL Servers from the one SQL Server with the GUI tools installed. Luckily I have mixed-mode authentication installed so I didn’t need to try to get the sa password.

  1. Drop to a command line and navigate to C:Program FilesMicrosoft SQL Server80ToolsBinn (or wherever your tools are installed to, obviously).
  2. Type (case-sensitive):
    osql -E
    This will log you in using your current logged in credentials
  3. Type (two lines):
    EXEC sp_addlogin 'Username', 'Password'
    GO
    Replacing Username and Password as needed
  4. In my case, I just wanted the account to have total control since I was going to be managing this remotely so I ran (two lines):
    EXEC sp_addsrvrolemember 'Username', 'sysadmin'
    GO
    Replacing Username as needed
  5. Alternatively you can run this if you want to use a custom role (two lines):
    EXEC sp_addrolemember 'role name', 'Username'
    GO
    For some reason the two role sproc’s parameters are in different orders

With this you should be able to login remotely.