How do you add an administrator user to SQL Server 2008?
5 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];
- 9,004
- 31
- 33
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?
-
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.
- 12,994
- 20
- 74
- 92
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
)
- 261
- 3
- 4
-
For me the key hint of this was the `go` commands; I'm used to SSMS so didn't think to use `go` to submit batch from command line client. – Pasi Savolainen Feb 16 '18 at 11:28
Hmm, there are two different things this could mean.
- How do I create a new user account that has administrative rights?
- I already have a windows user that is an administrator, how do I add that user to SQL?
Answers:
- In SQL Management Studio, create a new login and add it to the sysadmin server role.
- 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,696
- 4
- 28
- 58