Home / server / Questions / 338
In Process
kokos
Asked: 2009-05-01 02:40:01 +0800 CST

Adding an administrator user to SQL Server 2008

  • 772

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

sql-server-2008 users

5 Answers

  1. 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];
    • 29

  2. 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?

    • 12

  3. 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.

    • 10

  4. 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

    CREATE LOGIN [<DOMAIN>\<user>] FROM WINDOWS
go
exec sp_addsrvrolemember @loginame='<DOMAIN>\<user>', @rolename='sysadmin'
go
    • Stop the SQL Server instance (Service Tab)
    • Start the SQL Server instance (Service Tab, remove the start parameter -m)
    • 5

  5. 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.
    • 3