February 18, 2003 at 9:16 am
Hi.
I want to be able to audit users connection in my sql server database, so I can realize which logins are never used.
Is there a way to do it with sqlserver 6.5?
Can somebody help me?
Best Regards
February 18, 2003 at 11:10 am
You can run a trace and then back track through the data. There isn't a good way to do this in SQL Server (yet)
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
February 18, 2003 at 11:47 am
how I do it:
for the users you think they do not access =>>>>>>>>take them out from the database(maintain them in security just in case)
If nobody cries, then eliminate the user.
February 18, 2003 at 1:32 pm
Run SQL Enterprise Manager from the Microsoft SQL Server 6.5 group
Select the server that is going to be managed and, from the Server menu, select SQL Server->Configure.
Then on the new dialog change to the Security Options tab.
To track the successful and unsuccessful login attempts of your users, enable, or check, the options in the Audit Level group box.
February 19, 2003 at 7:41 am
a question on the same line...I have been trying to do the same exact thing
When I turn the audit level do I have to restart the server??
Also where does the audit detail get saved for me to analyse the results?
VR
February 19, 2003 at 8:18 am
Yes you must restart the SQL Server service.
I do believe they are in SQL Server Logs but don't have my documentation here for reference.
February 20, 2003 at 7:41 am
Yes, they appear in the SQL Server Logs
February 20, 2003 at 8:35 am
Another option for SQL 7.0/2000...
Log Explorer can be configured to save login session information to a table in a database which you specify. I believe it uses Trace functions to accomplish this.
February 23, 2003 at 9:20 pm
I am not too familiar with SQL 6.5, so the following information only applies to SQL 7 and SQL 2000. It might work for SQL 6.5. I just don't have time to test it.
I have encountered similar issues in my previous position. Not only do I have many logins that need to be cleaned up; I also had tons of other orphaned objects that need to be cleaned, such as databases and DTS packages.
You can audit database logins using Profiler or SQL Trace, which was discussed in one of my articles ( http://qa.sqlservercentral.com/columnists/hji/trace.asp ). I will not repeat it here.
Another approach you can use is to periodically record sp_who2 results in a table. Once you collected these results over 1 or 2 weeks of time, you should be able to remove the logins that were not used during that period.
Use the following scripts to create the table and the stored procedure. After the table and stored procedure is created, you can schedule the stored procedure to run every 10 or 15 minutes for 1 or 2 weeks (1 week should be enough). I used this approach myself and have cleaned dozen of orphaned logins on several servers successfully.
I am planning to write an article on how to get rid of orphaned objects, so stay tuned.
Hope this helps.
Haidong
http://qa.sqlservercentral.com/columnists/hji/
/****** Object: Stored Procedure dbo.usp_Trace_SP_WHO2 Script Date: 2/23/2003 9:41:06 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_Trace_SP_WHO2]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_Trace_SP_WHO2]
GO
/****** Object: Table [dbo].[SP_WHO2_Trace] Script Date: 2/23/2003 9:41:06 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_WHO2_Trace]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SP_WHO2_Trace]
GO
/****** Object: Table [dbo].[SP_WHO2_Trace] Script Date: 2/23/2003 9:41:08 PM ******/
CREATE TABLE [dbo].[SP_WHO2_Trace] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[spid] [int] NOT NULL ,
[status] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[login] [sysname] NULL ,
[hostname] [sysname] NULL ,
[blkby] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dbname] [sysname] NULL ,
[command] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cputime] [int] NULL ,
[diskio] [int] NULL ,
[lastbatch] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[programname] [sysname] NULL ,
[spid2] [int] NULL ,
[Trace_Time] [datetime] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SP_WHO2_Trace] WITH NOCHECK ADD
CONSTRAINT [DF_TTRACE_DATE] DEFAULT (getdate()) FOR [Trace_Time]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/****** Object: Stored Procedure dbo.usp_Trace_SP_WHO2 Script Date: 2/23/2003 9:41:09 PM ******/
CREATE PROCEDURE usp_Trace_SP_WHO2 AS
/*
Author: Haidong Ji
Date: 1/21/2003
Purpose: Trace and record SP_WHO2 results into the SP_WHO2_Trace table. The results will be invaluable
for future debugging, performence tuning, and trend analysis.
*/
SET NOCOUNT ON
/* Create a temp table to hold sp_who2 information */
IF EXISTS (SELECT * FROM tempdb..sysobjects
WHERE id = object_id(N'[tempdb]..[#tmp_who2]'))
DROP TABLE #tmp_who2
CREATE TABLE #tmp_who2 (
spid INTEGER NULL
,status VARCHAR(100) NULL
,login SYSNAME NULL
,hostname SYSNAME NULL
,blkby VARCHAR(10) NULL
,dbname SYSNAME NULL
,command VARCHAR(100) NULL
,cputime INTEGER NULL
,diskio INTEGER NULL
,lastbatch VARCHAR(50) NULL
,programname SYSNAME NULL
,spid2 INTEGER NULL
)
/* Populate #tmp_who2 with sp_who2 data */
INSERT INTO #tmp_who2
EXEC dbo.sp_Who2
/* Populate SP_WHO2_Trace with sp_who2 data. SP_WHO2_Trace will have an identity field and time stamp */
INSERT INTO SP_WHO2_Trace
(spid, status, login, hostname, blkby, dbname, command, cputime, diskio, lastbatch, programname, spid2)
SELECT spid, status, login, hostname, blkby, dbname, command, cputime, diskio, lastbatch, programname, spid2
FROM #tmp_who2
/* Clean up. Drop the #tmp_who2 table */
DROP TABLE #tmp_who2
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
February 24, 2003 at 2:33 am
I would definitly vote for Profiler (or SQLTrace) where you only have to select the "Audit Login" and/or "Audit Logout" event classes.
You can save the result the a file or SQL table.
I wouldn't opt for the sp_who2 approach because you cannot see if somebody was connected and disconnected between 2 runs
Bye
Gabor
February 24, 2003 at 4:46 am
Ok, both SQL 7 and SQL 2000 have the option built in to audit logins in the Server properties. The only reason not to use those is if you do not want to monitor for an extended period of time, need to audit logoffs or cannot stop the server to add or remove the option. In that case use profiler to perform a trace on the local box.
To setup Login Audit SQL 7/2000:
To setup open EM, drill to the SQL Server in question.
Right click the Server and choose Connection Properties.
Choose the Security Tab and set the Audit Level (See BOL for more details).
Press OK then bounce server.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply