Re: Tracing sql server user logins

From: Dejan Sarka (dejan_please_reply_to_newsgroups.sarka@reproms.si)
Date: 07/31/02 

From: "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@reproms.si>
Date: Wed, 31 Jul 2002 15:45:38 +0200

Neil,

Create the table(s) to hold the output. Use Books OnLine to find out the
structure of the output of the procedures and use this structure for the
table. Then use Insert...Exec sp syntax to fill the table(s). Example:

CREATE TABLE dbo.sphelpuseroutput(
UserName sysname NULL,
GroupName sysname NULL,
LoginName sysname NULL,
DefDBName sysname NULL,
UserID smallint NULL,
SID smallint NULL)
GO

INSERT INTO dbo.sphelpuseroutput
 EXEC sp_helpuser
GO

SELECT *
  FROM dbo.sphelpuseroutput 

--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Neil Bailey" <neil.bailey@mkgeneral.nhs.uk> wrote in message
news:309101c23896$bf946ae0$a5e62ecf@tkmsftngxa07...
Many thanks, this is a good start. Is there a way to then
get this information to be inserted into a table as they
occur, I would be grateful for any code help if possible.
>-----Original Message-----
>Neil,
>
>You have to start auditing, SQL Server does not audit by
default. Base login
>auditing can be turned on with procedure:
>Right-click a server, and then click Properties.
>Under Audit level, select the level at which user
accesses to Microsoft® SQL
>ServerT are recorded in the SQL Server error log:
>None causes no auditing to be performed.
>Success causes only successful login attempts to be
audited.
>Failure causes only failed login attempts to be audited.
>All causes successful and failed login attempts to be
audited.
>More detailed info can be gathered with the Profiler.
>
>--
>Dejan Sarka, SQL Server MVP
>FAQ from Neil & others at: http://www.sqlserverfaq.com
>Please reply only to the newsgroups.
>PASS - the definitive, global community
>for SQL Server professionals - http://www.sqlpass.org
>
>"Neil Bailey" <neil.bailey@mkgeneral.nhs.uk> wrote in
message
>news:407001c23869$a65bc9b0$35ef2ecf@TKMSFTNGXA11...
>> Does anybody know how to query SQL server (7 & 2000) to
>> find the last time a user logged in.
>
>
>.
>

Relevant Pages

  • Re: Tracing sql server user logins
    ... Now for your question: base auditing ... > GroupName sysname NULL, ... SQL Server does not audit by ... >>Failure causes only failed login attempts to be audited. ...
    (microsoft.public.sqlserver.security)
  • Re: Failure Audit Event ID: 18456 SQL Server Error
    ... you can always turn off failed login audit. ... "Anand Ganesh" wrote: ... The server has a public domain name. ... I am getting the following error in MS SQL SERVER 2005 Enterprise ...
    (microsoft.public.sqlserver.connect)
  • Re: Failure Audit Event ID: 18456 SQL Server Error
    ... you can always turn off failed login audit. ... "Anand Ganesh" wrote: ... The server has a public domain name. ... I am getting the following error in MS SQL SERVER 2005 Enterprise ...
    (microsoft.public.sqlserver.connect)
  • Re: failed login attempts
    ... but how do you setup profiler to return the PC name or IP ... I have used network monitor and setup a pattern match, which works, ... Microsoft SQL Server doesn't have built-in failed logins auditing ... >> support, including failed login attempts. ...
    (microsoft.public.sqlserver.security)
  • Re: How trace logon failed
    ... You'd have to capture the host name (which may not ... >> SQL Server supports four kinds of Audit levels to record the user accesses ... All - successful and failed login attempts are both audited. ...
    (microsoft.public.sqlserver.security)