Local admin rights

Question

Local admin rights

schleep

SSChampion

Points: 12582
More actions
May 3, 2016 at 9:29 am

#310333

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.

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

schleep SSChampion Points: 12582 More actions · Answer 2

schleep

SSChampion

Points: 12582

May 3, 2016 at 9:41 am

#1876294

Right! ugh.

Thanks Gail.

Lowell SSC Guru Points: 323504 More actions · Answer 3

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

SQLCMD -S .\SQLEXPRESS -E -A

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\APP-SQL-DBAs] FROM WINDOWS;

CREATE LOGIN [MyDomain\lowell] FROM WINDOWS;

GO

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

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

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

GO

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

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

GO

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!

schleep SSChampion Points: 12582 More actions · Answer 4

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?

Thanks

schleep SSChampion Points: 12582 More actions · Answer 5

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

https://www.mssqltips.com/sqlservertip/2682/recover-access-to-a-sql-server-instance/

Phew.

Thanks gents.

P