How to add a SQL user to a server when password is missing (How to script out a SQL user when you don't know the password)

Hi guys!

Today's post is a very common request.

What do you do if you have to add a SQL user to an instance, but don't know what the password is?

It's actually an easy fix.

First, you need to find the SID for the SQL user so you can map the user irregardless of the instance you are on.

--Step 0
SELECT SUSER_SID('yourSqlUser');  

GO  

Note: You need to copy the result from the results pane in SSMS. I.E.:
0x6FE56AD1A3AA6C40A1336814621ED733

Then you have to run 3 T-SQL scripts that will generate the permissions you need. You will need to put the results from the results pane together.

--Step 1
SELECT 'IF (SUSER_ID('+QUOTENAME(SP.name,'''')+') IS NULL) BEGIN CREATE LOGIN ' +QUOTENAME(SP.name)+
CASE 
WHEN SP.type_desc = 'SQL_LOGIN' THEN ' WITH PASSWORD = ' +CONVERT(NVARCHAR(MAX),SL.password_hash,1)+ ' HASHED, CHECK_EXPIRATION = ' 
+ CASE WHEN SL.is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END +', CHECK_POLICY = ' +CASE WHEN SL.is_policy_checked = 1 THEN 'ON,' ELSE 'OFF,' END
ELSE ' FROM WINDOWS WITH'
END 
+' DEFAULT_DATABASE=[' +SP.default_database_name+ '], DEFAULT_LANGUAGE=[' +SP.default_language_name+ '] END;' COLLATE SQL_Latin1_General_CP1_CI_AS AS [-- Logins To Be Created --]
FROM sys.server_principals AS SP LEFT JOIN sys.sql_logins AS SL
ON SP.principal_id = SL.principal_id
WHERE SP.type IN ('S','G','U')
AND SP.name NOT LIKE '##%##'
AND SP.name NOT LIKE 'NT AUTHORITY%'
AND SP.name NOT LIKE 'NT SERVICE%'

AND SP.name LIKE ('sa'); --replace 'sa' with the specific username you need

Note: You need to copy the result from the results pane in SSMS. I.E.:
IF (SUSER_ID('distributor_admin') IS NULL) BEGIN CREATE LOGIN [yourSqlUser] WITH PASSWORD = 0x020031B61E7C00D2E62ED7940B8040275DD6E387AD39D71A905DD04BD83FBB151823027AD4293D72A22AE7E17ADC3AC2FB0B3A0FE725A484DF55840E6A1FC7359B37A9F12EDD HASHED, CHECK_EXPIRATION = OFF, CHECK_POLICY = ON, DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english] END;



-- Step 2
SELECT 
'EXEC master..sp_addsrvrolemember @loginame = N''' + SL.name + ''', @rolename = N''' + SR.name + '''
' AS [-- Server Roles the Logins Need to be Added --]
FROM master.sys.server_role_members SRM
JOIN master.sys.server_principals SR ON SR.principal_id = SRM.role_principal_id
JOIN master.sys.server_principals SL ON SL.principal_id = SRM.member_principal_id
WHERE SL.type IN ('S','G','U')
AND SL.name NOT LIKE '##%##'
AND SL.name NOT LIKE 'NT AUTHORITY%'
AND SL.name NOT LIKE 'NT SERVICE%'

AND SL.name LIKE ('sa'); --replace 'sa' with the specific username you need

Note: You need to copy the result from the results pane in SSMS. I.E.:
EXEC master..sp_addsrvrolemember @loginame = N'yourSqlUser', @rolename = N'sysadmin'

**If  there are no results for this user with this script, your user may not have this level of permissions. However, to be sure, go to the original blog post on the Data Avail blog here to see the script with all logins to verify your user doesn't have a role.**  

--step 3 
SELECT 
CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION' 
THEN SrvPerm.state_desc 
ELSE 'GRANT' 
END
+ ' ' + SrvPerm.permission_name + ' TO [' + SP.name + ']' + 
CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION' 
THEN '' 
ELSE ' WITH GRANT OPTION' 
END collate database_default AS [-- Server Level Permissions to Be Granted --] 
FROM sys.server_permissions AS SrvPerm 
JOIN sys.server_principals AS SP ON SrvPerm.grantee_principal_id = SP.principal_id 
WHERE SP.type IN ( 'S', 'U', 'G' ) 
AND SP.name NOT LIKE '##%##'
AND SP.name NOT LIKE 'NT AUTHORITY%'
AND SP.name NOT LIKE 'NT SERVICE%'

AND SP.name LIKE ('sa'); --replace 'sa' with the specific username you need

Note: You need to copy the result from the results pane in SSMS. I.E.:
GRANT CONNECT SQL TO [yourSqlUser]

Now that you have the results from the 3 scripts, copy and paste them in a new query window on the server where you would like to add the login.

Edit the first result by adding the SID in Step 0 to the result from Step 1.
I.E. IF (SUSER_ID('distributor_admin') IS NULL) BEGIN CREATE LOGIN [yourSqlUser] WITH PASSWORD = 0x020031B61E7C00D2E62ED7940B8040275DD6E387AD39D71A905DD04BD83FBB151823027AD4293D72A22AE7E17ADC3AC2FB0B3A0FE725A484DF55840E6A1FC7359B37A9F12EDD HASHED, SID = 0x6FE56AD1A3AA6C40A1336814621ED733, CHECK_EXPIRATION = OFF, CHECK_POLICY = ON, DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english] END;

Now that all 3 results are complete, execute the 3 results in the query window against the Master database.

You're all set!

If you have any questions related to this post, please put them below.


Thank you and Happy reading,

-marshé hutchinson
#learnSQLwithme