I don't have SQL Server Management Studio installed, but I need to create a login for a user. How do I do it?

You can send SQL commands direct to SQL Server using the SQLCMD program. You invoke this program in a Command Prompt window. The program is named sqlcmd.exe and is located somewhere in the subfolders under the Program Files folder that contains the SQL Server software. For example, with SQL Server 2012, you should find SQLCMD in

C:\Program Files\Microsoft SQL Server\110\tools\binn\sqlcmd.exe

To enter these commands, you must be logged in under a login name that has SQL Server Administrator permissions.

To get started, open a Command Prompt window and CD to the folder that contains the SQLCMD program. To invoke the program, use the command line

sqlcmd -S lpc:servername\instancename

where servername\instancename identifies the instance of SQL Server that you want to work with. For example, you might enter

sqlcmd -S lpc:MYCOMPUTER\SQLEXPRESS

if you want to work with the instance MYCOMPUTER\SQLEXPRESS.

Once you have entered this command line, SQLCMD starts up. It will indicate that it's ready to receive input by prompting you with 1>. You can then enter commands to create appropriate login and user records (or to modify SQL Server in any other way).

As a concrete example, suppose that you want to give permissions on the MainBoss database to the local "Network Service" account. To do that, enter the following. (Note that the text below shows the prompts from SQLCMD as well as the commands you actually have to type in. Also note that in place of databasename, you should enter the actual name of the MainBoss database.)

1> CREATE LOGIN [NT AUTHORITY\Network Service] from windows
2> GO
1> USE databasename
2> GO
1> CREATE USER [Network Service] for login [NT AUTHORITY\Network Service] with default_schema = dbo;
2> GO
1> exec sp_addrolemember @rolename = [MainBoss], @membername = [Network Service]
2> GO
1> EXIT

A similar sequence can be used for any user name. Just replace the strings NT AUTHORITY\Network Service with the (domain-qualified) user name, and Network Service with the user name (without the domain qualifier).

If you want to give a MainBoss administrator the ability to create MainBoss users and you turn on the option to let MainBoss manage SQL Server users, you must give the administrator SQL Server System Administrator privileges. To do this, use the commands

1> ALTER SERVER ROLE [sysadmin] add member [domain\username]
2> GO

where domain\username is the user's domain-qualified user name.

Back to FAQ index