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 have a remote PC with SQL Server 2008 setup and allowing remote connections.

I am an Admin user on the PC and have remotely connected to the SQL Server and I am trying to restore a database from backup but getting permissions denied for anything I try to do on the server!

I can add myself as a login to the SQL database but only with public server roles!

The server is setup to Log in as Local Account and I also have the SQL Server Browser running as well!

What am I doing wrong or missed??

share|improve this question
    
Do you have the ability to log in as the SA account? If so, do you get similar issues using SA? –  Mark Henderson Aug 13 '09 at 11:42
    
Didn't know the SA password! Re-installed and set SA password and added myself a login! –  Jan Zelezny Aug 13 '09 at 11:53

5 Answers 5

up vote 1 down vote accepted

Sounds like the account you are logging in as does not have the rights to perform these options. To give yourself these rights, you will need to log in as an administrator, or the SA account (if mixed mode is enabled).

When you're logged in as the administrator, then you should be able to assign yourself the appropriate privileges to be able to restore databases using your own user account.

share|improve this answer
1  
I am an admin on the SQL PC! Ended up re-installing and setting a password on mixed mode then adding myself as a login! –  Jan Zelezny Aug 13 '09 at 11:52
1  
Being an admin on the PC does not mean you are an admin over the SQL process - especially in an active directory! –  Mark Henderson Aug 13 '09 at 11:54

The solution, that actually worked for me published by the author Blipsolt:

  1. shut down SQL Server from services

  2. open cmd window (as admin) and run single-user mode as local admin with this command

    c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Binn\sqlservr.exe" -m -s SQLEXPRESS

  3. open another cmd window (as admin)

  4. open sqlcmd

    sqlcmd -S .\SQLEXPRESS

  5. Now add the sysadmin user:

    1. sp_addsrvrolemember 'domain\user', 'sysadmin'

    2. GO

  6. Ctrl+C the single-user mode from the first cmd window to kill SQL Server.

  7. Restart it from services the normal way.

  8. Log into Management Studio and the user you created should be listed under logins with the credential of "sysadmin."

share|improve this answer
    
Huzzah! You saved my evening. –  crftr Oct 26 '11 at 4:10
    
You can also use the Management Studio (started as admin) to add the user. –  GaussZ Feb 27 '12 at 15:45

SQL 2008 changed the default permissions check this MSDN

Likely this is what you encountered: "By default, the local Windows Group BUILTIN\Administrator is no longer included in the SQL Server sysadmin fixed server role on new SQL Server 2008 installations."

share|improve this answer

When you install SQL Server 2008 one of the screens asks what accounts should have Administrative Rights to the SQL Server Instance. If you don't select and accounts, then no accounts have admin rights as the BUILTIN\Administrators group is no longer a member of the sysadmin fixed server role by default.

share|improve this answer

For what it's worth, even though my domain account had local computer admin rights, I couldn't do anything useful once logged in since I didn't have enough rights (as the others in this thread stated in more detail). I had to log out and back in as the local computer administrator account, use Windows Auth to get into SQL, and add an SQL login for my domain user account and set the sysadmin role for it. Then I logged back in with my domain account and then I had full rights with Windows Auth to create databases, manage security, etc.

If you can, I suggest installing SQL 2008 w/ mixed mode and setting an SA password, at least until you get in and get it configured for your environment.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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