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];
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. Oct 27, 2010 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.
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
)
-
For me the key hint of this was the
go
commands; I'm used to SSMS so didn't think to usego
to submit batch from command line client. Feb 16, 2018 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.