Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question
add comment

4 Answers

If you're doing it via T-SQL:

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

CREATE LOGIN [Domain\User] FROM WINDOWS;

or

CREATE LOGIN [Domain\Group] FROM WINDOWS;

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:

GRANT CONTROL SERVER TO [LoginName];
share|improve this answer
add comment

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),

sqlserver.exe -m 

and then do as K. Brian Kelley said.

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
add comment

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
add comment

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?

Answers:

  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
add comment

Your Answer

 
discard

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.