Re: Taking Control of SQL Server Database

Tech-Archive recommends: Speed Up your PC by fixing your registry

That works. Thanks a lot Rick.

Regards,
Nigel


"Rick Byham, (MSFT)" <rickbyh@xxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:832DB8F2-7A31-4492-95C4-2B2A73547700@xxxxxxxxxxxxxxxx
As a Enterprise Admin, you may or may not be in the local administrators group on the computer. If so, in SQL Server 2005 the local administrators group has sysadmin permissions to the SQL Server. Unless someone has removed it to tighten security. So attempt to connect with your login. If the OS is Windows Server 2008 or Windows Vista, start SQL Server Management Studio using the Run as Administrator option so Windows will pass your admin credentials to SQL Server.

If someone has removed the local administrators from the sysadmin role by removing BUILTIN\ADMINISTRATORS from the logins, then this will fail. In that case, restart the Database Engine in single user mode. Then SQL Server will honor the local administrators group. See the Books Online topic "Troubleshooting: Connecting to SQL
Server When System Administrators Are Locked Out" http://msdn.microsoft.com/en-us/library/dd207004.aspx
This is a SQL Server 2008 topic but someone recently told me it works on SQL Server 2005 also.
--
Rick Byham (MSFT), SQL Server Books Online
This posting is provided "AS IS" with no warranties, and confers no rights.

"Nigel Ainscoe" <discussions@xxxxxxxxxxxxx> wrote in message news:A10F7A25-80CF-43C7-B3FB-AE965719A6F0@xxxxxxxxxxxxxxxx
Hi,

I have a SQL Server 2005 database that I do not have sysadmin credentials
for, only Public, but I need to take control of it. I.e. give myself
sysadmin. I have Enterprise Admin credentials on the domain.

Anyone know what the best/quickest way of going about this?

TIA & HNY
Nigel Ainscoe

I have also posted this in sqlserver.programming as I was not sure which would be the better group for such a question. Apologies for cross posting.




.

Relevant Pages

  • RE: OpenDataSource "Unspecified Error" when connecting to local Excel
    ... Are there any another hidden permissions required for the OpenDataSource ... the file is on the local Sql Server. ... > Even if I add domain users to the server administrator role, ... > local administrators group on the Sql Server? ...
    (microsoft.public.sqlserver.connect)
  • Re: Taking Control of SQL Server Database
    ... As a Enterprise Admin, you may or may not be in the local administrators group on the computer. ... in SQL Server 2005 the local administrators group has sysadmin permissions to the SQL Server. ...
    (microsoft.public.sqlserver.setup)
  • Re: Adding Windows Admin to sa role after setup (MSSQL 2008)
    ... You can add domain logins, local logins, or the whole local administrators ... to SQL Server When System Administrators Are Locked Out ... Alain Quesnel ...
    (microsoft.public.sqlserver.setup)
  • Re: SQL server and SQL agent services logon account for Replication
    ... For push have the sql server agent run under the local administrators ... account which is in the sys_admin role. ... For pull, the sql server agent's on the subscriber must have rights to read ...
    (microsoft.public.sqlserver.replication)
  • Re: Is there an alternative to disabling windows authentication?
    ... Remove domain accounts from the Local Administrators. ... SQL Server, or installs an eval version of SQL Server and client tools ... so I want to protect my database by only allowing SQL Server ... The only logins are sa and several default ...
    (microsoft.public.sqlserver.security)