Problem:-
You may
inherited MSSQL Database Server from another dba without any documentation for
sysadmin user name and password or lost all password even sa password
or sa account is disabled or no one knows the password or get
lab machine without taken a password from your trainer or any good intention
reasons 😃
A solution, in a nutshell, Using CMD:-
Open a command prompt(cmd) with administrative privileges Then
Type below as-is cmd switches is case sensitive.
If using the default
instance
Net Stop MSSQLSERVER
Enter
Net Start MSSQLSERVER /m
(as-is cmd switches case sensitive)
Enter
SQLCMD
Enter
CREATE LOGIN TEMPSA
WITH PASSWORD=N'P@ssword';
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [TEMPSA];
GO
Exit
Net Stop MSSQLSERVER
Enter
Net Start MSSQLSERVER
Enter
If Using Named Instance
You need to get first your
name of the instance by copy n paste below command in cmd terminal.
Wmic service where (PathName like '%Binn\\sqlservr%') get caption, name, startmode, state, PathName, ProcessId
or by typing below in cmd
sc query type= service state= all |find "SQL" |find /V "DISPLAY_NAME"
NET Stop
MSSQL$DEV >> (NET Stop MSSQL$instanceName)
Enter
Net Start MSSQL$DEV /m
(as-is cmd switches case sensitive)
Enter
SQLCMD
Enter
CREATE LOGIN TEMPSA
WITH PASSWORD=N'P@ssword';
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [TEMPSA];
GO
Exit
Net Stop MSSQL$DEV
Enter
Net Start MSSQL$DEV
Enter
A solution in Details:-
1.
Insure that you are login to the machine using user member of the
administrator's group or Local administrator itself.
2.
Stop all SQL Server Services.
3.
Start SQL
server service engine (SQL Server (instance name)) in single-user
mode using below steps with insuring the SQL
Server Agent service stopped. Otherwise, SQL Server Agent might connect
first and prevent you from connecting as a second user.
Start SQL Server in single
user Mode Using GUI
Start SQL Server
Configuration Manager >> go to SQL Server Service (instance name) >>
then SQL Server Service Properties >>then startup Parameters >>
then type -m >> then
press add
Then open CMD as
administrator (make sure you type below AS-IS because SQLCMD switches are case sensitive)
SQLCMD -S servername Or SQLCMD
-S . Or SQLCMD (don't worry If you do not specify
the SQL Server name, it will try to connect to the local machine)
Or
Or
Then Type or Copy and paste
below
CREATE LOGIN TEMPSA
WITH PASSWORD=N'P@ssword';
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [TEMPSA];
GO
Exit
Now Open SSMS and try to
Connect using TEMPSA as SQL Authentication User with Password
"P@ssword" Now that user had the System admin
privilege should work fine.
If Not Work with server
error as below
Sqlcmd: Error: Microsoft ODBC
Driver 13 for SQL Server : Login failed for user 'ASUSWS\Mk.elsawy'. Reason:
Server is in single user mode. Only one administrator can connect at this
time..
That
means there is a user or system service user connect first so you need to
double-check and stop SQL Server Agent (instancename)
and restart engine SQL Server (instancename) to drop any user connection to SQL
server, also check SQL Error Log and you will find which user still connect
first to the SQL server.
you can
change SQL Service account with windows login has admin rights and try again it
should work fine.
Don’t
Forget
Revert
back the changes you had made on the instance level i.e. remove
the –m startup parameter and
restart the SQL Server Instance.
Notes:-
1. The application will be down during that activity.
2.
If you need to ensure that no connection coming from the application
server to SQL Server using windows firewall to block any communication throw
port 1433 or your custom port if configured to your SQL server.
3.
also, you can block connection to SQL Server by changing Port
Temporary to any port will help you block any connection from the application
servers.
Ref:-
No comments:
Post a Comment