How to determine the last access date of a DB?

(SQL Server 2000, SP3a)
Hello all!
In my company, we have several development servers that, over time, get cluttered with a
*lot* of "scratch" databases. Is there some inherent mechanism that will show the last
time a database was accessed? If not, is there a relatively simple SQL-based solution
that I could craft that could help track that?
Thanks for any help you can provide!
John Peterson
You have to do some server level settings for that.
SQL Server can log event information for logon attempts and you can view it
by reviewing the errorlog. By turning on the
auditing level of SQL Server.
follow these steps to enable auditing of all/successfull connections with
Enterprise Manager in SQL Server:
Expand a server group.
Right-click a server, and then click Properties.
On the Security tab, under Audit Level, click all/success etc(required
option).
You must stop and restart the server for this setting to take effect.
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
|||Login attempts won't tell you if a database has been used or
not. It will only tell you about server logins, not database
access. You would need to monitor this with profiler
going forward. I don't think there is any direct way to get
historical usage if you haven't been monitoring for such.
I suppose you could always drop the databases and then see
who calls complaining to determine if the databases are
being used. And you could use whatever criteria in
determining if you actually backup the databases before
dropping them. That could make for an interesting work day.
-Sue
On Thu, 8 Jul 2004 07:04:50 +0530, "Vishal Parkar"
<REMOVE_THIS_vgparkar@.yahoo.co.in> wrote:

>You have to do some server level settings for that.
>SQL Server can log event information for logon attempts and you can view it
>by reviewing the errorlog. By turning on the
>auditing level of SQL Server.
>follow these steps to enable auditing of all/successfull connections with
>Enterprise Manager in SQL Server:
>Expand a server group.
>Right-click a server, and then click Properties.
>On the Security tab, under Audit Level, click all/success etc(required
>option).
>You must stop and restart the server for this setting to take effect.
|||Thanks guys! Yeah...if I could put a trigger or something on sysprocesses, I could
potentially log access to specific databases. I suppose I could have a "monitor" wake up
every X seconds and read the distinct databases that were in use -- but that seems kind of
heavy handed...
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:4kdpe010faeumj5c62626q6oo83iegva7m@.4ax.com...
> Login attempts won't tell you if a database has been used or
> not. It will only tell you about server logins, not database
> access. You would need to monitor this with profiler
> going forward. I don't think there is any direct way to get
> historical usage if you haven't been monitoring for such.
> I suppose you could always drop the databases and then see
> who calls complaining to determine if the databases are
> being used. And you could use whatever criteria in
> determining if you actually backup the databases before
> dropping them. That could make for an interesting work day.
> -Sue
> On Thu, 8 Jul 2004 07:04:50 +0530, "Vishal Parkar"
> <REMOVE_THIS_vgparkar@.yahoo.co.in> wrote:
>
|||Triggers on system tables isn't supported though and
sysprocesses is only materialized when used so there
wouldn't even be something to put a trigger on.
-Sue
On Wed, 7 Jul 2004 21:52:56 -0700, "John Peterson"
<j0hnp@.comcast.net> wrote:

>Thanks guys! Yeah...if I could put a trigger or something on sysprocesses, I could
>potentially log access to specific databases. I suppose I could have a "monitor" wake up
>every X seconds and read the distinct databases that were in use -- but that seems kind of
>heavy handed...
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:4kdpe010faeumj5c62626q6oo83iegva7m@.4ax.com.. .
>
|||Understood. How about reading database logs? Would *that* show any activity? How would
I even go about doing that?
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:q4eqe0htf3hbgg1hme377t5ro5gpcvs351@.4ax.com... [vbcol=seagreen]
> Triggers on system tables isn't supported though and
> sysprocesses is only materialized when used so there
> wouldn't even be something to put a trigger on.
> -Sue
> On Wed, 7 Jul 2004 21:52:56 -0700, "John Peterson"
> <j0hnp@.comcast.net> wrote:
up[vbcol=seagreen]
of
>
|||Well...I noodled on using DBCC LOG to try and see if any of that information might be
germane, but I don't think so (everything is "reset" on a restart of SQL Server).
Then I looked at sysobjects.refdate, but that didn't pan out either.
Is there *any* way to determine the date/time that a particular table was last affected by
an INSERT/UPDATE/DELETE?
There seems to be a similar question here:
http://www.experts-exchange.com/Data..._20684794.html
But I don't want to sign up just to find out I *can't* do this... ;-)
Thanks for any help you can provide! :-)
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:%23Cd7QnPZEHA.2944@.TK2MSFTNGP11.phx.gbl...
> Understood. How about reading database logs? Would *that* show any activity? How
would[vbcol=seagreen]
> I even go about doing that?
>
> "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:q4eqe0htf3hbgg1hme377t5ro5gpcvs351@.4ax.com...
wake[vbcol=seagreen]
> up
kind
> of
>
|||The SQL Server logs...not reliably. Unless the databases
have errors all the time that get logged. In terms of the
database log, I don't think you can't get timestamps using
dbcc loginfo or dbcc log. You can check object creation
dates in the database but that won't necessarily mean the
database isn't being used if no one is creating objects. And
even with the logs, if for some reason, they are only
selecting out of the database, that wouldn't do you any good
either.
If you want to read details of log files, you need to use
something like Log Explorer from lumigent -
www.lumigent.com
-Sue
On Thu, 8 Jul 2004 07:42:36 -0700, "John Peterson"
<j0hnp@.comcast.net> wrote:

>Understood. How about reading database logs? Would *that* show any activity? How would
>I even go about doing that?
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:q4eqe0htf3hbgg1hme377t5ro5gpcvs351@.4ax.com.. .
>up
>of
>

How to determine the last access date of a DB?

(SQL Server 2000, SP3a)
Hello all!
In my company, we have several development servers that, over time, get clut
tered with a
*lot* of "scratch" databases. Is there some inherent mechanism that will sh
ow the last
time a database was accessed? If not, is there a relatively simple SQL-base
d solution
that I could craft that could help track that?
Thanks for any help you can provide!
John PetersonYou have to do some server level settings for that.
SQL Server can log event information for logon attempts and you can view it
by reviewing the errorlog. By turning on the
auditing level of SQL Server.
follow these steps to enable auditing of all/successfull connections with
Enterprise Manager in SQL Server:
Expand a server group.
Right-click a server, and then click Properties.
On the Security tab, under Audit Level, click all/success etc(required
option).
You must stop and restart the server for this setting to take effect.
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com|||Login attempts won't tell you if a database has been used or
not. It will only tell you about server logins, not database
access. You would need to monitor this with profiler
going forward. I don't think there is any direct way to get
historical usage if you haven't been monitoring for such.
I suppose you could always drop the databases and then see
who calls complaining to determine if the databases are
being used. And you could use whatever criteria in
determining if you actually backup the databases before
dropping them. That could make for an interesting work day.
-Sue
On Thu, 8 Jul 2004 07:04:50 +0530, "Vishal Parkar"
<REMOVE_THIS_vgparkar@.yahoo.co.in> wrote:

>You have to do some server level settings for that.
>SQL Server can log event information for logon attempts and you can view it
>by reviewing the errorlog. By turning on the
>auditing level of SQL Server.
>follow these steps to enable auditing of all/successfull connections with
>Enterprise Manager in SQL Server:
>Expand a server group.
>Right-click a server, and then click Properties.
>On the Security tab, under Audit Level, click all/success etc(required
>option).
>You must stop and restart the server for this setting to take effect.|||Thanks guys! Yeah...if I could put a trigger or something on sysprocesses,
I could
potentially log access to specific databases. I suppose I could have a "mon
itor" wake up
every X seconds and read the distinct databases that were in use -- but that
seems kind of
heavy handed...
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:4kdpe010faeumj5c62626q6oo83iegva7m@.
4ax.com...
> Login attempts won't tell you if a database has been used or
> not. It will only tell you about server logins, not database
> access. You would need to monitor this with profiler
> going forward. I don't think there is any direct way to get
> historical usage if you haven't been monitoring for such.
> I suppose you could always drop the databases and then see
> who calls complaining to determine if the databases are
> being used. And you could use whatever criteria in
> determining if you actually backup the databases before
> dropping them. That could make for an interesting work day.
> -Sue
> On Thu, 8 Jul 2004 07:04:50 +0530, "Vishal Parkar"
> <REMOVE_THIS_vgparkar@.yahoo.co.in> wrote:
>
>|||Triggers on system tables isn't supported though and
sysprocesses is only materialized when used so there
wouldn't even be something to put a trigger on.
-Sue
On Wed, 7 Jul 2004 21:52:56 -0700, "John Peterson"
<j0hnp@.comcast.net> wrote:

>Thanks guys! Yeah...if I could put a trigger or something on sysprocesses,
I could
>potentially log access to specific databases. I suppose I could have a "mo
nitor" wake up
>every X seconds and read the distinct databases that were in use -- but tha
t seems kind of
>heavy handed...
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:4kdpe010faeumj5c62626q6oo83iegva7m@.
4ax.com...
>|||Understood. How about reading database logs? Would *that* show any activit
y? How would
I even go about doing that?
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:q4eqe0htf3hbgg1hme377t5ro5gpcvs351@.
4ax.com...
> Triggers on system tables isn't supported though and
> sysprocesses is only materialized when used so there
> wouldn't even be something to put a trigger on.
> -Sue
> On Wed, 7 Jul 2004 21:52:56 -0700, "John Peterson"
> <j0hnp@.comcast.net> wrote:
>
up[vbcol=seagreen]
of[vbcol=seagreen]
>|||Well...I noodled on using DBCC LOG to try and see if any of that information
might be
germane, but I don't think so (everything is "reset" on a restart of SQL Ser
ver).
Then I looked at sysobjects.refdate, but that didn't pan out either.
Is there *any* way to determine the date/time that a particular table was la
st affected by
an INSERT/UPDATE/DELETE?
There seems to be a similar question here:
[url]http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20684794.html[/ur
l]
But I don't want to sign up just to find out I *can't* do this... ;-)
Thanks for any help you can provide! :-)
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:%23Cd7QnPZEHA.2944@.TK2MSFTNGP11.phx.gbl...
> Understood. How about reading database logs? Would *that* show any activity? Ho
w
would
> I even go about doing that?
>
> "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:q4eqe0htf3hbgg1hme377t5ro5gpcvs351@.
4ax.com...
wake[vbcol=seagreen]
> up
kind[vbcol=seagreen]
> of
>|||The SQL Server logs...not reliably. Unless the databases
have errors all the time that get logged. In terms of the
database log, I don't think you can't get timestamps using
dbcc loginfo or dbcc log. You can check object creation
dates in the database but that won't necessarily mean the
database isn't being used if no one is creating objects. And
even with the logs, if for some reason, they are only
selecting out of the database, that wouldn't do you any good
either.
If you want to read details of log files, you need to use
something like Log Explorer from lumigent -
www.lumigent.com
-Sue
On Thu, 8 Jul 2004 07:42:36 -0700, "John Peterson"
<j0hnp@.comcast.net> wrote:

>Understood. How about reading database logs? Would *that* show any activi
ty? How would
>I even go about doing that?
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:q4eqe0htf3hbgg1hme377t5ro5gpcvs351@.
4ax.com...
>up
>of
>

How to determine the last access date of a DB?

(SQL Server 2000, SP3a)
Hello all!
In my company, we have several development servers that, over time, get cluttered with a
*lot* of "scratch" databases. Is there some inherent mechanism that will show the last
time a database was accessed? If not, is there a relatively simple SQL-based solution
that I could craft that could help track that?
Thanks for any help you can provide!
John PetersonYou have to do some server level settings for that.
SQL Server can log event information for logon attempts and you can view it
by reviewing the errorlog. By turning on the
auditing level of SQL Server.
follow these steps to enable auditing of all/successfull connections with
Enterprise Manager in SQL Server:
Expand a server group.
Right-click a server, and then click Properties.
On the Security tab, under Audit Level, click all/success etc(required
option).
You must stop and restart the server for this setting to take effect.
--
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com|||Login attempts won't tell you if a database has been used or
not. It will only tell you about server logins, not database
access. You would need to monitor this with profiler
going forward. I don't think there is any direct way to get
historical usage if you haven't been monitoring for such.
I suppose you could always drop the databases and then see
who calls complaining to determine if the databases are
being used. And you could use whatever criteria in
determining if you actually backup the databases before
dropping them. That could make for an interesting work day.
-Sue
On Thu, 8 Jul 2004 07:04:50 +0530, "Vishal Parkar"
<REMOVE_THIS_vgparkar@.yahoo.co.in> wrote:
>You have to do some server level settings for that.
>SQL Server can log event information for logon attempts and you can view it
>by reviewing the errorlog. By turning on the
>auditing level of SQL Server.
>follow these steps to enable auditing of all/successfull connections with
>Enterprise Manager in SQL Server:
>Expand a server group.
>Right-click a server, and then click Properties.
>On the Security tab, under Audit Level, click all/success etc(required
>option).
>You must stop and restart the server for this setting to take effect.|||Thanks guys! Yeah...if I could put a trigger or something on sysprocesses, I could
potentially log access to specific databases. I suppose I could have a "monitor" wake up
every X seconds and read the distinct databases that were in use -- but that seems kind of
heavy handed...
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:4kdpe010faeumj5c62626q6oo83iegva7m@.4ax.com...
> Login attempts won't tell you if a database has been used or
> not. It will only tell you about server logins, not database
> access. You would need to monitor this with profiler
> going forward. I don't think there is any direct way to get
> historical usage if you haven't been monitoring for such.
> I suppose you could always drop the databases and then see
> who calls complaining to determine if the databases are
> being used. And you could use whatever criteria in
> determining if you actually backup the databases before
> dropping them. That could make for an interesting work day.
> -Sue
> On Thu, 8 Jul 2004 07:04:50 +0530, "Vishal Parkar"
> <REMOVE_THIS_vgparkar@.yahoo.co.in> wrote:
> >You have to do some server level settings for that.
> >
> >SQL Server can log event information for logon attempts and you can view it
> >by reviewing the errorlog. By turning on the
> >auditing level of SQL Server.
> >
> >follow these steps to enable auditing of all/successfull connections with
> >Enterprise Manager in SQL Server:
> >
> >Expand a server group.
> >Right-click a server, and then click Properties.
> >On the Security tab, under Audit Level, click all/success etc(required
> >option).
> >
> >You must stop and restart the server for this setting to take effect.
>|||Triggers on system tables isn't supported though and
sysprocesses is only materialized when used so there
wouldn't even be something to put a trigger on.
-Sue
On Wed, 7 Jul 2004 21:52:56 -0700, "John Peterson"
<j0hnp@.comcast.net> wrote:
>Thanks guys! Yeah...if I could put a trigger or something on sysprocesses, I could
>potentially log access to specific databases. I suppose I could have a "monitor" wake up
>every X seconds and read the distinct databases that were in use -- but that seems kind of
>heavy handed...
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:4kdpe010faeumj5c62626q6oo83iegva7m@.4ax.com...
>> Login attempts won't tell you if a database has been used or
>> not. It will only tell you about server logins, not database
>> access. You would need to monitor this with profiler
>> going forward. I don't think there is any direct way to get
>> historical usage if you haven't been monitoring for such.
>> I suppose you could always drop the databases and then see
>> who calls complaining to determine if the databases are
>> being used. And you could use whatever criteria in
>> determining if you actually backup the databases before
>> dropping them. That could make for an interesting work day.
>> -Sue
>> On Thu, 8 Jul 2004 07:04:50 +0530, "Vishal Parkar"
>> <REMOVE_THIS_vgparkar@.yahoo.co.in> wrote:
>> >You have to do some server level settings for that.
>> >
>> >SQL Server can log event information for logon attempts and you can view it
>> >by reviewing the errorlog. By turning on the
>> >auditing level of SQL Server.
>> >
>> >follow these steps to enable auditing of all/successfull connections with
>> >Enterprise Manager in SQL Server:
>> >
>> >Expand a server group.
>> >Right-click a server, and then click Properties.
>> >On the Security tab, under Audit Level, click all/success etc(required
>> >option).
>> >
>> >You must stop and restart the server for this setting to take effect.
>|||Understood. How about reading database logs? Would *that* show any activity? How would
I even go about doing that?
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:q4eqe0htf3hbgg1hme377t5ro5gpcvs351@.4ax.com...
> Triggers on system tables isn't supported though and
> sysprocesses is only materialized when used so there
> wouldn't even be something to put a trigger on.
> -Sue
> On Wed, 7 Jul 2004 21:52:56 -0700, "John Peterson"
> <j0hnp@.comcast.net> wrote:
> >Thanks guys! Yeah...if I could put a trigger or something on sysprocesses, I could
> >potentially log access to specific databases. I suppose I could have a "monitor" wake
up
> >every X seconds and read the distinct databases that were in use -- but that seems kind
of
> >heavy handed...
> >
> >
> >"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> >news:4kdpe010faeumj5c62626q6oo83iegva7m@.4ax.com...
> >> Login attempts won't tell you if a database has been used or
> >> not. It will only tell you about server logins, not database
> >> access. You would need to monitor this with profiler
> >> going forward. I don't think there is any direct way to get
> >> historical usage if you haven't been monitoring for such.
> >> I suppose you could always drop the databases and then see
> >> who calls complaining to determine if the databases are
> >> being used. And you could use whatever criteria in
> >> determining if you actually backup the databases before
> >> dropping them. That could make for an interesting work day.
> >>
> >> -Sue
> >>
> >> On Thu, 8 Jul 2004 07:04:50 +0530, "Vishal Parkar"
> >> <REMOVE_THIS_vgparkar@.yahoo.co.in> wrote:
> >>
> >> >You have to do some server level settings for that.
> >> >
> >> >SQL Server can log event information for logon attempts and you can view it
> >> >by reviewing the errorlog. By turning on the
> >> >auditing level of SQL Server.
> >> >
> >> >follow these steps to enable auditing of all/successfull connections with
> >> >Enterprise Manager in SQL Server:
> >> >
> >> >Expand a server group.
> >> >Right-click a server, and then click Properties.
> >> >On the Security tab, under Audit Level, click all/success etc(required
> >> >option).
> >> >
> >> >You must stop and restart the server for this setting to take effect.
> >>
> >
>|||Well...I noodled on using DBCC LOG to try and see if any of that information might be
germane, but I don't think so (everything is "reset" on a restart of SQL Server).
Then I looked at sysobjects.refdate, but that didn't pan out either.
Is there *any* way to determine the date/time that a particular table was last affected by
an INSERT/UPDATE/DELETE?
There seems to be a similar question here:
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20684794.html
But I don't want to sign up just to find out I *can't* do this... ;-)
Thanks for any help you can provide! :-)
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:%23Cd7QnPZEHA.2944@.TK2MSFTNGP11.phx.gbl...
> Understood. How about reading database logs? Would *that* show any activity? How
would
> I even go about doing that?
>
> "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:q4eqe0htf3hbgg1hme377t5ro5gpcvs351@.4ax.com...
> > Triggers on system tables isn't supported though and
> > sysprocesses is only materialized when used so there
> > wouldn't even be something to put a trigger on.
> >
> > -Sue
> >
> > On Wed, 7 Jul 2004 21:52:56 -0700, "John Peterson"
> > <j0hnp@.comcast.net> wrote:
> >
> > >Thanks guys! Yeah...if I could put a trigger or something on sysprocesses, I could
> > >potentially log access to specific databases. I suppose I could have a "monitor"
wake
> up
> > >every X seconds and read the distinct databases that were in use -- but that seems
kind
> of
> > >heavy handed...
> > >
> > >
> > >"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> > >news:4kdpe010faeumj5c62626q6oo83iegva7m@.4ax.com...
> > >> Login attempts won't tell you if a database has been used or
> > >> not. It will only tell you about server logins, not database
> > >> access. You would need to monitor this with profiler
> > >> going forward. I don't think there is any direct way to get
> > >> historical usage if you haven't been monitoring for such.
> > >> I suppose you could always drop the databases and then see
> > >> who calls complaining to determine if the databases are
> > >> being used. And you could use whatever criteria in
> > >> determining if you actually backup the databases before
> > >> dropping them. That could make for an interesting work day.
> > >>
> > >> -Sue
> > >>
> > >> On Thu, 8 Jul 2004 07:04:50 +0530, "Vishal Parkar"
> > >> <REMOVE_THIS_vgparkar@.yahoo.co.in> wrote:
> > >>
> > >> >You have to do some server level settings for that.
> > >> >
> > >> >SQL Server can log event information for logon attempts and you can view it
> > >> >by reviewing the errorlog. By turning on the
> > >> >auditing level of SQL Server.
> > >> >
> > >> >follow these steps to enable auditing of all/successfull connections with
> > >> >Enterprise Manager in SQL Server:
> > >> >
> > >> >Expand a server group.
> > >> >Right-click a server, and then click Properties.
> > >> >On the Security tab, under Audit Level, click all/success etc(required
> > >> >option).
> > >> >
> > >> >You must stop and restart the server for this setting to take effect.
> > >>
> > >
> >
>|||The SQL Server logs...not reliably. Unless the databases
have errors all the time that get logged. In terms of the
database log, I don't think you can't get timestamps using
dbcc loginfo or dbcc log. You can check object creation
dates in the database but that won't necessarily mean the
database isn't being used if no one is creating objects. And
even with the logs, if for some reason, they are only
selecting out of the database, that wouldn't do you any good
either.
If you want to read details of log files, you need to use
something like Log Explorer from lumigent -
www.lumigent.com
-Sue
On Thu, 8 Jul 2004 07:42:36 -0700, "John Peterson"
<j0hnp@.comcast.net> wrote:
>Understood. How about reading database logs? Would *that* show any activity? How would
>I even go about doing that?
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:q4eqe0htf3hbgg1hme377t5ro5gpcvs351@.4ax.com...
>> Triggers on system tables isn't supported though and
>> sysprocesses is only materialized when used so there
>> wouldn't even be something to put a trigger on.
>> -Sue
>> On Wed, 7 Jul 2004 21:52:56 -0700, "John Peterson"
>> <j0hnp@.comcast.net> wrote:
>> >Thanks guys! Yeah...if I could put a trigger or something on sysprocesses, I could
>> >potentially log access to specific databases. I suppose I could have a "monitor" wake
>up
>> >every X seconds and read the distinct databases that were in use -- but that seems kind
>of
>> >heavy handed...
>> >
>> >
>> >"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>> >news:4kdpe010faeumj5c62626q6oo83iegva7m@.4ax.com...
>> >> Login attempts won't tell you if a database has been used or
>> >> not. It will only tell you about server logins, not database
>> >> access. You would need to monitor this with profiler
>> >> going forward. I don't think there is any direct way to get
>> >> historical usage if you haven't been monitoring for such.
>> >> I suppose you could always drop the databases and then see
>> >> who calls complaining to determine if the databases are
>> >> being used. And you could use whatever criteria in
>> >> determining if you actually backup the databases before
>> >> dropping them. That could make for an interesting work day.
>> >>
>> >> -Sue
>> >>
>> >> On Thu, 8 Jul 2004 07:04:50 +0530, "Vishal Parkar"
>> >> <REMOVE_THIS_vgparkar@.yahoo.co.in> wrote:
>> >>
>> >> >You have to do some server level settings for that.
>> >> >
>> >> >SQL Server can log event information for logon attempts and you can view it
>> >> >by reviewing the errorlog. By turning on the
>> >> >auditing level of SQL Server.
>> >> >
>> >> >follow these steps to enable auditing of all/successfull connections with
>> >> >Enterprise Manager in SQL Server:
>> >> >
>> >> >Expand a server group.
>> >> >Right-click a server, and then click Properties.
>> >> >On the Security tab, under Audit Level, click all/success etc(required
>> >> >option).
>> >> >
>> >> >You must stop and restart the server for this setting to take effect.
>> >>
>> >
>sql

How to determine if a Windows Auth ID is no longer valid in SQL?

How can we proactively find out when a Windows Authentication ID in SQL
Server is no longer valid, i.e. an employee is no longer with the company so
we should delete their related Windows Authentication user IDs from our SQL
Server databases?Don't you have an employee table that lists the AD login and whether the
employee is active or not? Some companies store this data directly in AD.
You need to find out where the information is stored though, before we can
tell you how to fix your issue.
Another question...are they disabling the AD account? If you were using AD
groups, instead of individual logins, you would no longer have to worry abou
t
an individuals access. It's also easier to manage and delegate management
responsibility to the business and HR.
MeanOldDBA
derrickleggett@.hotmail.com
http://weblogs.sqlteam.com/derrickl
When life gives you a lemon, fire the DBA.
"Stephanie" wrote:

> How can we proactively find out when a Windows Authentication ID in SQL
> Server is no longer valid, i.e. an employee is no longer with the company
so
> we should delete their related Windows Authentication user IDs from our SQ
L
> Server databases?
>|||I found the sp_ValidateLogins very helpful.
To quote the BOL - "Reports information about orphaned Microsoft? Windows
NT? users and groups that no longer exist in the Windows NT environment but
still have entries in the Microsoft SQL Server? system tables."
Randy
"Stephanie" wrote:

> How can we proactively find out when a Windows Authentication ID in SQL
> Server is no longer valid, i.e. an employee is no longer with the company
so
> we should delete their related Windows Authentication user IDs from our SQ
L
> Server databases?
>

How to detect changes to the structure of databases, tables and even SP

HI,

Any help here is appreciated, I work in a large software company that has many small teams. I am faced with the issue of some of the other teams are changing the structure of tables and views and even SPs and functions without letting the rest know of these changes.

My question is, is there a way of tracking these changes through a job to alert everyone else in case this situation happens.

regards

You could write some TSQL code that will monitor or snapshot system table information, compare it to the database and find the differences. You can also go the 3rd party route and look at tools like Redgate's SQLCompare to do this for you. Your best bet however is to use your source code control system to track the changes and maintain them (I am assuming you already have such system in place). Apart from these, there are no built-in methods that are easy enough to detect differences out of the box.|||

That is what i thought, but I was hopping for an easy solution. Currently we are tracking it with source control although not all developers adhere to the rules.

thanks anyways for your quick response.

emad

|||

Yeah, RedGate has a nice snapshot feature that you can use to keep a version around of a server. I use SQLCompare when I am migrating changes just to make sure nothing has changed that hasn't been done quite right.

In 2005, you might also consider using a DDL trigger to capture changes (I do this to make sure no one makes any unkown changes. You could then check this table for who has made changes (of course if the changer has dbo rights they could disable this trigger before making changes, but if you have malicious programmers (rather than those who ar just too lazy to do things right :) then you have way more problems than any of us can help you with :)

|||I understand that the DML trigger can be used to find a one or many changes that happen to data in a user table purposely or otherwise. I did that using deleted, inserted, and updated tables. I do not know how to get the user name or id that changed it. Where can I get this information from?. Also, is it possible to get the column names in these three tables? Please advise. Thanks.|||DML trigger is for tracking data changes. You can use SYSTEM_USER to track login information for example. But the relevance of this information depends on your application architecture and how the interaction with the database happens from the end-user. DDL triggers (new in SQL Server 2005) on the other hand can be used to track schema changes for example and that is more closer to what you want.sql

How to define a parameter to show the true values for any of 35 different products?

My boss database is in a way that customers might have baught any of the 35 products of the company. Say if they have bought product# 16, and 27, these two values are true for that customer and the rest are false (the table has 36 columns: 35 for products and 1 for customer ID). How can I show if a customer has bought anything at all (some of them has not bought anything), and if so which numbers as a parameter? I mean I want to have a parameter that user defines to see the result for customers who have number 10, 17 as true? or number 6 as false? I probably need two parameters, one for true and false and one for the number. Although I think if I have just one parameter, that might work too. Let's say the user checks numbers 5,9, and 14 and see the customers who have bought those. So my drop down should have 35 rows for the user to choose from. But how can I make this parameter to work (define wise or query wise)?

Thanks.

I am still stuck on this. Can you people (lots of experts here) help me on this?

Lots of thanks,

Alexan

|||

Its really confusing your question anyway can u explain clearly

You need to Hide some records right?

|||

Thanks for answering Kiran.

Ok. Here is how it goes. We have a table in our database that holds the records for different products. I am going to scale down from 35 to 3 just for making the question easier. Here is how it is:

I have a table with these columns:

- Customer (Holds customer name string)

- Bought Product 1? (Holds Yes or No, indicating if this customer has bought Product 1)

- Bought Product 2? (Holds Yes or No, indicating if this customer has bought Product 2)

- Bought Product 3? (Holds Yes or No, indicating if this customer has bought Product 3)

Now I want to have a parameter so the user can choose to see only the customers who have bought product numbers passed by the parameter.

e.g. The user only wants to see which customers have bought Product 1 & 3, Or which customers have not bought product 1.

I hope this clarifies a little. Please if you still need some more clarification, send a post and I'll be explain more with details.

Waiting to hear from you guys.

I am new in databases and MS SQL, so Could you please help me out?

Lots of thanks,

|||Help please|||

Hi, ok assuming that you have SQL Server 2005 which holds the multivalue parameter and under the assumption your table structure is simplified as follows:

OrderDetail
========
Orderid
PosId
ProductId

Order
====
OrderId
CustomerID

You would have to do a Query like the following:

SELECT CustomerID From Order O
INNER JOIN OderDetail Od
ON O.OrderID = Od.OrderID
WHERE ProductID IN (@.YourParameter)

For the customername you would have to join additionally the Customer table to retrieve this data.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de