How Can I Tell the Number of Times People Use SSMS to Change Client Data

Question

How Can I Tell the Number of Times People Use SSMS to Change Client Data

timthetraveler

Grasshopper

Points: 21
More actions
March 21, 2015 at 2:46 pm

#302329

Guys,
I'm trying to quantify the number of times folks use SQL Server Management Studio to change client data in one of our production databases. Does SQL Server keep this statistic? How do I get to this data?
Thanks in advance for your help.
Tim

Viewing 7 posts - 1 through 6 (of 6 total)

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

You would have to create an audit to capture this kind of data. It is not maintained otherwise.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 2

timthetraveler (3/21/2015)
Guys,
I'm trying to quantify the number of times folks use SQL Server Management Studio to change client data in one of our production databases. Does SQL Server keep this statistic? How do I get to this data?
Thanks in advance for your help.
Tim

Further on Jason's reply, here is an audit example:

😎

USE tempdb;

GO

SET NOCOUNT ON;

/*

Test environment for auditing schema objects

1. Audited table dbo.TBL_TEST_AUDIT

2. Audit table dbo.TBL_GENERIC_AUDIT

3. Modification Blocking trigger on dbo.TBL_GENERIC_AUDIT

4. Audit trigger on dbo.TBL_TEST_AUDIT

5. Session information function dbo.XUDF_GET_SESSION_CONNECTION_INFO

*/

IF OBJECT_ID('dbo.TBL_TEST_AUDIT') IS NOT NULL DROP TABLE dbo.TBL_TEST_AUDIT;

IF OBJECT_ID('dbo.TBL_GENERIC_AUDIT') IS NOT NULL DROP TABLE dbo.TBL_GENERIC_AUDIT;

IF OBJECT_ID('dbo.XUDF_GET_SESSION_CONNECTION_INFO') IS NOT NULL DROP FUNCTION dbo.XUDF_GET_SESSION_CONNECTION_INFO;

DECLARE @SQL_STR_001 NVARCHAR(MAX) = N'

CREATE FUNCTION dbo.XUDF_GET_SESSION_CONNECTION_INFO()

/* Returns user, session, execution and relevant system information for auditing and diagnostics/delbuging

*/

RETURNS XML

AS

BEGIN

RETURN

(

SELECT * FROM

(

SELECT

SCON.session_id

,SCON.most_recent_session_id

,SCON.connect_time

,SCON.net_transport

,SCON.protocol_type

,SCON.protocol_version

,SCON.endpoint_id

,SCON.encrypt_option

,SCON.auth_scheme

,SCON.node_affinity

,SCON.num_reads

,SCON.num_writes

,SCON.last_read

,SCON.last_write

,SCON.net_packet_size

,SCON.client_net_address

,SCON.client_tcp_port

,SCON.local_net_address

,SCON.local_tcp_port

,SCON.connection_id

,SCON.parent_connection_id

,SES.login_time

,SES.host_name

,SES.program_name

,SES.host_process_id

,SES.client_version

,SES.client_interface_name

,CONVERT(VARCHAR(100),SES.security_id,1) AS security_id

,SES.login_name

,SES.nt_domain

,SES.nt_user_name

,SES.status

,CONVERT(VARCHAR(100),SES.context_info,1) AS context_info

,SES.cpu_time

,SES.memory_usage

,SES.total_scheduled_time

,SES.total_elapsed_time

,SES.last_request_start_time

,SES.last_request_end_time

,SES.reads

,SES.writes

,SES.logical_reads

,SES.is_user_process

,SES.text_size

,SES.language

,SES.date_format

,SES.date_first

,SES.quoted_identifier

,SES.arithabort

,SES.ansi_null_dflt_on

,SES.ansi_defaults

,SES.ansi_warnings

,SES.ansi_padding

,SES.ansi_nulls

,SES.concat_null_yields_null

,SES.transaction_isolation_level

,SES.lock_timeout

,SES.deadlock_priority

,SES.row_count

,SES.prev_error

,CONVERT(VARCHAR(100),SES.original_security_id,1) AS original_security_id

,SES.original_login_name

,SES.last_successful_logon

,SES.last_unsuccessful_logon

,SES.unsuccessful_logons

,SES.group_id

,SES.database_id

,SES.authenticating_database_id

,SES.open_transaction_count

,CONVERT(VARCHAR(100),SCON.most_recent_sql_handle,1) AS most_recent_sql_handle

FROM sys.dm_exec_connections SCON

OUTER APPLY sys.dm_exec_sessions SES

WHERE SCON.session_id = @@SPID

AND SES.session_id = @@SPID

) AS RWLog

FOR XML AUTO

)

END

;

';

EXEC (@SQL_STR_001);

/*

Generic audit table

*/

CREATE TABLE dbo.TBL_GENERIC_AUDIT

(

AUDIT_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_GENERIC_AUDIT_AUDIT_ID PRIMARY KEY CLUSTERED WITH ( FILLFACTOR = 100, DATA_COMPRESSION = PAGE )

,AUDIT_TIMESTAMP DATETIME NOT NULL CONSTRAINT DFLT_DBO_TBL_GENERIC_AUDIT_AUDIT_TIMESTAMP DEFAULT ( GETDATE() )

,AUDIT_COL_IDENTITY INT NOT NULL

,AUDIT_ACTION_TYPE TINYINT NOT NULL

,AUDIT_SESSION_XML XML NOT NULL CONSTRAINT DFLT_DBO_TBL_GENERIC_AUDIT_AUDIT_SESSION_XML DEFAULT ( dbo.XUDF_GET_SESSION_CONNECTION_INFO() )

,AUDIT_XML XML NOT NULL

);

GO

CREATE TRIGGER dbo.TRG_AUDIT_TBL_GENERIC_AUDIT_READONLY ON dbo.TBL_GENERIC_AUDIT

INSTEAD OF UPDATE, DELETE

AS

BEGIN

INSERT INTO dbo.TBL_GENERIC_AUDIT (AUDIT_COL_IDENTITY,AUDIT_ACTION_TYPE,AUDIT_XML)

SELECT

D.AUDIT_ID

,ISNULL((SIGN(I.AUDIT_ID) * 127),255)

,( SELECT

CASE

WHEN I.AUDIT_ID IS NOT NULL THEN 3

ELSE 2

END AS '@Type'

,(

SELECT

AUDIT_ID

,AUDIT_TIMESTAMP

,AUDIT_COL_IDENTITY

,AUDIT_ACTION_TYPE

FROM inserted B

WHERE A.AUDIT_ID = B.AUDIT_ID

FOR XML PATH('INSERTED'), TYPE,ELEMENTS XSINIL

)

,(

SELECT

AUDIT_ID

,AUDIT_TIMESTAMP

,AUDIT_COL_IDENTITY

,AUDIT_ACTION_TYPE

FROM deleted C

WHERE A.AUDIT_ID = C.AUDIT_ID

FOR XML PATH('DELETED'), TYPE,ELEMENTS XSINIL

)

FROM deleted A

WHERE D.AUDIT_ID = A.AUDIT_ID

FOR XML PATH('ACTION'),TYPE,ELEMENTS XSINIL

)

FROM deleted D

LEFT OUTER JOIN inserted I

ON D.AUDIT_ID = I.AUDIT_ID;

END

GO

CREATE TABLE dbo.TBL_TEST_AUDIT

(

TEST_AUDIT_ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED

,TEST_AUDIT_COL_1 INT NOT NULL

,TEST_AUDIT_COL_2 VARCHAR(50) NOT NULL

,TEST_AUDIT_COL_3 NVARCHAR(500) NULL

);

GO

CREATE TRIGGER dbo.TRG_AUDIT_DBO_TBL_TEST_AUDIT

ON dbo.TBL_TEST_AUDIT

/* If any of the three actions does not require auditing

then remove it from here. Normally only Update and

Delete needs to be audited as the inserted data will

be un-touched.

*/

FOR INSERT, UPDATE, DELETE

AS

BEGIN

WITH IDENTITY_VALUES(ID_VAL, ACTION_TYPE) AS

(

SELECT

X.ID_VAL

,SUM(X.ACTION_TYPE) AS ACTION_TYPE

FROM

(

SELECT

I.TEST_AUDIT_ID AS ID_VAL

,CONVERT(TINYINT,1,0) AS ACTION_TYPE

FROM inserted I

UNION ALL

SELECT

D.TEST_AUDIT_ID AS ID_VAL

,CONVERT(TINYINT,2,0) AS ACTION_TYPE

FROM deleted D

) AS X

GROUP BY X.ID_VAL

)

INSERT INTO dbo.TBL_GENERIC_AUDIT (AUDIT_COL_IDENTITY,AUDIT_ACTION_TYPE,AUDIT_XML)

SELECT

X.ID_VAL

,X.ACTION_TYPE

,(

SELECT

X.ACTION_TYPE AS '@Type'

,(

SELECT

*

FROM inserted B

WHERE A.ID_VAL = B.TEST_AUDIT_ID

FOR XML PATH('INSERTED'), TYPE,ELEMENTS XSINIL

)

,(

SELECT

*

FROM deleted C

WHERE A.ID_VAL = C.TEST_AUDIT_ID

FOR XML PATH('DELETED'), TYPE,ELEMENTS XSINIL

)

FROM IDENTITY_VALUES A

WHERE X.ID_VAL = A.ID_VAL

FOR XML PATH('ACTION'),TYPE,ELEMENTS XSINIL

)

FROM IDENTITY_VALUES X

END

GO

/* Testin the objects, first the audit functionality and then the modification blocking

*/

INSERT INTO dbo.TBL_TEST_AUDIT (TEST_AUDIT_COL_1,TEST_AUDIT_COL_2,TEST_AUDIT_COL_3)

VALUES (10,'ABC',N'Despite such apparent rivalries and the huge sums each is investing in R&D, the bigger story is that they are co-operating to deliver 5G.')

,(20,'DEF',N'"That''s until 6G comes along in around 2040," Prof Tafazolli remarks')

,(30,'GHI',NULL)

,(40,'JKL',NULL)

,(50,'MNO',N'In South Korea, which spearheaded work on 4G, Samsung hopes to launch a temporary trial 5G network in time for 2018''s Winter Olympic Games.');