导出sqlserver数据库中的登录用户和密码信息(2)
2022-12-19 16:53 abce 阅读(253) 评论(0) 编辑 收藏 举报sp_hexadecimal和sp_help_revlogin脚本的版本有点老，有人做了更新，因此记录一下。
相关连接：
导出sqlserver数据库中的登录用户和密码信息 - abce - 博客园 (cnblogs.com)
Stop using sp_hexadecimal & sp_help_revlogin - Andy M Mallon - AM² (am2.co)
dbo.ServerLogins.sql
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
dbo.ServerLoginPermissions.sql
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
