DBA - SQL login script

 CREATE TABLE #ToDo

(

 RID TINYINT

,MyLogin varchar(128)

,MyScript varchar(max)

)

CREATE TABLE #MyTemp

(

 RID INT IDENTITY(1,1) Primary Key

,LoginName nvarchar(max)

,DBname nvarchar(max)

,Username nvarchar(max)

,AliasName nvarchar(max)

)

INSERT INTO #MyTemp

(LoginName,DBname,Username,AliasName)

EXEC master..sp_msloginmappings 

CREATE TABLE #Logins

(

 RID INT IDENTITY(1,1) Primary Key

,LoginName nvarchar(max)

,DBname nvarchar(max)

,Username nvarchar(max)

,AliasName nvarchar(max)

)

INSERT INTO #Logins

(LoginName,DBname,Username,AliasName)

SELECT LoginName,DBname,Username,AliasName

    FROM #MyTemp 

    WHERE dbname IS NOT NULL

    AND SUBSTRING(LoginName,1,1) != '#'

    AND SUBSTRING(LoginName,1,3) != 'NT '

CREATE TABLE #Alters

(

 RID INT IDENTITY(1,1) Primary Key

,LoginName nvarchar(max)

,ALTERED nvarchar(max)

)

DECLARE @CurRow INT = 1

DECLARE @MaxRow INT

DECLARE @LoginName varchar(128)

DECLARE @DBName varchar(256)

DECLARE @MySQL varchar(max)

SELECT @MaxRow = MAX(RID)

    FROM #Logins

WHILE @CurRow <= @MaxRow

    BEGIN

        SELECT @LoginName = LoginName

            ,@DBName = DBName

            FROM #Logins

            WHERE RID = @CurRow

        ;WITH MyCTE

        AS

        (

        SELECT DP1.name AS DatabaseRoleName

            , isnull (DP2.name, 'No members') AS DatabaseUserName   

            FROM sys.database_role_members AS DRM  

                RIGHT OUTER JOIN sys.database_principals AS DP1  

                    ON DRM.role_principal_id = DP1.principal_id  

                LEFT OUTER JOIN sys.database_principals AS DP2  

                    ON DRM.member_principal_id = DP2.principal_id  

            WHERE DP1.type = 'R'

            AND DP2.name Like @LoginName

        )

        INSERT INTO #Alters

        (LoginName,ALTERED)

        SELECT @LoginName, 'USE '+@DBName+';'+char(10)+char(13)+'GO;'+char(10)+char(13)+ 'EXEC sp_addrolemember '''+DatabaseRoleName+''', '''+@LoginName+''';'

            FROM MyCTE

        SET @CurRow = @CurRow + 1

    END

INSERT INTO #ToDo

(RID, MyLogin, MyScript)

SELECT RID = 0, MyLogin = REPLACE(REPLACE(QUOTENAME(SP.name), '[',''),']','')

    ,'CREATE LOGIN ' +

QUOTENAME(SP.name)

        +CASE WHEN SP.type_desc = 'SQL_LOGIN' THEN ' WITH PASSWORD = '' '' '

       + ' , 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+ '] '

COLLATE SQL_Latin1_General_CP1_CI_AS AS MyScript

    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 <> ('sa');

INSERT INTO #ToDo

(RID, MyLogin, MyScript)

SELECT RID = 2

    , MyLogin = SL.name 

    ,'EXEC master..sp_addsrvrolemember @loginame = N''' + SL.name + ''', @rolename = N''' + SR.name + '''' AS MyScript

    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 <> ('sa');

INSERT INTO #ToDo

(RID, MyLogin, MyScript)

SELECT RID = 3

    ,MyLogin = SP.name 

    ,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 MyScript

    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 <> ('sa');

INSERT INTO #ToDo

(RID, MyLogin, MyScript)

SELECT RID = 4, MyLogin = LoginName, MyScript = ALTERED

    FROM #Alters

    ORDER BY LoginName, ALTERED

--SELECT * FROM #ToDo

SELECT RID, MyLogin, MyScript

    FROM #ToDo where MyLogin  not like 'CLEANHARBORS\%'

    ORDER BY MyLogin, RID, MyScript

-- cleanup

DROP TABLE #MyTemp

DROP TABLE #Logins

DROP TABLE #Alters

DROP TABLE #ToDo