How to Grant Myself Admin Access to a Local SQL Server Instance

How do I grant myself admin access to a local SQL Server instance?

Yes - it appears you forgot to add yourself to the sysadmin role when installing SQL Server. If you are a local administrator on your machine, this blog post can help you use SQLCMD to get your account into the SQL Server sysadmin group without having to reinstall. It's a bit of a security hole in SQL Server, if you ask me, but it'll help you out in this case.

How to get admin rights in SQL Server Management Studio?

Log on to your computer as the Local Administrator account. By default, that should be a sysadmin role in MSSQL.

Using SSMS, connect to your MSSQL instance using integrated authentication. You are now a sysadmin. As a sysadmin, you can now add your normal user account to the sysadmin role:

EXEC sp_addsrvrolemember @loginame = 'PC_OR_DOMAIN\loginname', @rolename = 'sysadmin'

If you use SSPI (aka Integrated Authentication, aka not a different username and password when you start up SSMS) then just use your Windows login as the loginame. If you use Sql Server Authentication (aka, a username and password) then use the username as loginame. If you use any other loginame, an account will be created as well.

There's certainly a way to do it within the GUI - but I don't have it handy ATM to tell you how. I think it's under Security -> Logins -> Properties and some checkboxes for the various server roles.

Edit: Enabling the local admin account on Vista
Also, if you are a local Administrator (your user account is listed under Local Administrators group) then - by default - you are a sysadmin. It may be worth double checking the members of the sysadmin role (SQL) and the Local Admins group (Vista).

Edit2: Turns out, SQL 2008 does not add BUILTIN\Administators any more. In that case, you need to check what you did add. That should be available via the Logins node. There is a note that you can be locked out of MSSQL Admin if you don't choose a sysadmin login. If that's the case, I'd reinstall. You can save your databases by stopping MSSQL and copying the *.MDF and *.LDF files. After reinstallation, copy them back and use sp_attach_db to reattach them.

Windows Administrator doesn't have permission on SQL Server

If your DBA didn't provision local Administrators as sysadmin role members. you can do that after installation if you are a local admin. From an administrator command prompt:

1) Start SQL Server in single-user mode

• default instance: NET START MSSQLSERVER /m
• named instance: NET START MSSQL$YourInstanceName /m

2) Run SQLCMD to add provision additional sysadmin role members:

• default instance: SQLCMD -E -Q "sp_addsrvrolemember N'BUILTIN\Administrators', N'sysadmin';"
• named instance: SQLCMD -E -S.\YourInstanceName -Q "sp_addsrvrolemember N'BUILTIN\Administrators', N'sysadmin';"

3) Restart SQL Server as normal and you can now connect as a sysadmin

Note you can specify any Windows user or group as sysadmin role members in step 2.

Use ALTER SERVER ROLE sysadmin ADD MEMBER [BUILTIN\Administrators]; instead of sp_addsrvrolemember in SQL Server 2012 and later versions. The sp_addsrvrolemember proc was deprecated in in SQL Server 2012 in favor of DCL syntax.

How to add new sysadmin account when no sysadmin accounts exist

The details presented in this answer are correct - however other answers do provide workarounds that exist to allow SQL Server to be started in a special mode that allows the server administrator to access SQL Server as a member of the sysadmin role.

---

Even the most uber-admin type of connection option Dedicated Administrator Connection (DAC), which can only be used as a local connection, and lets you undo all kinds of evil, still requires login credentials. So I don't think there's an official way to do this.

The quickest way to resurrect this system may be to shut down SQL Server, copy the user database files somewhere safe, uninstall, reinstall (making sure to service pack up to at least the level you were previously at), copy the files back and attach the databases. (Not sure the copying out/back is required, but just to be safe...).

You'll still need to manually recover server level objects (e.g. logins)

---

Related Topics