Do you know how to add user as SysAdmin without using SQL Management Studio? The following is the brief tutorial about how to add a user as sysadmin without using SSMS. Let’s get started!
Get Started!
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 you can share to others! Thank you
Javier is Content Specialist and also .NET developer. He writes helpful guides and articles, assist with other marketing and .NET community work
