Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question

migrated from stackoverflow.com Aug 1 '09 at 12:23

This question came from our site for professional and enthusiast programmers.

    
this is what microsoft supplies about this problem: msdn.microsoft.com/en-us/library/dd207004.aspx i can recommend this script: archive.msdn.microsoft.com/addselftosqlsysadmin/Release/… –  user98941 Oct 25 '11 at 18:27
add comment

6 Answers

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

share|improve this answer
    
What is your advice to solve my problem? –  George2 Aug 1 '09 at 12:17
    
Not sure if any walk-arounds or solutions without reinstalling all SQL Server 2008 again! :-( –  George2 Aug 1 '09 at 12:21
    
"Use a login that has rights" -- how can I get a list of logins which has enough permission? –  George2 Aug 1 '09 at 12:59
    
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? –  George2 Aug 1 '09 at 13:12
1  
It's a reserved SQL login, not a Windows user. On my test SQL 2008 install, the account that installed SQL Server is listed. –  gbn Aug 1 '09 at 13:51
show 1 more comment

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.

share|improve this answer
add comment

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.

share|improve this answer
    
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. –  ldsandon Oct 14 '10 at 8:03
add comment

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.
share|improve this answer
    
Hi Remus, I am confused that during installation process, I do not setup any passowrd for sa, is there a default password? –  George2 Aug 2 '09 at 9:44
    
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? –  George2 Aug 2 '09 at 9:47
    
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. –  Remus Rusanu Aug 2 '09 at 16:40
    
"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. –  michielvoo Mar 23 '10 at 13:38
add comment

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.

share|improve this answer
add comment

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

share|improve this answer
add comment

protected by voretaq7 Nov 9 '12 at 21:06

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.