cannot add a user to sysadmin role in SQL Server

Question

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. :-)

Answer 1

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

Answer 2

In an otherwise good article Troubleshooting: Connecting to SQL Server When System Administrators Are Locked Out, the author tells us that all we have to do to regain control of our SQL Server is to

"Start the instance of SQL Server in single-user mode by using either the -m or -f options. Any member of the computer's local Administrators group can then connect to the instance of SQL Server as a member of the sysadmin fixed server role."

Unfortunately, what it means to start in single-user mode is not an intuitive matter. Also, at least in my experience, membership in my computer's local Administrator's group did not grant sysadmin status to my "user" account.

This story had begun when to address a problem of membership in my domain by a non-DC server (a consequence of changing ISP), I rebuilt the DC into Windows Server 2008 R2 from 2008. This solved a couple minor problems but did not address the question of membership by the errant server. It took a fix suggested in ServerFault to accomplish that (in my case, it was a matter of resetting winsock and tcpip).

SQL Server 2008 resides on that second server, now a member of the domain. Here's the problem. As someone points out on ServerFault, it is common to push the "let the current user become as sysadmin," when installing Server 2008. Common also to not consider anyone else. Because that single user identity was a domain member of a domain that no longer existed, no one had permission to administer Sql Server.

I logged on as the local server administrator that had been around when Sql Server was installed, but although this allowed me access to the Management Studio, I quickly discovered that BUILTIN\Administrators had only the "public" server role.

After much research and experimentation, I happened upon an article giving the specifics of single-user logon http://technet.microsoft.com/en-us/library/ms180965.aspx:

C:\>cd \Program Files\Microsoft SQL Server\MSSQL10_50.1\MSSQL\Binn 
C:\...>sqlservr.exe -m

To prepare to take this step, you must shut down Sql Server (which runs by default in most cases). Use SQL Configuration Manager to stop "Sql Server."

Then, in a command prompt, navigate to that program file location (or equivalent on your machine), and issue the "sqlservr.exe -m" command. If you see a stream of activity logged in your command prompt, you're being successful. If it fails to start, then you probably have SQL Server running already. Shut it down.

Allow the single-user instance to attach its databases. When log activity has ceased, open Management Studio. You will be automatically in single-user mode, and therefore whatever account you represent will be a sysadmin. Use that power to adjust Security logins and server roles.

In my case, I had to recreate the domain accounts in the new domain, then delete their namesakes in SQL Server, and reconstruct them (due to the SID/GUID situation), reassigning permissions to particular databases as necessary.

Answer 3

SQL Server 2008 no longer adds the BUILTIN\Administrators as members of the sysadmin fixed server role

That's true, BUT in that case you can recover from that situation starting the SQL Server Service in Single User Mode (emergency mode), and any local admin will be able to logon.

See MSDN.

Answer 4

By default SQL adds the group BUILTIN\Administrators to the sysadmin role, meaning that any local NT administrator is automatically a SQL sysadmin. On Vista, because of the UAC, you must run in 'as Administrator' mode to benefit from this privilege.

• If you are on Vista, run the client tool (SSMS) 'as administrator' and then try to add yourself as a sysadmin.
• If the BUILTIN\Administrators group was removed from sysadmin role accidentally, then you must login with another sysadmin login. If ther eis no other sysadmin login, you must login with SQL authentication as sa and use the sa password that was set during setup. Once logged in as a member of sysadmin, ad BUILTIN\Admisnitrators back to sysadmin role. If SQL logins are disabled then congratulation, you just lock yourself out of the SQL installation.
• If the BUILTIN\Administrators group was removed intentionally from the sysadmin role (as per KB932881) then you are trying to hack into system you don't have permission to.

Answer 5

By default SQL Server 2008 no longer adds the BUILTIN\Administrators as members of the sysadmin fixed server role. When you go through the installer it asks what account or accounts should have sysadmin rights. There's a button which basically says "make me a sysadmin" which will add the account of the user doing the install as a sysadmin.

If they didn't add any other users as sysadmins then only that user is currently a sysamdin. You'll need to have that person log into the SQL Server and grant the DBAs sysadmin rights.

Answer 6

I mulled over the same issue after a new installation until I registered the server, on SQL Server 2008, then everything went as it should