June 29, 2015 at 6:53 am
Yes this is a common topic, but I would like to say I have completed several different solutions to try to correct this. None seem to work and I am a little bit stumped.
Scenario: 3rd party company set up SQL 2008 R2 SP1(awhile ago, years), they disabled SA no other logon was created with Admin rights, Database is Mixed Mode, never had a DBA. the database is from a Transactional system. Now comes me. I need to set up backups, maintenance etc..
I have been provided System Admin\Local Admin rights on the Server. The Server has been set up like a Domain Controller. I have tried all of the command line options that I know and I can find. I have not been able to get any to work, they all fail login to SQL. Before I take the route of reinstalling SQL on the server I want to make sure I haven't missed any thing, especially since this is a 100% production environment with 3rd party processes and applications attached that are not documented or supported. (nightmare)
I would prefer a cmdline or PowerShell script (ps2)
I figure that the holiday is coming up so whatever I will end up needing to do I will be able to complete over the holiday weekend when it is down.
One set I tried as "run as Admin"
[Code="other"]
SQLServr.Exe –m
SQLCMD –S <Server_Name\Instance_Name>
CREATE LOGIN ‘<Login_Name>’ with PASSWORD=’<Password>’
GO
SP_ADDSRVROLEMEMBER '<Login_Name>','SYSADMIN'
[/code]
I should point out the rest of our environments are SQL 2012
June 29, 2015 at 7:30 am
My first questions would be
did you manage to start the instance using
[Code="other"]
SQLServr.Exe –m[/code]
if you didn't manage to start the instance what error did you get for the login failure (check the sql server error log or the windows application log)?
Use the following from one cmd window
[Code="other"]
SQLServr.Exe –m -sInstancename[/code]
The instance should start and should display messages like "starting tempdb".
Then in a 2nd cmd window run
SQLCMD –S <Server_Name\Instance_Name> -E
CREATE LOGIN ‘<Login_Name>’ with PASSWORD=’<Password>’
GO
SP_ADDSRVROLEMEMBER '<Login_Name>','SYSADMIN'
Please post back details of any errors you receive and at what point
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 29, 2015 at 7:45 am
I keep going back and checking my windows account thinking they permissions aren't right but they look fine. Its not like I haven't done this before. All I can think if is it has to be with my windows Account but I can't find anything wrong with it.
Error messages received
SQLServr.Exe –m (-m not recognized)
SQLServr.Exe –m -sInstancename (-m not recognized)
SQLCMD –S <Server_Name\Instance_Name> -E (Failed login)
I had got logged in once, and received"
CREATE LOGIN ‘<Login_Name>’ with PASSWORD=’<Password> ’Failed on Privileges (I don't recall exact message)
June 29, 2015 at 8:09 am
can you provide a screenshot of the error in the cmd window?
are you copying and pasting the command into the command window?
When connecting to the instance use
SQLCMD –S Server_Name\Instance_Name -E
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 29, 2015 at 9:22 am
today when trying to start instance I am receiving
SQLServr.exe -m
SQLServr.exe is not recognized as an internal or external command, operable program, or batch file
SQLcmd -S ServerName -E
Unable to provide the screen shot but, it says "Login Failed for User"
I really think this has to do with the domain controller aspect of the equation.
Can you tag members in questions?
July 6, 2015 at 10:10 am
I resolved my issue with a bit of help from various resources. I uploaded a document on what I did to get Admin rights back.
Thank you everyone.:hehe:
July 6, 2015 at 10:22 am
that document doesn't do anymore than what you say you were doing except that your directly modifying the service startup.
Inconsistencies I can see are you start a default instance of sql server from the command prompt
SQLServr.Exe –m
Then you try and connect to a named instance
SQLCMD –S <Server_Name\Instance_Name>
Anyhow at least you have it working now
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 6, 2015 at 10:23 am
Thanks for the document, and that works, but starting from a command line also works. If it doesn't work, I suspect you don't have rights or you needed to specify an instance.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
July 6, 2015 at 1:23 pm
I could not start SQL Server in single user mode from the command line. I had to alter the Start up properties to work.
1) All references said to place a -m;C:\.... in the Start up Parameters section at the front. This did not work. [highlight=#ffff11]I had to place it at the end, and the text I had to use was -m sqlcmd
[/highlight]
2) Another option provided was to open via the command line in single user mode the SQLsrvr.exe -m this did not work either -m unrecognizable
3) sqlcmd -m also the -m unrecognizable
The configuration of the SQL Server had the option turned off
July 6, 2015 at 1:48 pm
swoozie (7/6/2015)
SQLsrvr.exe -m this did not work either -m unrecognizable
You have to run this from the folder where the exe lives, usually
C:\Program Files\Microsoft SQL Server\MSSQL11.INSTANCEID\MSSQL\Binn
As i said before it seems you were attempting to start a default instance but trying to connect to named instance
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply