cannot add a user to sysadmin role in SQL Server

Hello everyone,

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

thanks in advance, George

From serverfault George2

• 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

  George2 : What is your advice to solve my problem?

  George2 : Not sure if any walk-arounds or solutions without reinstalling all SQL Server 2008 again! :-(

  George2 : "Use a login that has rights" -- how can I get a list of logins which has enough permission?

  George2 : Thanks, I find the only member listed from EXEC sp_helpsrvrolemember 'sysadmin' is called sa, is it a Windows User? I cannot find such a user from Windows Users list. Any ideas?

  gbn : It's a reserved SQL login, not a Windows user. On my test SQL 2008 install, the account that installed SQL Server is listed.

  George2 : Thanks gbn, do you mean sa is not a Windows user. But why it is listed? I think if I am using Windows Integrated Authentication mode, then all listed users should be Windows users or groups. Any comments?
  From gbn

• 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.
  George2 : Hi Remus, I am confused that during installation process, I do not setup any passowrd for sa, is there a default password?

  George2 : Another confusion is, from running store procedure sp_helpsrvrolemember to find all sysadmin role users, only sa is listed, no other users from local machine adminsitrator group, any ideas why?

  Remus Rusanu : There is no default password for sa. sa's password is set during setup, if mixed authentication is enabled. You are going to have to ask administrator of your database to grant you access if you need.

  michielvoo : "If SQL logins are disabled then congratulation, you just lock yourself out of the SQL installation." In that case, see Jordi's answer below to start SQL Server in Single User Mode.
  From Remus Rusanu

• 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.

  From mrdenny

• 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

  From $pageUsers[$post.author].name

• "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.

  http://msdn.microsoft.com/en-us/library/ms188236.aspx

  Regards.

  From $pageUsers[$post.author].name

• In an otherwise good article--"Troubleshooting: Connecting to SQL Server When System Administrators Are Locked Out" [http://msdn.microsoft.com/en-us/library/dd207004.aspx], 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.

  ldsandon : There's also another way to set parameters: open service, manager, select the service and then properties from the right-click menu. In the general tab there is a "Start parameters" edit. Type the needed parameters there. Then **start the service using the start button in that page**, the parameters set there are not permanent and valid only when the service is started from the Properties dialog.
  From Michael Broschat