Technical Article

Generate Login Script(2008R2 And Older)

,

- This Code Generate Script from Logins of Sql Server to recerate in other instance .
- In the generated script Passwords are Hashed .
- this code can not create correctly script in Sql server 2012 or Later Version Because the Password is strong in these .
SELECT 'IF(SUSER_ID('+QUOTENAME(p.name,'''')+') IS NULL)BEGIN CREATE LOGIN '+QUOTENAME(p.name)+
       CASE WHEN p.type_desc = 'SQL_LOGIN'
            THEN ' WITH PASSWORD = '+CONVERT(NVARCHAR(MAX),L.password_hash,1)+' HASHED'
            ELSE ' FROM WINDOWS'
       END + ';/*'+p.type_desc+'*/ END;'
       COLLATE SQL_Latin1_General_CP1_CI_AS
  FROM sys.server_principals AS p
  LEFT JOIN sys.sql_logins AS L
    ON p.principal_id = L.principal_id
 WHERE p.type_desc IN ('SQL_LOGIN','WINDOWS_GROUP','WINDOWS_LOGIN')
   AND p.name NOT IN ('SA')
   AND p.name NOT IN ('Shayan')
   AND p.name NOT LIKE '##%##';

Rate

3.17 (6)

Share

Share

Rate

3.17 (6)