question

suesh avatar image
suesh asked

How to add user account to sql server without connecting to management studio?

Hi, How to add user account(sysadmin user) to sql server without connecting to sql server management studio?
account
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Please vote to help the site run. Indicate helpful answers by clicking on the thumbs up, and if an answer solved your problem, show this by clicking on the check mark.
0 Likes 0 ·
VishalhSingh avatar image
VishalhSingh answered
Check these system stored procedures: sp_addlogin
sp_grantlogin You will find more about them [here][1] [1]: http://technet.microsoft.com/en-us/library/ms173768(SQL.110).aspx
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

What prevents you changing the password for this account?
1 Like 1 ·
Hi, I am saying without connecting to sql server management studio. Because we have one service account and sql services are running under that account, but we forgot that password so we are unable to connect. thats the reason we want to add the other account to sql server. :)
0 Likes 0 ·
I say just reset that service account password. Change the connection strings if you must. But you should not be connecting to SSMS via the Service Account - that is just bad practice.
0 Likes 0 ·
sdoubleday avatar image
sdoubleday answered
SQLAuthority has instructions on how connect to a SQL Server instance using SSMS by first logging into the Windows server itself as an administrator: http://blog.sqlauthority.com/2014/05/21/sql-server-reset-sa-password-sql-in-sixty-seconds-066/
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

SQLGoooRooo avatar image
SQLGoooRooo answered
you can run queries from the command line. this way you don't ever have to get into management studio. http://technet.microsoft.com/en-us/library/ms165702(v=sql.105).aspx
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

For this you need first connect to SQL server and than write a command (for example sqlcmd -S server1\SQLExpress -U SqlUserAccount -P SqlPassword ). He can't connect to SQL server because he forget his password. So this is not the right answer.
0 Likes 0 ·
He actually says he cannot connect via the Service Account because they forgot the service account password. They have said nothing about any other account or his account.
0 Likes 0 ·
VishalhSingh avatar image
VishalhSingh answered
The same system SPs you can execute using SQLCmd (command line interface for SQL Server).
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Sule avatar image
Sule answered
Start SQL service in single user mode (-m), restart service (be carefull because SQL Agent service must be disabled) and connect to SQL server with admin (on server) account. When you connecting to SQL Server via SSMS, start SSMS as administrator (right click, run as administrator). link: http://msdn.microsoft.com/en-us/library/dd207004(v=sql.110).aspx
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.