Grant sysadmin permissions to windows login when SQL Server SA login password is not known

Here are the detailed steps:

1: Start SQL Server in Single User Mode Open SQL Server Configuration Manager.

 
Stop the SQL Server Instance you need to recover

                                      |  
Right-click on the SQL Server Instance and select Properties.

                                      |  
Click on the Advanced tab, and add -m or -f; to the beginning of Startup parameters.

                                      |  
Click OK and start the instance.

2: Add an existing login or a newly created one to the sysadmin server role

After the SQL Server Instance starts in single-user mode, the Windows Administrator account is able to connect to SQL Server using the sqlcmd utility using Windows authentication. You can use Transact-SQL commands such as "sp_addsrvrolemember" to add an existing login or a newly created one to the sysadmin server role.


Open an elevated command prompt and enter the command:

SQLCMD -S myServer\instanceName

Replace myServer\instanceName with the name of the computer and the instance of SQL Server that you want to connect to.
At the next prompts, enter the following commands to create windows login for sysadmin role:

CREATE LOGIN [US\TEST] FROM WINDOWS
go
ALTER SERVER ROLE sysadmin ADD MEMBER [US\TEST] 

(Grant sysadmin role for newly created login or existing login)

go
quit

 
Stop the SQL Server instance.

                          |  
Remove the -m option from the Start parameters field, 

                          |

and then start the SQL Server service.


At this point you should be able to login to SQL Server with sysadmin privileges and reset the SA login password.

Login backup before restoring databases on SQL Server

To take a backup of SQL serve logins and permissions. 

SELECT 'CREATE USER [' + NAME + '] FOR LOGIN [' + NAME + ']' AS '--Database Users Creation--'
FROM sys.database_principals
WHERE Type IN (
'U'
,'S','G'
)
AND NAME NOT IN (
'dbo'
,'guest'
,'sys'
,'INFORMATION_SCHEMA'
)
GO
SELECT 'EXEC sp_AddRoleMember ''' + DBRole.NAME + ''', ''' + DBUser.NAME + '''' AS '---Add Users to Database Roles---'

FROM sys.database_principals DBUser
INNER JOIN sys.database_role_members DBM 
ON DBM.member_principal_id = DBUser.principal_id

INNER JOIN sys.database_principals DBRole 
ON DBRole.principal_id = DBM.role_principal_id
GO

SQL Server - To check all logins and permission and other important details of SQL Server User.

Easy script-

To check all logins and permission and other details

SELECT

p.name as 'Name',

Case p.type

When 'S' Then 'SQL login'

When 'U' Then 'Windows login'

When 'G' Then 'Windows group'

When 'R' Then 'Server role'

When 'C' Then 'Login mapped to a certificate'

When 'K' Then 'Login mapped to an asymmetric key' End as 'Type',

Case sp.type When 'COSQ' Then 'CONNECT SQL' End as 'Permission Type',

sp.[state_desc] as 'Permission State',

p.is_disabled AS IsDisabled,

LOGINPROPERTY(p.name, N'IsExpired') AS IsExpired,

LOGINPROPERTY(p.name, N'IsLocked') AS IsLocked,

LOGINPROPERTY(p.name, N'IsMustChange') AS IsMustChange,

CAST(sl.is_policy_checked AS bit) AS PasswordPolicyEnforced,

CAST(sl.is_expiration_checked AS bit) AS PasswordExpirationEnabled,

LOGINPROPERTY(p.name, N'BadPasswordCount') AS BadPasswordCount,

LOGINPROPERTY(p.name, N'BadPasswordTime') AS BadPasswordTime,

LOGINPROPERTY(p.name, N'PasswordLastSetTime') AS PasswordLastSetTime,

LOGINPROPERTY(p.name, N'LockoutTime') AS LockoutTime,

LOGINPROPERTY(p.name, N'DaysUntilExpiration') AS DaysUntilExpiration,

p.create_date AS CreateDate,

p.modify_date AS LastModifieddate

FROM sys.server_principals AS p

LEFT OUTER JOIN sys.server_permissions AS sp ON sp.grantee_principal_id = p.principal_id

LEFT OUTER JOIN sys.sql_logins AS sl ON sl.principal_id = p.principal_id

WHERE

(sp.[type] = N'COSQ' and sp.type is not null)

and p.type not in ('R', 'C', 'K')

And p.name not like '##MS_%' --Filter out Certificate-based SQL Server Logins (https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/principals-database-engine)

Order By p.Type ASC, p.name ASC

User is unable to connect to SQL Server in 2016. What may be scenarios and how to troubleshoot it?

Possible Scenarios and Resolution to login issue with Fixes-

• •  Error: 26: SQL Browser Firewall No connectivity between client and server.- Check SQL Server browser service using Services.msc 
  
  
  •  Error: 28: Instance TCP/IP was disabled- Go to SQL Server configuaration manager and enable the TCP/IP 
  
  
  •  Error: 40: Instance service is not running - Open the services and RUN the MSSQLSERVICE to permanent FIX run type make it as automatically. 
  
  
  •  Error: 18456: Login failed. (invalid login or password ) - Check your user name and pasowrd, Also server which you are trying to connect.
  
  
  •  Expired Timeout: Network issue, Server is busy, In server max sessions are open ,No available session memory - Try to ping the server, if network issue contact to Network team and if Memory issue contact to DBA support team or increase the memory. 
  
  
  •  Connection Forcibly Closed: Update the client computer to the server version of the SQL Server Native Client.
  
  
  •  In single user mode: if any other service is connected with the db Engine, it doesn't allow connections. change the mode as multiuser
  
  

How to give select permission to account to see the only system databases table and DMVs or How to Grant Server State Permissions to User to query DMVs?

First create an user with Db_datareader permission and grant VIEW SERVER STATE permission

-- Syntax

USE master;

GO

GRANT VIEW SERVER STATE TO UserName;

GO

-- Example

USE master;

GO

GRANT VIEW SERVER STATE TO 'Fareast/TestReaderUser'

OR

GRANT VIEW SERVER STATE TO TestReaderUser

GO

Please comment belo if you know any other step to do the same. - Jainendra Verma