No "admin rights" in SQL 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.

So my question is:

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

Sql Server Configuration Manager.

That will allow you to set the instance account.

However what I think you might be after is changing the database owner to the user you connect to the database with. IF you're using SSPI then this will be your logged in user, or if you are using SQL authentication this will be the user connecting inside your connection string.

Additional:

If you are debugging on Windows XP or above, and you are not the administrator (local), then you will need to be placed into the "Debugging Users" group on that system. Otherwise accessing memory or attaching a debugger to a process you don't own is a big no no.

Answer 2

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.

Answer 3

See http://blogs.msdn.com/b/sqlexpress/archive/2010/02/23/how-to-take-ownership-of-your-local-sql-server-2008-express.aspx