Menu
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?
428
Using SQL Server Management StudioRight-click a stored procedure and select Properties. In the Stored Procedure Properties -stored_procedure_name dialog box, under select a page, select Permissions. Use this page to add users or roles to the stored procedure and specify the permissions those users or roles have.
You right click sysadmin and click properties to get a list of sysadmins. You can do this for any role, and that's the easy way if you want to verify permissions.
SQL Server includes a very useful system function sys. fn_my_permissions to list all the permissions of a particular principal (user or login) and this system function will help you list all permissions of a principal on a specific database object (securable).
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.
61
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
