Audit, track database connections / logins to a table ?
-
Hello All,
Please help me in tracking the user activities on my DB.
1. who logged into a DB
2. when logged
3. when was the last connection
I would like to have audit to be recorded into a table.
I tried using DDL Triggers, but failing to connect the instance after.
Please help me in identifying this.
Thanks in advance.
- Win.
------------------------------------------------------------------------------------
Actually Mistakely I wrote this in 2000 forums. Please ignore.
I was trying to audit database logins using DDL Triggers.
Logon failed for the user 'sa' due to trigger execution.
I would like to capture the database logins and connections made.
Can anyone please help me in resolving this.
Cheers,
- Win." Have a great day "
-
if your looking for a long term audti then DDL trigger is a good appraoch , if this is a one time activity then you can run a trace on the db and perform the audit
the beloe link has an example specfic to logon audit
-
Thank you for your quick response.
I approached DDL Trigger but, after that am not able to login the instance, throwing error...
I need this for long term in our TrackerDB. Where we have 6000+ users for this database through different application.
Criteria:
Can I know/log/record the users/logins who are viewing/accessing my database. Is there any script to track these users.
Like , create a table with date/time stamp, account, and so.. Once a user access DB it needs to be stored into the table.
Thanks again.
Cheers,
- Win." Have a great day "
-
I applied DDL triggers but then failed to connect SQL Instance:
Logon failed for login ‘sa' due to trigger execution.
Changed database context to ‘master’.
Changed language setting to us_english. (Microsoft SQL Server, Error: 17892)
Cheers,
- Win." Have a great day "
-
Hi
Here is something which I do use ; It might not perfect for you but at least it will give you a starting point. In my case all user info stored in Authentication database and the you can pass the Database name as a parameter to check login history for each application.
use [Authentication]
declare @username as varchar(50)
declare @userFullName as varchar(50)
declare @application as varchar(50)
declare @DateFrom as datetime
Declare @innerUsername as varchar(50)
declare @loginCount as integer
declare @totalLoginCount as integer
declare @fromDateAsString as varchar(10)
declare @toDateAsString as varchar(10)
SET @innerUsername = '' --Initialise to ''
set @application = 'DatabaseName'
SET @DateFrom = '2011-01-01 00:00:00.000'
SET @loginCount = 0
SET @totalLoginCount = 0
SET @fromDateAsString = SUBSTRING(CONVERT(VarChar(50), @DateFrom , 111), 9, 2) + '/' +
SUBSTRING(CONVERT(VarChar(50), @DateFrom , 111), 6, 2) + '/' +
SUBSTRING(CONVERT(VarChar(50), @DateFrom , 111), 1, 4)
SET @toDateAsString = SUBSTRING(CONVERT(VarChar(50), GETDATE() , 111), 9, 2) + '/' +
SUBSTRING(CONVERT(VarChar(50), GETDATE() , 111), 6, 2) + '/' +
SUBSTRING(CONVERT(VarChar(50), GETDATE() , 111), 1, 4)
declare cuUserCURSOR cursor for
--lh.LogonDateTime,
Select lh.Username, u.FullName
--, count(lh.Username)
From dbo.LogonHistory lh
inner join dbo.tblUsers u on lh.Username = u.Username COLLATE SQL_Latin1_General_CP1_CI_AS
where lh.application = @application
and (lh.LogonDateTime > @DateFrom and lh.LogonDateTime < getdate())
order by lh.Username
Open cuUserCURSOR
--iterative fetch
fetch next from cuUserCURSOR into @username, @userFullName
while @@fetch_status = 0
begin
SET @totalLoginCount = @totalLoginCount + 1
SET @loginCount = @loginCount + 1
If (@innerUsername = '' or @innerUsername <> @username)
BEGIN
SET @innerUsername = @username
Print 'username: ' + @username + ', fullName: ' + @userFullName + ' ' + CAST(@loginCount as varchar(10))
SET @loginCount = 0
END
fetch next from cuUserCURSOR into @username, @userFullName
END
close cuUserCURSOR
deallocate cuUserCURSOR
Print ''
Print 'Total logins for period from ' + @fromDateAsString + ' to ' + @toDateAsString + ' = ' + cast(@totalLoginCount as varchar(10))
+ ' for Application ' + @application
cheers
ImI
-
Thanks Nadeem, appreciate your help...
I will definitely test this and get back to you.
Cheers,
- Win." Have a great day "
-
Your script is of cursor, will that eat my Server Memory, CPU... ?
Will this hit every second or only at logon ?
I used this script for tracking logons and it kicked with the error which is told earlier: Bu this is server level trigger. But my criteria is to record each and every logon on Database.
Criteria:
Can I know/log/record the users/logins who are viewing/accessing my database. Is there any script to track these users.
Like , create a table with date/time stamp, account, and so.. Once a user access DB it needs to be stored into the table.
-------------------------------------------------------------------------------------------
/***
Logging the connection info.
Here is the script to create a table to store the data and a DDL Logon trigger to capture this data.
This should be created in the master database.
***/
--1)--Create the Logon Trigger Trigger_ServerLogon
CREATE TRIGGER Trigger_ServerLogon
ON ALL SERVER FOR LOGON
AS
BEGIN
DECLARE @data XML
SET @data = EVENTDATA()
INSERT INTO dbo.ServerLogonHistory
SELECT @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(512)')
, @data.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime')
, @data.value('(/EVENT_INSTANCE/SPID)[1]', 'nvarchar(4)')
, @data.value('(/EVENT_INSTANCE/ServerName)[1]', 'nvarchar(512)')
, @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(512)')
, @data.value('(/EVENT_INSTANCE/LoginType)[1]', 'nvarchar(512)')
, @data.value('(/EVENT_INSTANCE/SID)[1]', 'nvarchar(512)')
, @data.value('(/EVENT_INSTANCE/ClientHost)[1]', 'nvarchar(512)')
, @data.value('(/EVENT_INSTANCE/IsPooled)[1]', 'nvarchar(1)')
END
GO
--2)--Create the dbo.ServerLogonHistory Table
CREATE TABLE dbo.ServerLogonHistory
(
EventType VARCHAR(512),
PostTime DATETIME,
SPID INT,
ServerName VARCHAR(512),
LoginName VARCHAR(512),
LoginType VARCHAR(512),
SID VARCHAR(512),
ClientHost VARCHAR(512),
IsPooled BIT
)
GO
--Grant insert rights to public for this table
GRANT INSERT ON dbo.ServerLogonHistory TO PUBLIC
-- Connect the Database instance, query the table
use <DB_Name>
select * from ServerLogonHistory
Cheers,
- Win." Have a great day "
-
win your triggers pretty close to mine, it just has few issues:
permissions: MOST users will not have access to that audit table you are inserting into...so the end result is YOU can login(with those sysadmin privs) , and they cannot, with some error like action ended in a trigger.
you didn't explicitly specifiy where the table would exist....you need to use a three part naming convention to say the table in master...otherwise even if they had permissions to that table in master, due to their default database context, the trigger might fail with object not found.
here's a trigger i've used.
note i've got TWO grants to public in it...one so that they have access to the trace table,and another to a sys view so i can log the IP address they are coming from
IMPORTANT: best practice is DO NOT CLOSE YOUR SSMS WINDOW THAT CREATES THE TRIGGER
keep it open, even for days if need be, until you are sure that the trigger is working...it makes it so much easier to disable the trigger fromt hat window, in cases where you malformed the trigger and it blocks you from logging in.
CREATE TABLE [master].[dbo].[TRACETABLE] (
[EVENTDATE] DATETIME NOT NULL,
[DBNAME] NVARCHAR(256) NULL,
[HOSTNAME] NVARCHAR(256) NULL,
[APPLICATIONNAME] NVARCHAR(256) NULL,
[PROCEDURENAME] NVARCHAR(256) NULL,
[USERID] SMALLINT NULL,
[USERNAME] NVARCHAR(256) NULL,
[SUSERID] INT NULL,
[SUSERNAME] NVARCHAR(256) NULL,
[IS_SERVERADMIN_SYSADMIN] INT NULL,
[IS_DB_OWNER] INT NULL,
[IS_DDL_ADMIN] INT NULL,
[IS_DB_DATAREADER] INT NULL,
[ORIGINAL_LOGIN] NVARCHAR(256) NULL,
[IPADDRESS] VARCHAR(48) NULL,
[AUTHENTICATIONTYPE] NVARCHAR(256) NULL)
GO
GRANT INSERT ON [master].[dbo].[TRACETABLE] TO PUBLIC
GRANT SELECT on [master].[sys].[dm_exec_connections]
GO
CREATE TRIGGER Logon_Trigger_Track_IP
ON ALL SERVER FOR LOGON
AS
BEGIN
INSERT INTO [master].[dbo].[TRACETABLE]
--the auditing snippet below works fine in a
--login trigger,
--database trigger
--or any stored procedure.
SELECT
getdate() AS EventDate,
DB_NAME() AS DBName,
HOST_NAME() AS HostName,
APP_NAME() AS ApplicationName,
OBJECT_NAME(@@PROCID) AS ProcedureName,
USER_ID() AS Userid,
USER_NAME() AS UserName,
SUSER_ID() AS sUserid,
SUSER_SNAME() AS sUserName,
IS_SRVROLEMEMBER ('sysadmin') AS [Is_ServerAdmin_Sysadmin],
IS_MEMBER('db_owner') AS [Is_DB_owner],
IS_MEMBER('db_ddladmin') AS [Is_DDL_Admin],
IS_MEMBER('db_datareader') AS [Is_DB_Datareader],
ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN],
client_net_address AS IPAddress,
auth_scheme AS AuthenticationType
FROM [master].[sys].[dm_exec_connections]
WHERE session_id = @@spid
END
GO
ENABLE TRIGGER [Logon_Trigger_Track_IP] ON ALL SERVER
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Thanks Lowell for your suggestions and response....
Iam affraid of using it, Iam sorry already I have an issue with my one of the DEV instances. Its not allowing me to logon after the trigger has been created.
Is there anyway to track a particular database connections using a Procedure or any source and logging into a table, where I can send as a weekend report to clients.
Its huge task to me to make that in more than 3000+ instances.
Initially I want this to be tested successfully in Local, DEV instances and need to get a sign to go ahead.
Thanks much for your help, and all who are looking into..
- Win.
Cheers,
- Win." Have a great day "
-
Hello Lowell..
Thanks for your help on auditing and for your query..
I tested that in my DEV environment, but its giving only MASTER database info alone.
I tried creating that in another database as well, but still it shows only Master database information only in DBName column of our table.
Could you please help me in sorting out that to get for all the DB info on any instance.
Thanks much for your help in advance.
Cheers,
- Win." Have a great day "
-
Can anyone look into this and help me in resolving ?
Thanks in advance.
Cheers,
- Win." Have a great day "
-
- Win. (7/25/2011)
Hello Lowell..Thanks for your help on auditing and for your query..
I tested that in my DEV environment, but its giving only MASTER database info alone.
I tried creating that in another database as well, but still it shows only Master database information only in DBName column of our table.
Could you please help me in sorting out that to get for all the DB info on any instance.
Thanks much for your help in advance.
the database name in question would be the default database of the user in question at the moment of logging in, if the connection string does not have the IntialCatalog/Database in the connection string. Otherwise it would be the database that comes from the connection string. How are you testing this?
once you've logged in, a person could connect to other databases, which is completely outside of the audit of a LOGON.
for the other part of what you are looking for, where you want to know who is using/touching a database, the only way to do that is with a trace. you'd need a DML trace to track which commands nad which database it was executed on.
what is the whole emphasis on the database that was used, and not what they do in the database?
maybe you should back up and say what you want to accomplish.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Thanks Lowell for your clarification. That was a great help done by you.
I want just to get the DB name and connection made on it, either applciation , or an adhoc query, loaded to our table.
Yes, this was tested in one instance and many of were already logged into the DB apart from my connection. Even from application hit also, it shows default master database.
The database is used for request center tool we used in our Organization. The task given was, "track the users hitting the Logger database with timestamp". Last week my mates failed to work on this. They used DDL which made one of our DEV servers filed to logon.
The query which you provided was exact to my needs, but I tried with multiple connections, but its still shows Master database alone with SQL Jobs, replication info. Confused, if am doing anything wrong. Please correct me.
Appreciate your help towards this.
Thanks much in advance.
Cheers,
- Win." Have a great day "
-
I checked TraceTable records, it logged 6844 connections in 24 hrs of time.
But only MASTER database details and in application name it shows only SQL Agent info.
as below...
SQLAgent - Step History Logger
Replication Distribution History
SQLAgent - Step History Logger
SQLAgent - Job Manager
other than these anyother way to test and check. Let me know if am failed some where as per our query and Trigger.
Cheers,
- Win." Have a great day "
-
for tracking DATABASE access, a logon triggers not really going to help you; I'd really suggest using a DML trace instead.
For that logon trigger,
for example, pick the top two users of that 6844 connections ;
go to SSMS and review their login...what is their DEFAULT database?
that, most likely, is what you are going to see for most of your logins...master. once they've connected, the database might be switched to something else...but that switching and additional access is outside /after that login trigger, and is not going to capture what you wanted.
here's a link to a prototype DML trace i like to use; it's a proc which toggles(creates/destroys) a DML trace. The advantage of this script is that it also is going to create a view in the master database to easily get the data in the trace itself.
now, once that view is in place and the trace is running, you can do some nice metrics like this:
SELECT * FROM (
select
row_number() OVER (PARTITION BY LoginName,DatabaseName ORDER BY EndTime) AS RW,
LoginName,
DatabaseName
from sp_DMLTrace
--first of this month
WHERE EndTime > DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
) MyAlias where RW = 1
this shows me every database the users accessed, and the view has the details if i need to dig into when/what time....it even has the actual commands they executed.
so from there, you might see the login "MyDomain\billy" connected to the PAYROLL database...then you could dig for the details and see what he did.
I think that is going to be a lot closer to what you want to do.
my trace is similar to the default trace...it's only keeping a limited amount of data...only 100 meg of data before it rolls over ...so you might need to adjust the size, or add additional filters on a busier system.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply