Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

How do you add an administrator user to SQL Server 2008?

share|improve this question

If you're doing it via T-SQL:

Granting a Windows Login the ability to connect to SQL Server:




If SQL Server-based login:

CREATE LOGIN [LoginName] WITH PASSWORD = 'SomePassword';

After that, you can do one of two things. You can add it to the membership of the sysadmin fixed server role using sp_addsrvrolemember:

EXEC sp_addsrvrolemember 'LoginName', 'sysadmin';

or you can grant the login CONTROL SERVER permissions:

share|improve this answer

Before that I think we need to be able to login to SQL Server first. I have experienced being a server administrator, but I couldn't login since SQL Server was installed by a Domain Admin account.

So you might need to start SQL Server with command-line option -m (single user mode),

sqlservr.exe -m 

and then do as K. Brian Kelley said, or connect via management studio, as suggested by Jeff (the login will succeed)

More step-by-step instructions can be found in SQL Server 2008: Forgot to add an administrator account?

share|improve this answer
That's exactly what I needed, thanks :) – Mark Pim Sep 9 '10 at 9:50
Thanks for this. I've installed SQL Server 2008 before, for whatever reason I was unable to login with SSMS. Following your suggestion was exactly what I needed to do to fix the sysadmin account login. – Jim Schubert Oct 27 '10 at 22:38

For completeness, the GUI method

From SQL Server Management Studio, under the Security / Logins folder for the database, right click Logins and select New Login:

Be sure to use the full domain\username format in the Login Name field, and check the Server Roles list to make sure the user gets the roles you want.

share|improve this answer

Hmm, there are two different things this could mean.

  1. How do I create a new user account that has administrative rights?
  2. I already have a windows user that is an administrator, how do I add that user to SQL?


  1. In SQL Management Studio, create a new login and add it to the sysadmin server role.
  2. In SQL Management Studio, create a login connected to the Windows account or a group that it's a member of. You might well find BUILTIN\Administrators is already there.
share|improve this answer

Since there was no complete answer for me for solving this Problem, here is what i did, for SQLServer 2014

  • Stop the SQL Server instance (Service Tab)
  • Start the SQL Server instance (Service Tab, use the start parameter -m)
  • Open a command sql shell (as admin): sqlcmd -S <instance name> -E

Type the following in the shell

exec sp_addsrvrolemember @loginame='<DOMAIN>\<user>', @rolename='sysadmin'
  • Stop the SQL Server instance (Service Tab)
  • Start the SQL Server instance (Service Tab, remove the start parameter -m)
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.