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 missing permissions ?

I've been tediously working on setting up scripts to limit access through
our different departments accordingly. I've been working at creating the
tightest set of perms first but can't seem to get it working. I've posted
the script obviously but figured I'd ask if there were some way to determine
what permissions are required when I get an access denied error...
In the script it is assumed that there are already 3 logins created and
added to the current database. I know there is alot to go on the script as
far as error checking, previous existence etc....but in a controlled
environment (my localhost at this time) this should work. The problem is
after running this I couldn't even open and view db properties...I had to
drop and recreate the user from login. Then I still couldn't select data
from any table.
THANKS!!!! For any input. I know I'm missing it somewhere here bigtime.
----
--
-- LIVE_LimitedWebAccess --
-- creates permissions applicable to a live environment for web
----
--
----
--
-- create the role that will be used to limit access
----
--
CREATE ROLE [LimitedWebAccess] AUTHORIZATION [dbo]
----
--
-- add logins to the new role
----
--
EXEC sp_addrolemember N'LimitedWebAccess', N'web'
EXEC sp_addrolemember N'LimitedWebAccess', N'webdev'
EXEC sp_addrolemember N'LimitedWebAccess', N'wtf'
----
--
-- loop thru all tables setting permissions appropriately for the new role
----
--
declare @.strName nvarchar(100)
declare @.strSQL nvarchar(1024)
declare curTables cursor for
select [name] from sys.tables where type='U'
open curTables
fetch next from curTables into @.strName
while @.@.fetch_status=0
begin
set @.strSQL='DENY ALTER ON [dbo].[' + @.strName + '] TO [LimitedW
ebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='DENY CONTROL ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='GRANT DELETE ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='GRANT INSERT ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='DENY REFERENCES ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='GRANT SELECT ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='DENY TAKE OWNERSHIP ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='GRANT UPDATE ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='DENY VIEW DEFINITION ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
fetch next from curTables into @.strName
end
close curTables
deallocate curTables
----
--
-- loop thru all views setting permissions appropriately for the new role
----
--
declare curViews cursor for
select [name] from sys.views
open curViews
fetch next from curViews into @.strName
while @.@.fetch_status=0
begin
set @.strSQL='DENY ALTER ON [dbo].[' + @.strName + '] TO [LimitedW
ebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='DENY CONTROL ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='GRANT DELETE ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='GRANT INSERT ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='DENY REFERENCES ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='GRANT SELECT ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='DENY TAKE OWNERSHIP ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='GRANT UPDATE ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='DENY VIEW DEFINITION ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
fetch next from curViews into @.strName
end
close curViews
deallocate curViews
----
--
-- loop thru all stored procs setting permissions appropriately for the new
role
----
--
declare curProcs cursor for
select [name] from sys.procedures where is_ms_shipped=0
open curProcs
fetch next from curProcs into @.strName
while @.@.fetch_status=0
begin
set @.strSQL='GRANT EXECUTE ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='DENY ALTER ON [dbo].[' + @.strName + '] TO [LimitedW
ebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='DENY CONTROL ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='DENY TAKE OWNERSHIP ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='DENY VIEW DEFINITION ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
fetch next from curProcs into @.strName
end
close curProcs
deallocate curProcs
----
--
-- loop thru all user functions setting permissions appropriately for the
new role
----
--
declare curFuncs cursor for
select [name] from sys.objects where type='FN'
open curFuncs
fetch next from curFuncs into @.strName
while @.@.fetch_status=0
begin
set @.strSQL='GRANT EXECUTE ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='DENY ALTER ON [dbo].[' + @.strName + '] TO [LimitedW
ebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='DENY CONTROL ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='DENY TAKE OWNERSHIP ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='DENY VIEW DEFINITION ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
fetch next from curFuncs into @.strName
end
close curFuncs
deallocate curFuncs> the script obviously but figured I'd ask if there were some way to
> determine what permissions are required when I get an access denied
> error...
I have a couple questions,
Does the login have sys_admin privileges?
does the user have db_owner fixed database role privileges?
Have you issued GRANT ON SELECT.... for more details please refer to the
BOL
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:e$gFbWPiGHA.3296@.TK2MSFTNGP05.phx.gbl...
> I've been tediously working on setting up scripts to limit access through
> our different departments accordingly. I've been working at creating the
> tightest set of perms first but can't seem to get it working. I've posted
> the script obviously but figured I'd ask if there were some way to
> determine what permissions are required when I get an access denied
> error...
> In the script it is assumed that there are already 3 logins created and
> added to the current database. I know there is alot to go on the script
> as far as error checking, previous existence etc....but in a controlled
> environment (my localhost at this time) this should work. The problem is
> after running this I couldn't even open and view db properties...I had to
> drop and recreate the user from login. Then I still couldn't select data
> from any table.
> THANKS!!!! For any input. I know I'm missing it somewhere here bigtime.
> ----
--
> -- LIVE_LimitedWebAccess --
> -- creates permissions applicable to a live environment for web
> ----
--
>
> ----
--
> -- create the role that will be used to limit access
> ----
--
> CREATE ROLE [LimitedWebAccess] AUTHORIZATION [dbo]
> ----
--
> -- add logins to the new role
> ----
--
> EXEC sp_addrolemember N'LimitedWebAccess', N'web'
> EXEC sp_addrolemember N'LimitedWebAccess', N'webdev'
> EXEC sp_addrolemember N'LimitedWebAccess', N'wtf'
>
> ----
--
> -- loop thru all tables setting permissions appropriately for the new role
> ----
--
> declare @.strName nvarchar(100)
> declare @.strSQL nvarchar(1024)
> declare curTables cursor for
> select [name] from sys.tables where type='U'
> open curTables
> fetch next from curTables into @.strName
> while @.@.fetch_status=0
> begin
> set @.strSQL='DENY ALTER ON [dbo].[' + @.strName + '] TO [Limite
dWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='DENY CONTROL ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='GRANT DELETE ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='GRANT INSERT ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='DENY REFERENCES ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='GRANT SELECT ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='DENY TAKE OWNERSHIP ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='GRANT UPDATE ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='DENY VIEW DEFINITION ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> fetch next from curTables into @.strName
> end
> close curTables
> deallocate curTables
>
> ----
--
> -- loop thru all views setting permissions appropriately for the new role
> ----
--
> declare curViews cursor for
> select [name] from sys.views
> open curViews
> fetch next from curViews into @.strName
> while @.@.fetch_status=0
> begin
> set @.strSQL='DENY ALTER ON [dbo].[' + @.strName + '] TO [Limite
dWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='DENY CONTROL ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='GRANT DELETE ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='GRANT INSERT ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='DENY REFERENCES ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='GRANT SELECT ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='DENY TAKE OWNERSHIP ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='GRANT UPDATE ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='DENY VIEW DEFINITION ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> fetch next from curViews into @.strName
> end
> close curViews
> deallocate curViews
>
> ----
--
> -- loop thru all stored procs setting permissions appropriately for the
> new role
> ----
--
> declare curProcs cursor for
> select [name] from sys.procedures where is_ms_shipped=0
> open curProcs
> fetch next from curProcs into @.strName
> while @.@.fetch_status=0
> begin
> set @.strSQL='GRANT EXECUTE ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='DENY ALTER ON [dbo].[' + @.strName + '] TO [Limite
dWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='DENY CONTROL ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='DENY TAKE OWNERSHIP ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='DENY VIEW DEFINITION ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> fetch next from curProcs into @.strName
> end
> close curProcs
> deallocate curProcs
>
> ----
--
> -- loop thru all user functions setting permissions appropriately for the
> new role
> ----
--
> declare curFuncs cursor for
> select [name] from sys.objects where type='FN'
> open curFuncs
> fetch next from curFuncs into @.strName
> while @.@.fetch_status=0
> begin
> set @.strSQL='GRANT EXECUTE ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='DENY ALTER ON [dbo].[' + @.strName + '] TO [Limite
dWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='DENY CONTROL ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='DENY TAKE OWNERSHIP ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='DENY VIEW DEFINITION ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> fetch next from curFuncs into @.strName
> end
> close curFuncs
> deallocate curFuncs
>
>
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eFFpF6SiGHA.4200@.TK2MSFTNGP05.phx.gbl...
> I have a couple questions,
> Does the login have sys_admin privileges?
> does the user have db_owner fixed database role privileges?
Do you mean for the user running the script? If so I'm running the script
as 'sa'. If you mean the user 'web' then absolutely not. We want to
restrict them to selecting/updating data. As shown in the script below they
are explicitly granted select on all tables/views...

> Have you issued GRANT ON SELECT.... for more details please refer to the
> BOL
I have. That is how I got this far...but I'm having trouble now finding
what is missing. I don't want the user 'web' to have sys_admin privileges.
But with the script below the user 'web' now gets 'Invalid object name
'tblname'' when trying to select data from a table.

>
> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
> news:e$gFbWPiGHA.3296@.TK2MSFTNGP05.phx.gbl...
>|||Tim
> But with the script below the user 'web' now gets 'Invalid object name
> 'tblname'' when trying to select data from a table.
Pobably because the "web" user/login is not the owner of the "tblname", Can
you check it?
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:eoG8BuXiGHA.3408@.TK2MSFTNGP05.phx.gbl...
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eFFpF6SiGHA.4200@.TK2MSFTNGP05.phx.gbl...
> Do you mean for the user running the script? If so I'm running the script
> as 'sa'. If you mean the user 'web' then absolutely not. We want to
> restrict them to selecting/updating data. As shown in the script below
> they are explicitly granted select on all tables/views...
>
>
> I have. That is how I got this far...but I'm having trouble now finding
> what is missing. I don't want the user 'web' to have sys_admin
> privileges. But with the script below the user 'web' now gets 'Invalid
> object name 'tblname'' when trying to select data from a table.
>
>
>

How to Determine if a user is a member of the System Admin role?

Is there a script/function that can be used to determine if a user (granted
login/access via NT Group Membership) is a member of the System Administrato
r
group?
We had an issue where a user was a member of multiple NT Global Groups, one
of which was a member of (had) the System Admin role. Our application check
s
to see if the NT Group for our Application has DBO rights, but this returned
false ... yet the user would (by default) create objects (views/tables) in
dbo. We finally traced this down via Enterprise Mgr, Security, Server Roles
and dbl-clicked "System Administrators" and found that there were unexpected
groups there, and our user(s) were in one or more of these groups. How can w
e
determine this via code/script and then "turn it off" for our database (of
course it is possible that a user that is a member of another group MAY need
SA rights in another database)?
Thank you,
Brad
--
Brad Ashforth> Is there a script/function that can be used to determine if a user
> (granted
> login/access via NT Group Membership) is a member of the System
> Administrator
> group?
SELECT IS_SRVROLEMEMBER('sysadmin')

> How can we
> determine this via code/script and then "turn it off" for our database (of
> course it is possible that a user that is a member of another group MAY
> need
> SA rights in another database)?
In SQL 2000, there are only 2 cases where objects will be created in the dbo
schema by default: 1) user is the database owner and 2) user is a
sysadmin role member. The query 'SELECT USER' will return 'dbo' in both
cases.
I'm not sure I understand what you mean by 'turn it off'. Do you mean that
you want the default schema to be other than 'dbo' for the dbo user? Have
you considered schema-qualifying object names so that the default schema
isn't relevant?
Hope this helps.
Dan Guzman
SQL Server MVP
"Brad Ashforth" <banospam@.nospam.nospam> wrote in message
news:5BEE8141-69CC-415A-A57E-48C875CB31AE@.microsoft.com...
> Is there a script/function that can be used to determine if a user
> (granted
> login/access via NT Group Membership) is a member of the System
> Administrator
> group?
> We had an issue where a user was a member of multiple NT Global Groups,
> one
> of which was a member of (had) the System Admin role. Our application
> checks
> to see if the NT Group for our Application has DBO rights, but this
> returned
> false ... yet the user would (by default) create objects (views/tables) in
> dbo. We finally traced this down via Enterprise Mgr, Security, Server
> Roles
> and dbl-clicked "System Administrators" and found that there were
> unexpected
> groups there, and our user(s) were in one or more of these groups. How can
> we
> determine this via code/script and then "turn it off" for our database (of
> course it is possible that a user that is a member of another group MAY
> need
> SA rights in another database)?
> Thank you,
> Brad
> --
> Brad Ashforth|||Hello Brad,
As for a windows user account(or group) or a sqlserver account, before we
check if it is of sysadmin role (in the server instance), we should first
check if it's a server login(principal) on that server instance. For this,
we can use some T-SQL query to lookup all the principals of sysadmin role
in the master db. It'll be a bit different for SQL 2005 and SQL 2000:
============2005===========
select p1.Name as Role_name, p2.Name as Member_name from
sys.server_role_members r1 inner join sys.server_principals p1
on r1.Role_principal_id = p1.Principal_id
inner join sys.server_principals p2
on r1.Member_principal_id = p2.Principal_id
=========================
As you can see, we need to query multiple catalog views in sys schema.
While in SQL server 2000, we can diretly query the "syslogins" table in
master db, and this table contains a "sysadmin" column indicate whether the
certain principal is of sysadmin role.
==============2000==================
select * from syslogins
Hope this helps.
Regards,
Steven Cheng
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hello Brad,
How are you doing on this issue or does our suggestion help you some? If
there is still anything we can help, please feel free to post here.
Regards,
Steven Cheng
Microsoft MSDN Online Support Lead
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hello Brad,
How are you doing on this issue or does our suggestion help you some? If
there is still anything we can help, please feel free to post here.
Regards,
Steven Cheng
Microsoft MSDN Online Support Lead
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

How to design link tables?

I have been having a similar discussion on the MS Access newsgroup for the
last w and I wanted to discuss the issues in the context of MS SQL in
addition to MS Access. I hope this form of cross posting is not offensive to
anyone.
Lets assume I have to relations: student and course and I want to create a
junction table to record which students are taking which courses.
(1) If I create a two column table containing fkStudent and fkCourse where
the primary consists of these two columns. Now I want to perform a join to
find out all the courses a particular student is taking. Since the primary
key index structure requires both foreign keys and I'm only providing the
value for fkStudent and not fkCourse, will I be preforming a linear search
on the junction table when I preform a join to determine what courses I am
taking?
My guess is yes. If your answer is yes, would you anticipate a linear search
to be a problem? I would. If you agree that we should avoid linear searches,
then what would you recommend for a primary key on this link table? Do we
need a pimary key at all? Most folks say yes -- but I'm not clear on why.
(2) Lets say I have many thousands of job titles -- too many for a combo
box. I want to have a M:M relationship between Job Titles and Job Postings.
Let's say I find a job posting on the web and I need to find the foriegn key
for a job title (if it exists) or create a job title (if it does not exist).
I can assume it does not exist and try an SQL INSERT and, if that fails, use
a SELECT. Or, I can assume it does exist and use SELECT and if I don't find
any, use INSERT. Either way, the worst case scenerio requires two redundant
lookups. Is there a better approach?
Thanks,
SiegfriedHi
1) Look at [Order Details] table in Northwind database.
It is a "junction table" between Orders and Product tables.
2)
IF NOT EXISTS (SELECT * FROM Table WHERE....)
INSERT INTO ......
ELSE
SELECT <columns> FROM ......
"Siegfried Heintze" <siegfried@.heintze.com> wrote in message
news:%23ofWEqlsFHA.460@.TK2MSFTNGP15.phx.gbl...
>I have been having a similar discussion on the MS Access newsgroup for the
> last w and I wanted to discuss the issues in the context of MS SQL in
> addition to MS Access. I hope this form of cross posting is not offensive
> to
> anyone.
> Lets assume I have to relations: student and course and I want to create a
> junction table to record which students are taking which courses.
> (1) If I create a two column table containing fkStudent and fkCourse where
> the primary consists of these two columns. Now I want to perform a join to
> find out all the courses a particular student is taking. Since the primary
> key index structure requires both foreign keys and I'm only providing the
> value for fkStudent and not fkCourse, will I be preforming a linear search
> on the junction table when I preform a join to determine what courses I am
> taking?
> My guess is yes. If your answer is yes, would you anticipate a linear
> search
> to be a problem? I would. If you agree that we should avoid linear
> searches,
> then what would you recommend for a primary key on this link table? Do we
> need a pimary key at all? Most folks say yes -- but I'm not clear on why.
> (2) Lets say I have many thousands of job titles -- too many for a combo
> box. I want to have a M:M relationship between Job Titles and Job
> Postings.
> Let's say I find a job posting on the web and I need to find the foriegn
> key
> for a job title (if it exists) or create a job title (if it does not
> exist).
> I can assume it does not exist and try an SQL INSERT and, if that fails,
> use
> a SELECT. Or, I can assume it does exist and use SELECT and if I don't
> find
> any, use INSERT. Either way, the worst case scenerio requires two
> redundant
> lookups. Is there a better approach?
> Thanks,
> Siegfried
>

How to deploy my Reports to the Internet? Anonymous access

How can I achive this. I need some reports to be on my page; I already know
to use reportviewer. So I will show it inside my page. But I want to
configure some of the reports that the user dont be asked for a DOMAIN user
name.*
What should I do?
--
LUIS ESTEBAN VALENCIA
MICROSOFT DCE 3.
MIEMBRO ACTIVO DE ALIANZADEV
http://spaces.msn.com/members/extremed/What kind of Authentication you are using for website? Form based?
HTH,
ALi-R
"Luis Esteban Valencia" <luisvalen@.haceb.com> wrote in message
news:Ob62InICFHA.3504@.TK2MSFTNGP12.phx.gbl...
> How can I achive this. I need some reports to be on my page; I already
know
> to use reportviewer. So I will show it inside my page. But I want to
> configure some of the reports that the user dont be asked for a DOMAIN
user
> name.*
> What should I do?
> --
> LUIS ESTEBAN VALENCIA
> MICROSOFT DCE 3.
> MIEMBRO ACTIVO DE ALIANZADEV
> http://spaces.msn.com/members/extremed/
>|||It is not often good to enable anonymous access to the report server - this
hinders your ability to manage the system (everyone is the same user). You
can use a custom security extension to govern this w/o needing to create
DOMIAN accounts. You'd need to author and deploy the custom security
extension.
If this is not viable, an alternative is to deploy two report servers in a
web farm. Make on internet facing and allow anonymous access on it's
virtual directory. Make the other internal facing and require
authentication on it. This allows you to both give anonymous users rights
while ensuring fine grained security control over who can do report server
operations.
You will also want to customize the roles on your report server to limit
what anonymous users can do - anonymous shouldn't be able to subscribe for
example, this might bring your report server to its knees in the hands of an
attacker :-).
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
"Luis Esteban Valencia" <luisvalen@.haceb.com> wrote in message
news:Ob62InICFHA.3504@.TK2MSFTNGP12.phx.gbl...
> How can I achive this. I need some reports to be on my page; I already
> know
> to use reportviewer. So I will show it inside my page. But I want to
> configure some of the reports that the user dont be asked for a DOMAIN
> user
> name.*
> What should I do?
> --
> LUIS ESTEBAN VALENCIA
> MICROSOFT DCE 3.
> MIEMBRO ACTIVO DE ALIANZADEV
> http://spaces.msn.com/members/extremed/
>|||Hi Lukasz,
I know that you always monitor the threads you answer so I'd like to use
this thread to let you know that ,the thread I initiated with the subject of
"Sharepoint and Reporting Services" has been left unanswered for that last
post I did.I would appreciate if you could take a quick look at it too.
Thanks
"Lukasz Pawlowski [MSFT]" <lukaszp@.online.microsoft.com> wrote in message
news:eNaNyMfDFHA.2824@.tk2msftngp13.phx.gbl...
> It is not often good to enable anonymous access to the report server -
this
> hinders your ability to manage the system (everyone is the same user).
You
> can use a custom security extension to govern this w/o needing to create
> DOMIAN accounts. You'd need to author and deploy the custom security
> extension.
> If this is not viable, an alternative is to deploy two report servers in a
> web farm. Make on internet facing and allow anonymous access on it's
> virtual directory. Make the other internal facing and require
> authentication on it. This allows you to both give anonymous users rights
> while ensuring fine grained security control over who can do report server
> operations.
> You will also want to customize the roles on your report server to limit
> what anonymous users can do - anonymous shouldn't be able to subscribe for
> example, this might bring your report server to its knees in the hands of
an
> attacker :-).
> -Lukasz
>
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Luis Esteban Valencia" <luisvalen@.haceb.com> wrote in message
> news:Ob62InICFHA.3504@.TK2MSFTNGP12.phx.gbl...
> > How can I achive this. I need some reports to be on my page; I already
> > know
> > to use reportviewer. So I will show it inside my page. But I want to
> > configure some of the reports that the user dont be asked for a DOMAIN
> > user
> > name.*
> > What should I do?
> >
> > --
> > LUIS ESTEBAN VALENCIA
> > MICROSOFT DCE 3.
> > MIEMBRO ACTIVO DE ALIANZADEV
> > http://spaces.msn.com/members/extremed/
> >
> >
>|||Sorry, I don't have that thread anymore. What is the issue?
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
"ALI-R" <newbie@.microsoft.com> wrote in message
news:OanIKNhDFHA.2508@.TK2MSFTNGP09.phx.gbl...
> Hi Lukasz,
> I know that you always monitor the threads you answer so I'd like to use
> this thread to let you know that ,the thread I initiated with the subject
> of
> "Sharepoint and Reporting Services" has been left unanswered for that last
> post I did.I would appreciate if you could take a quick look at it too.
> Thanks
> "Lukasz Pawlowski [MSFT]" <lukaszp@.online.microsoft.com> wrote in message
> news:eNaNyMfDFHA.2824@.tk2msftngp13.phx.gbl...
>> It is not often good to enable anonymous access to the report server -
> this
>> hinders your ability to manage the system (everyone is the same user).
> You
>> can use a custom security extension to govern this w/o needing to create
>> DOMIAN accounts. You'd need to author and deploy the custom security
>> extension.
>> If this is not viable, an alternative is to deploy two report servers in
>> a
>> web farm. Make on internet facing and allow anonymous access on it's
>> virtual directory. Make the other internal facing and require
>> authentication on it. This allows you to both give anonymous users
>> rights
>> while ensuring fine grained security control over who can do report
>> server
>> operations.
>> You will also want to customize the roles on your report server to limit
>> what anonymous users can do - anonymous shouldn't be able to subscribe
>> for
>> example, this might bring your report server to its knees in the hands of
> an
>> attacker :-).
>> -Lukasz
>>
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>>
>> "Luis Esteban Valencia" <luisvalen@.haceb.com> wrote in message
>> news:Ob62InICFHA.3504@.TK2MSFTNGP12.phx.gbl...
>> > How can I achive this. I need some reports to be on my page; I already
>> > know
>> > to use reportviewer. So I will show it inside my page. But I want to
>> > configure some of the reports that the user dont be asked for a DOMAIN
>> > user
>> > name.*
>> > What should I do?
>> >
>> > --
>> > LUIS ESTEBAN VALENCIA
>> > MICROSOFT DCE 3.
>> > MIEMBRO ACTIVO DE ALIANZADEV
>> > http://spaces.msn.com/members/extremed/
>> >
>> >
>>
>|||The thread subject is "Sharepiont and Reporting services" and from "ALI-R"
,it was about using kerbers for having sps and rs on different computers,I
asked the last question and unfortunately I didn't get any answer from you.
"Lukasz Pawlowski [MSFT]" <lukaszp@.online.microsoft.com> wrote in message
news:%23rB5wTvDFHA.2568@.TK2MSFTNGP10.phx.gbl...
> Sorry, I don't have that thread anymore. What is the issue?
> -Lukasz
>
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "ALI-R" <newbie@.microsoft.com> wrote in message
> news:OanIKNhDFHA.2508@.TK2MSFTNGP09.phx.gbl...
> > Hi Lukasz,
> >
> > I know that you always monitor the threads you answer so I'd like to use
> > this thread to let you know that ,the thread I initiated with the
subject
> > of
> > "Sharepoint and Reporting Services" has been left unanswered for that
last
> > post I did.I would appreciate if you could take a quick look at it too.
> > Thanks
> > "Lukasz Pawlowski [MSFT]" <lukaszp@.online.microsoft.com> wrote in
message
> > news:eNaNyMfDFHA.2824@.tk2msftngp13.phx.gbl...
> >> It is not often good to enable anonymous access to the report server -
> > this
> >> hinders your ability to manage the system (everyone is the same user).
> > You
> >> can use a custom security extension to govern this w/o needing to
create
> >> DOMIAN accounts. You'd need to author and deploy the custom security
> >> extension.
> >>
> >> If this is not viable, an alternative is to deploy two report servers
in
> >> a
> >> web farm. Make on internet facing and allow anonymous access on it's
> >> virtual directory. Make the other internal facing and require
> >> authentication on it. This allows you to both give anonymous users
> >> rights
> >> while ensuring fine grained security control over who can do report
> >> server
> >> operations.
> >>
> >> You will also want to customize the roles on your report server to
limit
> >> what anonymous users can do - anonymous shouldn't be able to subscribe
> >> for
> >> example, this might bring your report server to its knees in the hands
of
> > an
> >> attacker :-).
> >>
> >> -Lukasz
> >>
> >>
> >> --
> >> This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >>
> >>
> >>
> >> "Luis Esteban Valencia" <luisvalen@.haceb.com> wrote in message
> >> news:Ob62InICFHA.3504@.TK2MSFTNGP12.phx.gbl...
> >> > How can I achive this. I need some reports to be on my page; I
already
> >> > know
> >> > to use reportviewer. So I will show it inside my page. But I want
to
> >> > configure some of the reports that the user dont be asked for a
DOMAIN
> >> > user
> >> > name.*
> >> > What should I do?
> >> >
> >> > --
> >> > LUIS ESTEBAN VALENCIA
> >> > MICROSOFT DCE 3.
> >> > MIEMBRO ACTIVO DE ALIANZADEV
> >> > http://spaces.msn.com/members/extremed/
> >> >
> >> >
> >>
> >>
> >
> >
>|||I'm sorry, could you restate your question? I don't have the other thread
and I don't know which question you're referring to when you say, "the last
question.".
Are you asking how to deploy Kerberos? I couldn't really find a link that
explained how to do this. I know there are books written on this topic.
You can start here:
http://toolbar.search.msn.com/results.aspx?q=kerberos+how+to+site%3Amsdn.microsoft.com&FORM=QBRE
-Lukasz
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"ALI-R" <newbie@.microsoft.com> wrote in message
news:OD9%23NqwDFHA.2232@.TK2MSFTNGP14.phx.gbl...
> The thread subject is "Sharepiont and Reporting services" and from "ALI-R"
> ,it was about using kerbers for having sps and rs on different computers,I
> asked the last question and unfortunately I didn't get any answer from
> you.
> "Lukasz Pawlowski [MSFT]" <lukaszp@.online.microsoft.com> wrote in message
> news:%23rB5wTvDFHA.2568@.TK2MSFTNGP10.phx.gbl...
>> Sorry, I don't have that thread anymore. What is the issue?
>> -Lukasz
>>
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>>
>> "ALI-R" <newbie@.microsoft.com> wrote in message
>> news:OanIKNhDFHA.2508@.TK2MSFTNGP09.phx.gbl...
>> > Hi Lukasz,
>> >
>> > I know that you always monitor the threads you answer so I'd like to
>> > use
>> > this thread to let you know that ,the thread I initiated with the
> subject
>> > of
>> > "Sharepoint and Reporting Services" has been left unanswered for that
> last
>> > post I did.I would appreciate if you could take a quick look at it too.
>> > Thanks
>> > "Lukasz Pawlowski [MSFT]" <lukaszp@.online.microsoft.com> wrote in
> message
>> > news:eNaNyMfDFHA.2824@.tk2msftngp13.phx.gbl...
>> >> It is not often good to enable anonymous access to the report server -
>> > this
>> >> hinders your ability to manage the system (everyone is the same user).
>> > You
>> >> can use a custom security extension to govern this w/o needing to
> create
>> >> DOMIAN accounts. You'd need to author and deploy the custom security
>> >> extension.
>> >>
>> >> If this is not viable, an alternative is to deploy two report servers
> in
>> >> a
>> >> web farm. Make on internet facing and allow anonymous access on it's
>> >> virtual directory. Make the other internal facing and require
>> >> authentication on it. This allows you to both give anonymous users
>> >> rights
>> >> while ensuring fine grained security control over who can do report
>> >> server
>> >> operations.
>> >>
>> >> You will also want to customize the roles on your report server to
> limit
>> >> what anonymous users can do - anonymous shouldn't be able to subscribe
>> >> for
>> >> example, this might bring your report server to its knees in the hands
> of
>> > an
>> >> attacker :-).
>> >>
>> >> -Lukasz
>> >>
>> >>
>> >> --
>> >> This posting is provided "AS IS" with no warranties, and confers no
>> > rights.
>> >>
>> >>
>> >>
>> >> "Luis Esteban Valencia" <luisvalen@.haceb.com> wrote in message
>> >> news:Ob62InICFHA.3504@.TK2MSFTNGP12.phx.gbl...
>> >> > How can I achive this. I need some reports to be on my page; I
> already
>> >> > know
>> >> > to use reportviewer. So I will show it inside my page. But I want
> to
>> >> > configure some of the reports that the user dont be asked for a
> DOMAIN
>> >> > user
>> >> > name.*
>> >> > What should I do?
>> >> >
>> >> > --
>> >> > LUIS ESTEBAN VALENCIA
>> >> > MICROSOFT DCE 3.
>> >> > MIEMBRO ACTIVO DE ALIANZADEV
>> >> > http://spaces.msn.com/members/extremed/
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>

How to deploy my reports on the Internet: Anonymous Access?

How can I achive this. I need some reports to be on my page; I already know
to use reportviewer. So I will show it inside my page. But I want to
configure some of the reports that the user dont be asked for a DOMAIN user
name.*
What should I do?
--
LUIS ESTEBAN VALENCIA
MICROSOFT DCE 3.
MIEMBRO ACTIVO DE ALIANZADEV
http://spaces.msn.com/members/extremed/Anonymous access is pretty difficult to set up. With a default
configuration, when you turn on anonymous access on the web site, then
you're not prompted for a user name at all, and then you can't do your other
stuff like get to protected information or publish files using the Report
Manager.
There are ways to do it, but because of the security concerns, my suggestion
would be to call Microsoft and open a support ticket asking them to walk you
through the process.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Luis Esteban Valencia" <luisvalen@.haceb.com> wrote in message
news:%23iPZZVrEFHA.1836@.tk2msftngp13.phx.gbl...
> How can I achive this. I need some reports to be on my page; I already
> know
> to use reportviewer. So I will show it inside my page. But I want to
> configure some of the reports that the user dont be asked for a DOMAIN
> user
> name.*
> What should I do?
> --
> LUIS ESTEBAN VALENCIA
> MICROSOFT DCE 3.
> MIEMBRO ACTIVO DE ALIANZADEV
> http://spaces.msn.com/members/extremed/
>

How to deny information schema views...

Hello, all. I created a login and granted the user access to my sql 2005
database. well when the user creates his odbc dsn to access the database, I
discovered he can also see the INFORMATION_SCHEMA views. What gives? How
can I deny him access to these objects. He should have access to the db I
granted him.
Help!!!!!!!!
RozThe Information Schema views in SQL Server 2005 should only return for the
user information about the objects the user actually has access to. While
this was a prominent information disclosure issue in SQL Server 2000, it's
not as wide open in SQL Server 2005. They are provided for SQL-92 compliance
so that users can query the metadata/schema of the database without having
to query the system tables. Is there a reason you want to block access to
them?
K. Brian Kelley, brian underscore kelley at sqlpass dot org
http://www.truthsolutions.com/

> Hello, all. I created a login and granted the user access to my sql
> 2005 database. well when the user creates his odbc dsn to access the
> database, I discovered he can also see the INFORMATION_SCHEMA views.
> What gives? How can I deny him access to these objects. He should
> have access to the db I granted him.
> Help!!!!!!!!
> Roz|||Hello Roz,
You can't hide the fact the views exist as far as I can tell, but if you
look at what he see, it won't be much if anything. Basically he has to be
able to the see the metadata's metadata, but he shouldn't be able to see
the metadata itself unless you start granting him rights to do so (e.g.,
VIEW DEFINITION).
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Thanks for reply. I want to block access because as my users create their
ODBC DSNs, they can open these tables and **change** data. I've tried it an
d
it works. Very scary.
Roz
"K. Brian Kelley" wrote:

> The Information Schema views in SQL Server 2005 should only return for the
> user information about the objects the user actually has access to. While
> this was a prominent information disclosure issue in SQL Server 2000, it's
> not as wide open in SQL Server 2005. They are provided for SQL-92 complian
ce
> so that users can query the metadata/schema of the database without having
> to query the system tables. Is there a reason you want to block access to
> them?
>
> K. Brian Kelley, brian underscore kelley at sqlpass dot org
> http://www.truthsolutions.com/
>
>
>|||Kent,
Simply having the "public" role, gets him access to these tables. He (I)
was even able to open these tables say in Access thru ODBC, and potentially
change the data. Scary.
Roz
"Kent Tegels" wrote:

> Hello Roz,
> You can't hide the fact the views exist as far as I can tell, but if you
> look at what he see, it won't be much if anything. Basically he has to be
> able to the see the metadata's metadata, but he shouldn't be able to see
> the metadata itself unless you start granting him rights to do so (e.g.,
> VIEW DEFINITION).
> Thanks!
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>|||I am wondering if you are seeing something else.
Could you please give us the steps you used to open
information schema views and change the underlying data on
SQL Server 2005? Which views, data in what columns?
As far as I know, what you are saying is not possible.
If it is actually other tables you are referring too, I
think you have a permissions issue with how you have
security set up. I think that's likely the issue anyway.
-Sue
On Tue, 20 Mar 2007 16:51:05 -0700, Roz
<Roz@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Thanks for reply. I want to block access because as my users create their
>ODBC DSNs, they can open these tables and **change** data. I've tried it a
nd
>it works. Very scary.
>Roz
>"K. Brian Kelley" wrote:
>