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.
- Drop to a command line and navigate to
C:Program FilesMicrosoft SQL Server80ToolsBinn(or wherever your tools are installed to, obviously).
- Type (case-sensitive):
This will log you in using your current logged in credentials
- Type (two lines):
EXEC sp_addlogin 'Username', 'Password'
Replacing Username and Password as needed
- 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'
Replacing Username as needed
- Alternatively you can run this if you want to use a custom role (two lines):
EXEC sp_addrolemember 'role name', 'Username'
For some reason the two role sproc’s parameters are in different orders
With this you should be able to login remotely.