5

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??

2
  • Do you have the ability to log in as the SA account? If so, do you get similar issues using SA? Aug 13, 2009 at 11:42
  • Didn't know the SA password! Re-installed and set SA password and added myself a login! Aug 13, 2009 at 11:53

5 Answers 5

1

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.

2
  • 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! Aug 13, 2009 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! Aug 13, 2009 at 11:54
20

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

2
  • Huzzah! You saved my evening.
    – crftr
    Oct 26, 2011 at 4:10
  • You can also use the Management Studio (started as admin) to add the user.
    – GaussZ
    Feb 27, 2012 at 15:45
3

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

2

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.

0

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .