How to Add user to Sql Server Express 2008 without Management Studio

Question

I had installed SQL Server 2008 R2 "SQL Server Database Engine" in virtual machine and I want to connect to it over odbc from other computer (Linux).

I hadn't installed management studio as I wanted minimal possible installation (it is mostly for very basic testing purposes not for real work).

I hadn't installed Management Studio and I don't want to install it.

During installation procedure I provided a user and password for connection however, looks like thing went wrong and I can't login using this user.

Situation:

1. I have installed "Database Engine" and configured TCP connection
2. I can connect to it using isql but it reports that user does not exist.
3. I see in Event Log (of Windows) complains that user does not exist.
4. I've tried to run import tool and managed to login using XP user on same PC and could run simple queries, however this tool does not allow me to run a statement so I can't run CREATE LOGIN user-name WITH PASSWORD='secret'.

How can I connect to this database and add new users?

Basically at this point I have two options:

1. Reinstall database, but:

   I don't want to and nothing promises that this time it would work and create a user.

2. Install management studio I don't want it is huge install for virtual machine.

Question:

Is there any simple command line tool to access DB in place just to run several statements using XP authentication without a huge Management studio tool?

Solution Summary:

1. Use sqlcmd -Q "create login ..." to run sql statements I need. sqlcmd.exe is installed with "SQL Server Database Engine"
2. My user wasn't defined correctly during the installation because MS SQL didn't like my password I had given and it didn't alert me on the problem...

Solution 1

sqlcmd will allow you to run commands against the server using Windows or SQL Authentication. If something's gone wrong and you've ended up with no windows accounts granted rights to the server then you can start SQL Serve in single user mode to get access and create new accounts.

Solution 2

On my system, which is SQL 2008 (not R2), sqlcmd.exe is located in "C:\Program Files\Microsoft SQL Server\100\Tools\Binn". If you are using R2, it might be a slightly different path. You could try looking for "osql.exe". osql.exe works mostly the same as sqlcmd but predates sqlcmd.exe and is not quite as full-featured. On my system, osql.exe is in the same Binn directory as sqlcmd.exe.

Even when I intend to administer the server remotely, I find that installing the tools on the server, along with the SQL services, causes much less pain than not installing them. And I've been using SQL Server for 12 years. Certain things (like replication) are very difficult to do by manually writing Transact-sql because the stored procedure calls can be very complex, and often some seemingly trivial utility that you wind up needing is missing.

Comments

[Admin]

It seems silly to not use the purpose built tools that come packaged with the product just to save a few hundred MB.

[Admin]

@Dan not in case when you have 1-2GB of virtual disk in virtual machine. Especially when you don't need them.

[Admin]

You do know you don't HAVE TO install SSMS on that particular VM to access the DB that resides there?

[Admin]

@GregD - but you need to install it somewhere, and basically this is the only Windows machine I have, rest are Linuxes, unless MS released SSMS for Linux :-)

[Admin]

@Artyom: As someone who manages numerous SQL Server instances from Sql 2000 - 2008, creating a VM to throw XP (at least) just to run SSMS makes sense. SSMS is your friend..not the enemy you're making it out to be..

[Admin]

@GregD I believe it is not enemy, in fact I know this tool and work with it in other environments. However the fact is I want smallest possible MS SQL Server installation on small VM to run basic tests. So I want to keep things small. I don't need any fancy stuff. I don't need to manage many servers I need simple client to execute a simple command. Does it make sense to install huge application to run few SQL commands? At least in the world I come from (Linux) it doesn't. So I home sqlcmd solves the problem for me and I'll accept the answer as soon as I get to the computer.

[Admin]

@Artyom: I see where you're coming from now. Sometimes making the move to another OS involves thinking about things in a different way. It's hard for me to make the jump to *nix because I'm a Windows admin for instance. While you can install/manage SQL server from a command line, it's probably not the norm. Good luck with the way you want to use it.

[Admin]

What's the problem of adding another virtual drive?

[Admin]

@Artyom: I'm not sure what experience you have with SQL Server. IMHO you are doing yourself a disservice by avoiding SSMS simply because it's 150MB install...disk space is cheap. If you are only adding a user to SQL then sqlcmd can be the right tool for the job..but get any more sophisticated than that..SSMS is the right tool for the job. If this is all you're doing with SQL, then I question your use of it. There are other database products that have a much smaller footprint then SQL.

[Admin]

@GregD - I need MS SQL to do some tests against this Database. I have no more interests to use it then that. I'm really happy with Sqlite3, PostgreSQL and MySQL databases. I really prefer them because they are cross platform, powerful, share common SQL syntax (like LIMIT and not TOP) and fit all my needs very well. But I do need to do some basic test against MS SQL and finally I'm going to connect it over ODBC from linux run some queries, insert some. Thats it. No more. This what I'm talking about from beginning.

[Artyom]

is sqlcmd included in "SQL Server Database Engine" or it is part of management studio? Because I searched for all exes in installation and hadn't found this one (or I missed it)

[DanC]

@Artyom - for me, sqlcmd is {program files}\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE

[Artyom]

Thanks. I'll give it a try as soon as I get to this computer.

[Artyom]

Thanks you very much, it was on "C:\" even I installed MS SQL on "E:" ... This really helped me.

[Chris]

I had to go in to single user mode myself. Adding ;-m to the end of the service startup parameters and restarting did it. Make sure to return it to normal once you have your account added.