导出sqlserver数据库中的登录用户和密码信息(2)

2022-12-19 16:53 abce 阅读(273) 评论(0) 编辑收藏举报

sp_hexadecimal和sp_help_revlogin脚本的版本有点老，有人做了更新，因此记录一下。

Stop using sp_hexadecimal & sp_help_revlogin - Andy M Mallon - AM² (am2.co)

IF NOT EXISTS (SELECT * FROM sys.objects WHERE type = 'V' AND object_id = object_id('dbo.ServerLogins'))
    EXEC ('CREATE VIEW dbo.ServerLogins AS SELECT Result = ''This is a stub'';' )
GO
 
ALTER VIEW dbo.ServerLogins
/*************************************************************************************************
AUTHOR: Andy Mallon
CREATED: 20210627
    View to get basic info for logins, simplifying/flattening DMVs, to make applying additional 
    criteria/filters from automation & troubleshooting easier
EXAMPLES:
* All logins that can log in (enabled + have CONNECT SQL), modified in the last 7 days:
    SELECT LoginName, DateModified, CreateSql
    FROM dbo.ServerLogins
    WHERE CanLogIn = 1
    AND DateModified >= DATEADD(DAY, -7, GETUTCDATE())
**************************************************************************************************
    This code is licensed as part of Andy Mallon's DBA Database.
    https://github.com/amtwo/dba-database/blob/master/LICENSE
    ©2014-2020 ● Andy Mallon ● am2.co
*************************************************************************************************/
AS
    SELECT 
            LoginSid                = p.sid, 
            LoginName               = p.name, 
            LoginType               = p.type_desc,
            DefaultDatabase         = p.default_database_name,
            VarbinaryPasswordHash   = sl.password_hash,
            IsPolicyChecked         = IIF(sl.is_policy_checked=1,1,0),
            IsExpirationChecked     = IIF(sl.is_expiration_checked=1,1,0),
            IsEnabled               = IIF(p.is_disabled = 0,1,0),
            CanLogIn                = IIF(perm.state IN ('G','W'),1,0),
            CreateSql               = CASE
                                        WHEN p.type IN ('U','G')
                                            THEN CONCAT(N'CREATE LOGIN ', 
                                                     QUOTENAME(p.name),
                                                     N' FROM WINDOWS',
                                                     N' WITH DEFAULT_DATABASE = ',
                                                     QUOTENAME(p.default_database_name),
                                                     N';'
                                                 )
                                        WHEN p.type = 'S'
                                            THEN CONCAT(N'CREATE LOGIN ', 
                                                     QUOTENAME(p.name),
                                                     N' WITH PASSWORD = ',
                                                     CONVERT(varchar(514), sl.password_hash, 1),
                                                     N' HASHED, SID = ',
                                                     CONVERT(varchar(514), p.sid, 1),
                                                     N', DEFAULT_DATABASE = ',
                                                     QUOTENAME(p.default_database_name),
                                                     N', CHECK_POLICY = ',
                                                     IIF(sl.is_policy_checked=1,N'ON','OFF'),
                                                     N', CHECK_EXPIRATION = ',
                                                     IIF(sl.is_expiration_checked=1,N'ON','OFF'),
                                                     N';'
                                                 )
                                      END,
            EnableSql               = CONCAT(N'ALTER LOGIN ',
                                            QUOTENAME(p.name), ' ',
                                            IIF(p.is_disabled = 1,' DISABLE',' ENABLE'),
                                            N';'
                                    ),
            DateCreated             = p.create_date,
            DateModified            = p.modify_date
            FROM sys.server_principals AS p
            LEFT JOIN sys.sql_logins AS sl 
                ON p.name = sl.name
            --Left join here to check to determine if the login is enabled & has connect SQL
            LEFT JOIN sys.server_permissions AS perm 
                ON perm.grantee_principal_id = p.principal_id
                AND perm.type = 'COSQ' 
                AND perm.state IN ('G','W')
                AND p.is_disabled = 0
            WHERE p.type IN ('S','U','G')
            AND p.name <> N'sa'
            AND p.name NOT LIKE N'##%##';
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE type = 'V' AND object_id = object_id('dbo.ServerLoginPermissions'))
    EXEC ('CREATE VIEW dbo.ServerLoginPermissions AS SELECT Result = ''This is a stub'';' )
GO
 
ALTER VIEW dbo.ServerLoginPermissions
/*************************************************************************************************
AUTHOR: Andy Mallon
CREATED: 20210627
    View to get basic info for server-level permissions, simplifying/flattening DMVs, to make applying additional 
    criteria/filters from automation & troubleshooting easier
EXAMPLES:
* Get GRANT/DENY commands for permissions on all enabled users
    SELECT p.LoginName, p.PermissionSql
    FROM dbo.ServerLoginPermissions AS p
    JOIN dbo.ServerLogins AS l ON l.LoginSid = p.LoginSid
    WHERE l.IsEnabled = 1;
**************************************************************************************************
    This code is licensed as part of Andy Mallon's DBA Database.
    https://github.com/amtwo/dba-database/blob/master/LICENSE
    ©2014-2020 ● Andy Mallon ● am2.co
*************************************************************************************************/
AS
    SELECT 
            LoginSid                = p.sid, 
            LoginName               = p.name, 
            LoginType               = p.type_desc,
            DefaultDatabase         = p.default_database_name,
            LoginIsEnabled          = IIF(p.is_disabled = 0,1,0),
            CanLogIn                = COALESCE((SELECT TOP 1 1 FROM sys.server_permissions AS cosq
                                                WHERE cosq.grantee_principal_id = p.principal_id
                                                AND cosq.type = 'COSQ' 
                                                AND cosq.state IN ('G','W')
                                                AND p.is_disabled = 0
                                                ),
                                        0),
            PermissionType          = perm.type,
            PermissionState         = perm.state,
            PermissionSql           = CONCAT(perm.state_desc, N' ',
                                                perm.permission_name, N' TO ',
                                                QUOTENAME(p.name) COLLATE Latin1_General_CI_AS_KS_WS, 
                                                N';'
                                                ),
            DateLoginCreated        = p.create_date,
            DateLoginModified       = p.modify_date
    FROM sys.server_principals AS p
    JOIN sys.server_permissions AS perm 
        ON perm.grantee_principal_id = p.principal_id
    WHERE p.type IN ('S','U','G')
    AND p.name <> N'sa'
    AND p.name NOT LIKE N'##%##';
GO