Security – cannot add a user to sysadmin role in SQL Server

Securitysql serversql-server-2008

I am using SQL Server 2008 Management Studio. The current logon account belongs to machine local administrator group. I am using Windows Integrated Security mode in SQL Server 2008.

My issue is, after log into SQL Server Management Studio, I select my login name under Security/Logins, then select Server Roles Tab, then select the last item — sysadmin to make myself belong to this group/role, but it says I do not have enough permission. Any ideas what is wrong? I think local administrator should be able to do anything. 🙂

Best Answer

The login of the session where you try to set permissions must have permissions.

So, the login you are using is a local admin? This means the local admin group does not have enough rights.

The GUI will run this too sp_addsrvrolemember. Although this says

Requires membership in the role to which the new member is being added.

So, the local admin group is not a member of sysadmin.

Edit:

To solve: Use a login that has rights. you can find one using sp_helpsrvrolemember

Related Topic