How to get "admin rights" in SQL Server Management Studio?

Question

I'm using SQL Management Studio 2008 Express as a graphic interface to my local SQL Server 2008 Express instance, both of which I have locally only as a test and developement interface for my web projects.

I have recently grown more confident in SQL coding, and started to use some more complicated sql stuff - my latest field of exploration being triggers. However, to my great surprise I don't seem to have admin rights on my server instance, which means I can't do simple things like debugging or changing access rights.

When I try to start debugging I get a permission denied message, and I have also seen it when I try to manage permission rules to different databases. I suspect this has something to do with some option I chose when I installed the server, but as I already have several databases set up and filled with data, I have no desire to uninstall and reinstall the whole thing.

Is there any way to change which user account is the master admin of the server instance? If not, is there any other way to get permission rights to for example debug my triggers?

Answer 1

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.