Post reply

Script All Logins / Users / and Roles

  • November 2, 2016 at 8:49 am

    #1910136

    Thanks for the script, I'll dive into it.

  • March 29, 2018 at 10:47 am

    #1985002

    All logins and rolas together:

    SELECT
    Case when ROW_NUMBER() OVER(PARTITION BY sp.name ORDER BY r.name DESC) = 1
    then
    '
    IF(SUSER_ID('+QUOTENAME(SP.name,'''')+') IS NULL)
    BEGIN
    CREATE LOGIN '+QUOTENAME(SP.name)+
    ' WITH PASSWORD = '+CONVERT(NVARCHAR(MAX),SL.password_hash,1)+' HASHED '+ ', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
    END;'
    +
    '
    ALTER SERVER ROLE ['+R.name+'] ADD MEMBER ['+SP.name+']
    GO
    '
    COLLATE SQL_Latin1_General_CP1_CI_AS
    ELSE
    '
    ALTER SERVER ROLE ['+R.name+'] ADD MEMBER ['+SP.name+']
    GO
    '
    end string_content
    ,'Login backup'
    ,SP.name + ' as ' + R.name
    ,getdate()
    FROM sys.server_principals AS SP
    LEFT JOIN sys.sql_logins AS SL
    ON SP.principal_id = SL.principal_id
    JOIN sys.server_role_members RM
    ON SP.principal_id = RM.member_principal_id
    JOIN sys.server_principals R
    ON RM.role_principal_id = R.principal_id
    WHERE SP.type_desc IN ('SQL_LOGIN')
    AND SP.name NOT LIKE '##%##'
    AND SP.name NOT IN ('SA')

    These are just sql logins, not windows autenticated users.

    ---------------------------------------------------------
    Database Comedian as my Friends says 🙂

  • October 17, 2018 at 12:37 pm

    #2010135

    Missing pieces:
    re-creating any custom roles
    SQL users without logins

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply