problem with sa account after cloning server

Question

problem with sa account after cloning server

motif

SSC Enthusiast

Points: 177
More actions
October 8, 2017 at 3:19 am

#333146

Hi,
somehow despite of sever being in windows and SQL authentication mode the sa account is disabled.
I can log in as sysadmin but cannot enable sa or create any other user since not enough privileges.
Tried already putting server into single user mode and from command line alter login sa but same error,
no permission or account doesn't exist. Then I tried running SSMS with PsExec (from sys internals) to run it as
NT_AUTHORITY and still have no permission to enable sa....
Any other last resort solution before i will have to reinstall sql server?

Viewing 9 posts - 1 through 9 (of 9 total)

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

Thom A SSC Guru Points: 99139 More actions · Answer 1

You say you received an error when trying to re-enable the sa account. What was this error?

Do you have any other accounts that have sysadmin privs? Generally, if you are using SQL Server Authentication, it's a good idea to disable the sa account and create a different account. This at least means that someone can't brute force access that account. Did you create a different account hat you have access to?

If not, do you have access to the Computer/Server that SQL server is running on, and access to run process as the local administrator? If so, you can log into the SQL Server as a Local Admin and re-enable (or create) a system Administrator account. Details on how to do so can be be found on the Microsoft Docs site.

If you don't have access to run as local administrator, find someone that does. If you don't and no one else does then, unfortunately, you're going to be locked out of the SA account(s) on your SQL Server.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Sue_H SSC Guru Points: 90860 More actions · Answer 2

urban.robert - Sunday, October 8, 2017 3:19 AM
I can log in as sysadmin but cannot enable sa or create any other user since not enough privileges.

A sysadmin bypasses security checks so you are not logging into SQL Server as a sysadmin.

Sue

motif SSC Enthusiast Points: 177 More actions · Answer 3

Thom A - Sunday, October 8, 2017 10:11 AM
You say you received an error when trying to re-enable the sa account. What was this error?
Do you have any other accounts that have sysadmin privs? Generally, if you are using SQL Server Authentication, it's a good idea to disable the sa account and create a different account. This at least means that someone can't brute force access that account. Did you create a different account hat you have access to?
If not, do you have access to the Computer/Server that SQL server is running on, and access to run process as the local administrator? If so, you can log into the SQL Server as a Local Admin and re-enable (or create) a system Administrator account. Details on how to do so can be be found on the Microsoft Docs site.
If you don't have access to run as local administrator, find someone that does. If you don't and no one else does then, unfortunately, you're going to be locked out of the SA account(s) on your SQL Server.

you didn't read what I have posted with understanding. Of course I have full access to VM this server is installed on, I said I am logged in as sysadmin.

Thom A SSC Guru Points: 99139 More actions · Answer 4

urban.robert - Sunday, October 8, 2017 12:18 PM
you didn't read what I have posted with understanding. Of course I have full access to VM this server is installed on, I said I am logged in as sysadmin.

if you are logged in as a sysadmin, then you would be able to enable the sa account, like Sue pointed out. sysadmin's can do anything; if you receive a permission denied then you aren't a sysadmin. My understanding was fine.

As you have access to the local admin, did you try the instructions in the link I gave before shooting down my understanding?

Out of interest, what is the output of this statement:
PRINT 'The current User' + CASE IS_SRVROLEMEMBER ('sysadmin', SYSTEM_USER) WHEN 1 THEN 'is' ELSE ' is not' END + ' a SysAdmin.';

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

motif SSC Enthusiast Points: 177 More actions · Answer 5

Thom A - Sunday, October 8, 2017 12:26 PM
urban.robert - Sunday, October 8, 2017 12:18 PM
you didn't read what I have posted with understanding. Of course I have full access to VM this server is installed on, I said I am logged in as sysadmin.
if you are logged in as a sysadmin, then you would be able to enable the sa account, like Sue pointed out. sysadmin's can do anything; if you receive a permission denied then you aren't a sysadmin. My understanding was fine.
As you have access to the local admin, did you try the instructions in the link I gave before shooting down my understanding?
Out of interest, what is the output of this statement:
PRINT 'The current User' + CASE IS_SRVROLEMEMBER ('sysadmin', SYSTEM_USER) WHEN 1 THEN 'is' ELSE ' is not' END + ' a SysAdmin.';

By sysadmin I meant local comp admin, maybe I was not clear - output is I'm not sysadmin, what exactly is the problem, because my account is local administrator. Somehow for SQL server it's not enough.
I read somewhere on this forum local admins are not automatically added as sysadmin for sql since 2008 version.

Thom A SSC Guru Points: 99139 More actions · Answer 6

motif - Sunday, October 8, 2017 1:03 PM
By sysadmin I meant local comp admin, maybe I was not clear - output is I'm not sysadmin, what exactly is the problem, because my account is local administrator. Somehow for SQL server it's not enough.
I read somewhere on this forum local admins are not automatically added as sysadmin for sql since 2008 version.

A local admin does not have automatic sysadmin rights on a SQL server instance, no.

Again though, have a look at the link I posted. It tells you what to do.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

motif SSC Enthusiast Points: 177 More actions · Answer 7

Thom A - Sunday, October 8, 2017 1:18 PM
motif - Sunday, October 8, 2017 1:03 PM
By sysadmin I meant local comp admin, maybe I was not clear - output is I'm not sysadmin, what exactly is the problem, because my account is local administrator. Somehow for SQL server it's not enough.
I read somewhere on this forum local admins are not automatically added as sysadmin for sql since 2008 version.
A local admin does not have automatic sysadmin rights on a SQL server instance, no.
Again though, have a look at the link I posted. It tells you what to do.

problem with this solution is I'm getting error stating only one user can login as I'm in single mode. I don't think I have other sql services running except for server browser.
From the command line I have link error, no pipe on the other end etc.

Thom A SSC Guru Points: 99139 More actions · Answer 8

The guide explains how to force the type of application can connect. Also, note that if you're in Single User, do not expand any of the menus, just open a query menu and run the SQL. Using the GUI will use a connection. Instead, you'd be better to limit the application to SQLCMD and issue the enable command via that.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

problem with sa account after cloning server

Cookies on SQLServerCentral