主页 / dba / 问题 / 285002
Accepted
Peter Å
Asked: 2021-02-10 00:13:52 +0800 CST

审计 SQL Server 2008 R2 中的数据库连接

  • 772

我们最终计划关闭我们最后一个 SQL Server 2008 R2 服务器。在我们继续之前，我们需要跟踪哪些用户正在连接到哪些数据库。我们尝试了不同的选项，例如使用 SQL 审计和检查错误日志中的登录，但所有这些都缺少连接到哪个数据库的信息。我们也考虑过使用 LOGON 触发器，但由于它是在用户登录到实例时触发的，所以它不会跟踪用户之后是否更改数据库。

在较新的版本中，我使用扩展事件进行此类审核，但此数据库版本中尚不提供 sqlserver.login 事件。似乎应该可以为此使用 Profiler，但我担心专门在这台旧服务器上持续运行跟踪。是否还有其他我错过的选项或者 Profiler 是唯一的选项？

sql-server sql-server-2008-r2

3 个回答

  1. 几年前我遇到了类似的问题，并创建了一个 SQL 代理作业来每隔几分钟运行一次下面的查询。查询轮询 sys.dm_exec_sessions、sys.sysprocesses、sys.dm_exec_connections 它有点粗糙和准备就绪，但它符合我的目的，也可能对您有所帮助

    CREATE TABLE [dbo].[log_db_connections](
[Database] [nvarchar](128) NULL,
[Login] [nvarchar](128) NOT NULL,
[nt_user_name] [nvarchar](128) NULL,
[LoginTime] [datetime] NOT NULL,
[CollectionTime] [datetime] NOT NULL CONSTRAINT [DF_CollectionTime] DEFAULT GETDATE(),
[Program] [nvarchar](128) NULL,
[client_interface_name] [nvarchar](32) NULL,
[Host] [nvarchar](128) NULL,
[client_net_address] [varchar](48) NULL,
[client_tcp_port] [int] NULL,
[net_transport] [nvarchar](40) NOT NULL,
[net_packet_size] [int] NULL,
[protocol_type] [nvarchar](40) NULL,
[endpoint_id] [int] NULL,
[encrypt_option] [nvarchar](40) NOT NULL,
[auth_scheme] [nvarchar](40) NOT NULL,
[num_reads] [int] NULL,
[num_writes] [int] NULL
) ON [PRIMARY]
GO



-- Create Unique Clustered Index with IGNORE_DUPE_KEY=ON to avoid duplicates
CREATE UNIQUE CLUSTERED INDEX [CIX_log_db_connections] ON log_db_connections
(
    [Database] ASC,
    Login ASC,
    Host ASC,
    Program ASC,
    LoginTime ASC
) WITH (IGNORE_DUP_KEY = ON)

INSERT [dbo].[log_db_connections]
(
[Database],
[Login],
[nt_user_name],
[LoginTime],
[Program],
[client_interface_name],
[Host],
[client_net_address],
[client_tcp_port],
[net_transport],
[net_packet_size],
[protocol_type],
[endpoint_id],
[encrypt_option],
[auth_scheme],
[num_reads],
[num_writes]
)
SELECT
DB_NAME(p.dbid) ,
s.login_name ,
nt_user_name,
s.login_time as LoginTime,
s.[program_name] as Program,
s.client_interface_name,
[host_name] as Host,
c.client_net_address,
c.client_tcp_port,
c.net_transport,
c.net_packet_size,
c.protocol_type,
c.endpoint_id,
c.encrypt_option,
c.auth_scheme,
c.num_reads,
c.num_writes
FROM sys.dm_exec_sessions s
inner join sys.sysprocesses p on
s.session_id = p.spid
inner join sys.dm_exec_connections c on
s.session_id = c.session_id
WHERE s.[program_name] not like 'Microsoft SQL Server Management Studio%'
    • 1
  2. Best Answer

    使用下面的事件会话，您可以获得有关已完成批次的信息。信息包括 database_id 和用户名：

    CREATE EVENT SESSION [QueryBatchTracking] ON SERVER 

ADD EVENT sqlserver.error_reported(
    ACTION(sqlserver.client_app_name,sqlserver.database_id,sqlserver.nt_username,sqlserver.session_id,sqlserver.username,sqlserver.sql_text)
    WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)))),

ADD EVENT sqlserver.rpc_completed(
    ACTION(sqlserver.client_app_name,sqlserver.database_id,sqlserver.nt_username,sqlserver.session_id,sqlserver.username,sqlserver.sql_text)
    WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)))),

ADD EVENT sqlserver.sql_statement_completed(
    ACTION(sqlserver.client_app_name,sqlserver.database_id,sqlserver.nt_username,sqlserver.session_id,sqlserver.username,sqlserver.sql_text)
    WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0))))

ADD TARGET package0.asynchronous_file_target(SET filename=N'd:\ExEvents\QueryBatchTracking.xet')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)
GO

ALTER EVENT SESSION QueryBatchTracking 
ON SERVER STATE = START 
GO

    除了仅获取登录事件之外，您还将获取有关用户执行的操作的信息。

    • 1

  3. 我想我的问题是为什么你需要知道用户正在连接哪些数据库？知道哪些用户正在登录服务器应该可以回答您对新服务器所需的所有问题，因为您无论如何都打算关闭整个旧服务器。一旦您知道连接到服务器的用户的登录名，您就可以通过SSMS中服务器级别的安全文件夹查看他们的映射数据库，并且大概您希望将您的用户映射到新服务器中的相同数据库。无需详细尝试记录正在连接的数据库。

    此外，如果用户连接到数据库 A，但运行一个从数据库 B 访问表的查询（因为跨数据库查询肯定是可能的），您想知道该用户使用了哪个数据库？例如：

    USE DatabaseA;

SELECT SomeColumn 
FROM DatabaseB.dbo.Table1

    以上运行在 DatabaseA 的上下文中。

    • 1

相关问题