Recently I have worked on a thick client application. The application uses SQL Server as it’s database server.

While debugging an issue on the test server, I wanted to add one windows user as a sysadmin. I knew how to add a user as sysadmin using SQL Server Management Studio. But there was no SSMS installed on that machine.

Background

There is one stored procedure which helps to add a user as with specified role. The stored procedure name is – sp_addsrvrolemember

With every SQL installation, there is a command line utility – SQLCMD

The SQLCMD utility has a lot of options. You can have a look at this MSDN documentation link.

Using SQLCMD, we can execute a SQL statement or a query. We need to provide the SQL Server instance (-S option), the query (-q option) and that’s it.

Tip

sqlcmd -S .\sql-instance -q "exec sp_addsrvrolemember
'machin-name\windows-user', 'sysadmin'"

The above sqlcmd command will try to add user with name “windows-user” as “sysadmin” in the named SQL Server instance with name “sql-instance“. You can run this command on the regular command prompt.

Current windows user’s credentials would be used to execute this SQL statement as we have not specified the SQL credentials explicitly in the command

Please note that only an existing “sysadmin” can add new users as “sysadmin”. Hence if the current windows logged in user does not have “sysadmin” privilege, the above statement would fail.

Also, if your machine is domain joined, only machine name (without any domain name suffix) is required to be specified in the query.

If this tip helped you, please comment and let me know. That would really encourage me to add more such short tips.