Local admin rights

  • If my Domain\User account is a member of the local administrators group on my SQL Server, do I have sa rights on that SQL Server?

    (We deleted the BuiltIn\Administrators login, and I've forgotten the sa password).

    But I'm trying to add a domain Windows group that is local admin on our SQL Servers, and I'm getting permission denied.

  • No. You have no rights at all.

    If you restart the SQL service in single user mode and connect as that single user, then and only then is a local administrator given sysadmin by default

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Right! ugh.

    Thanks Gail.

  • if you are local admin, you probably want to do a takeover of the server,

    then i believe you can at least add yourself via a dedicated admin connection and it will not even require a stop and start of the services.

    for example, from sqlcmd, on the server itself(not a remote connection!), you can run this command:

    -E = Trusted Connection,

    -A = Admin connection

    SQLCMD -S . -E -A

    if it's a named instance, add the name, ie


    with an local login + admin connection, you are in as a sysadmin, so you can add users via SQL command, like this:

    here' i'm adding three logins, adding them to the sysadmin role, and then testing to see if it worked.

    CREATE LOGIN [MyDomain\Domain Admins] FROM WINDOWS;


    CREATE LOGIN [MyDomain\lowell] FROM WINDOWS;


    EXEC sp_addsrvrolemember 'MyDomain\Domain Admins','sysadmin';

    EXEC sp_addsrvrolemember 'MyDomain\APP-SQL-DBAs','sysadmin';

    EXEC sp_addsrvrolemember 'MyDomain\lowell','sysadmin';


    SELECT IS_srvrolemember('sysadmin','MyDomain\APP-SQL-Admins')

    SELECT IS_srvrolemember('sysadmin','MyDomain\lowell')



    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • No luck with the -E -A with my own local admin account.

    We're using a domain\account to run the SQL Server service. That account has the same privs as my own, including local admin on the SQL Servers.

    I am not able to connect using -A with that account, in single-user or otherwise.

    I'm running out of ideas.

    Going back to Gail's post: how does the SQL Server service need to be started in order to connect in single user using a local machine admin account?


  • I was able to get it sorted using PsExec and the instructions here.



    Thanks gents.


Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply