Long running query

Hello group,
We have a query that calculates certain dates, etc. It runs very slow on
this server (3 hours!) which is a Gateway quad 600mhz with 2GB RAM running
Windows 2003 server and SQL 2000. If you copy the database over to another
server or XP workstation (even running a single 2.6 GHZ CPU with 1gb ram)
and run the same query it runs in 2-3 minutes. This is a huge gap of time
and I don't see a whole lot of performance changes using perfmon on the
troubled server.
Any ideas what I can use to troubleshoot this? Do you think this may be
hardware related or database or ?
TIA,
Jason
There can be many reasons. Did you verify using sp_who that this query is
not getting blocked? Did you monitor the physical disk counters to see if
there are any issues with disk subsystem performance?
What about the execution plans? Did you compare the execution plan of the
queries between your server and workstation? This will help in determining
any missing indexes or out of date stats.
Worth checking the fragmentation on your bigger tables and also updating the
statistics.
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Jason King" <jasonk@.bham.wednet.edu> wrote in message
news:%23ETRjMgOFHA.3356@.TK2MSFTNGP12.phx.gbl...
> Hello group,
> We have a query that calculates certain dates, etc. It runs very slow on
> this server (3 hours!) which is a Gateway quad 600mhz with 2GB RAM running
> Windows 2003 server and SQL 2000. If you copy the database over to another
> server or XP workstation (even running a single 2.6 GHZ CPU with 1gb ram)
> and run the same query it runs in 2-3 minutes. This is a huge gap of time
> and I don't see a whole lot of performance changes using perfmon on the
> troubled server.
> Any ideas what I can use to troubleshoot this? Do you think this may be
> hardware related or database or ?
> TIA,
> Jason
>
|||Thanks. We found the problem. It was due to passing NULL parameters within
the SP for retrieving certain dates. If we call the dates first then execute
the procedure without any NULL parameters, it runs very fast.
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:uIDsfRnOFHA.3280@.TK2MSFTNGP10.phx.gbl...
> There can be many reasons. Did you verify using sp_who that this query is
> not getting blocked? Did you monitor the physical disk counters to see if
> there are any issues with disk subsystem performance?
> What about the execution plans? Did you compare the execution plan of the
> queries between your server and workstation? This will help in determining
> any missing indexes or out of date stats.
> Worth checking the fragmentation on your bigger tables and also updating
the[vbcol=seagreen]
> statistics.
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Jason King" <jasonk@.bham.wednet.edu> wrote in message
> news:%23ETRjMgOFHA.3356@.TK2MSFTNGP12.phx.gbl...
running[vbcol=seagreen]
another[vbcol=seagreen]
ram)[vbcol=seagreen]
time
>
sql

Long running query

Hello group,
We have a query that calculates certain dates, etc. It runs very slow on
this server (3 hours!) which is a Gateway quad 600mhz with 2GB RAM running
Windows 2003 server and SQL 2000. If you copy the database over to another
server or XP workstation (even running a single 2.6 GHZ CPU with 1gb ram)
and run the same query it runs in 2-3 minutes. This is a huge gap of time
and I don't see a whole lot of performance changes using perfmon on the
troubled server.
Any ideas what I can use to troubleshoot this? Do you think this may be
hardware related or database or '
TIA,
JasonThere can be many reasons. Did you verify using sp_who that this query is
not getting blocked? Did you monitor the physical disk counters to see if
there are any issues with disk subsystem performance?
What about the execution plans? Did you compare the execution plan of the
queries between your server and workstation? This will help in determining
any missing indexes or out of date stats.
Worth checking the fragmentation on your bigger tables and also updating the
statistics.
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Jason King" <jasonk@.bham.wednet.edu> wrote in message
news:%23ETRjMgOFHA.3356@.TK2MSFTNGP12.phx.gbl...
> Hello group,
> We have a query that calculates certain dates, etc. It runs very slow on
> this server (3 hours!) which is a Gateway quad 600mhz with 2GB RAM running
> Windows 2003 server and SQL 2000. If you copy the database over to another
> server or XP workstation (even running a single 2.6 GHZ CPU with 1gb ram)
> and run the same query it runs in 2-3 minutes. This is a huge gap of time
> and I don't see a whole lot of performance changes using perfmon on the
> troubled server.
> Any ideas what I can use to troubleshoot this? Do you think this may be
> hardware related or database or '
> TIA,
> Jason
>|||Thanks. We found the problem. It was due to passing NULL parameters within
the SP for retrieving certain dates. If we call the dates first then execute
the procedure without any NULL parameters, it runs very fast.
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:uIDsfRnOFHA.3280@.TK2MSFTNGP10.phx.gbl...
> There can be many reasons. Did you verify using sp_who that this query is
> not getting blocked? Did you monitor the physical disk counters to see if
> there are any issues with disk subsystem performance?
> What about the execution plans? Did you compare the execution plan of the
> queries between your server and workstation? This will help in determining
> any missing indexes or out of date stats.
> Worth checking the fragmentation on your bigger tables and also updating
the
> statistics.
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Jason King" <jasonk@.bham.wednet.edu> wrote in message
> news:%23ETRjMgOFHA.3356@.TK2MSFTNGP12.phx.gbl...
running[vbcol=seagreen]
another[vbcol=seagreen]
ram)[vbcol=seagreen]
time[vbcol=seagreen]
>

Long running query

Hello group,
We have a query that calculates certain dates, etc. It runs very slow on
this server (3 hours!) which is a Gateway quad 600mhz with 2GB RAM running
Windows 2003 server and SQL 2000. If you copy the database over to another
server or XP workstation (even running a single 2.6 GHZ CPU with 1gb ram)
and run the same query it runs in 2-3 minutes. This is a huge gap of time
and I don't see a whole lot of performance changes using perfmon on the
troubled server.
Any ideas what I can use to troubleshoot this? Do you think this may be
hardware related or database or '
TIA,
JasonThere can be many reasons. Did you verify using sp_who that this query is
not getting blocked? Did you monitor the physical disk counters to see if
there are any issues with disk subsystem performance?
What about the execution plans? Did you compare the execution plan of the
queries between your server and workstation? This will help in determining
any missing indexes or out of date stats.
Worth checking the fragmentation on your bigger tables and also updating the
statistics.
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Jason King" <jasonk@.bham.wednet.edu> wrote in message
news:%23ETRjMgOFHA.3356@.TK2MSFTNGP12.phx.gbl...
> Hello group,
> We have a query that calculates certain dates, etc. It runs very slow on
> this server (3 hours!) which is a Gateway quad 600mhz with 2GB RAM running
> Windows 2003 server and SQL 2000. If you copy the database over to another
> server or XP workstation (even running a single 2.6 GHZ CPU with 1gb ram)
> and run the same query it runs in 2-3 minutes. This is a huge gap of time
> and I don't see a whole lot of performance changes using perfmon on the
> troubled server.
> Any ideas what I can use to troubleshoot this? Do you think this may be
> hardware related or database or '
> TIA,
> Jason
>|||Thanks. We found the problem. It was due to passing NULL parameters within
the SP for retrieving certain dates. If we call the dates first then execute
the procedure without any NULL parameters, it runs very fast.
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:uIDsfRnOFHA.3280@.TK2MSFTNGP10.phx.gbl...
> There can be many reasons. Did you verify using sp_who that this query is
> not getting blocked? Did you monitor the physical disk counters to see if
> there are any issues with disk subsystem performance?
> What about the execution plans? Did you compare the execution plan of the
> queries between your server and workstation? This will help in determining
> any missing indexes or out of date stats.
> Worth checking the fragmentation on your bigger tables and also updating
the
> statistics.
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Jason King" <jasonk@.bham.wednet.edu> wrote in message
> news:%23ETRjMgOFHA.3356@.TK2MSFTNGP12.phx.gbl...
> > Hello group,
> >
> > We have a query that calculates certain dates, etc. It runs very slow on
> > this server (3 hours!) which is a Gateway quad 600mhz with 2GB RAM
running
> > Windows 2003 server and SQL 2000. If you copy the database over to
another
> > server or XP workstation (even running a single 2.6 GHZ CPU with 1gb
ram)
> > and run the same query it runs in 2-3 minutes. This is a huge gap of
time
> > and I don't see a whole lot of performance changes using perfmon on the
> > troubled server.
> >
> > Any ideas what I can use to troubleshoot this? Do you think this may be
> > hardware related or database or '
> >
> > TIA,
> >
> > Jason
> >
> >
>

Long response time at a certain time each week

Our server is experiencing random slowdowns (for specific
inserts, updates or selects) at a certain time each week.
I've checked to make sure something else is not running
and do not see any scheduled jobs during this time. I've
captured a trace file and don't see any reason why it
would take a long time. It is not a busy time on the
server, either.
Any suggestions for some obvious things I could check?
Our environment is SQL Server 2000 with Service Pack 3 on
Windows 2000 with Service Pack 3.
TIA,
Jeanne UngerIs your database configured for auto-shrink? Is there some other activity on
your network that could be causing a slow down?
Jim
"Jeanne Unger" <anonymous@.discussions.microsoft.com> wrote in message
news:0a9601c3fb02$5f1bf540$a101280a@.phx.gbl...
> Our server is experiencing random slowdowns (for specific
> inserts, updates or selects) at a certain time each week.
> I've checked to make sure something else is not running
> and do not see any scheduled jobs during this time. I've
> captured a trace file and don't see any reason why it
> would take a long time. It is not a busy time on the
> server, either.
> Any suggestions for some obvious things I could check?
> Our environment is SQL Server 2000 with Service Pack 3 on
> Windows 2000 with Service Pack 3.
> TIA,
> Jeanne Unger|||It was configured to auto shrink, so I turned that off
yesterday. I will find out Sunday night whether or not it
made a difference.
I haven't been able to find anything on the network that
is causing the slowdown, and it is specific to this
particular database. For example, the queries usually
take 0-16 milliseconds but at 00:30 GMT it can take 3-4
minutes. No other applications that run across the
network are affected.

>--Original Message--
>Is your database configured for auto-shrink? Is there
some other activity on
>your network that could be causing a slow down?
>Jim
>"Jeanne Unger" <anonymous@.discussions.microsoft.com>
wrote in message
>news:0a9601c3fb02$5f1bf540$a101280a@.phx.gbl...
specific
week.
I've
on
>
>.
>

Long response time at a certain time each week

Our server is experiencing random slowdowns (for specific
inserts, updates or selects) at a certain time each week.
I've checked to make sure something else is not running
and do not see any scheduled jobs during this time. I've
captured a trace file and don't see any reason why it
would take a long time. It is not a busy time on the
server, either.
Any suggestions for some obvious things I could check?
Our environment is SQL Server 2000 with Service Pack 3 on
Windows 2000 with Service Pack 3.
TIA,
Jeanne UngerIs your database configured for auto-shrink? Is there some other activity on
your network that could be causing a slow down?
Jim
"Jeanne Unger" <anonymous@.discussions.microsoft.com> wrote in message
news:0a9601c3fb02$5f1bf540$a101280a@.phx.gbl...
> Our server is experiencing random slowdowns (for specific
> inserts, updates or selects) at a certain time each week.
> I've checked to make sure something else is not running
> and do not see any scheduled jobs during this time. I've
> captured a trace file and don't see any reason why it
> would take a long time. It is not a busy time on the
> server, either.
> Any suggestions for some obvious things I could check?
> Our environment is SQL Server 2000 with Service Pack 3 on
> Windows 2000 with Service Pack 3.
> TIA,
> Jeanne Unger|||It was configured to auto shrink, so I turned that off
yesterday. I will find out Sunday night whether or not it
made a difference.
I haven't been able to find anything on the network that
is causing the slowdown, and it is specific to this
particular database. For example, the queries usually
take 0-16 milliseconds but at 00:30 GMT it can take 3-4
minutes. No other applications that run across the
network are affected.
>--Original Message--
>Is your database configured for auto-shrink? Is there
some other activity on
>your network that could be causing a slow down?
>Jim
>"Jeanne Unger" <anonymous@.discussions.microsoft.com>
wrote in message
>news:0a9601c3fb02$5f1bf540$a101280a@.phx.gbl...
>> Our server is experiencing random slowdowns (for
specific
>> inserts, updates or selects) at a certain time each
week.
>> I've checked to make sure something else is not running
>> and do not see any scheduled jobs during this time.
I've
>> captured a trace file and don't see any reason why it
>> would take a long time. It is not a busy time on the
>> server, either.
>> Any suggestions for some obvious things I could check?
>> Our environment is SQL Server 2000 with Service Pack 3
on
>> Windows 2000 with Service Pack 3.
>> TIA,
>> Jeanne Unger
>
>.
>

Logon trigger in sql 2000 ?

Hi,
We have a reqt to do certain things as soon as a user logins into our
server. Is there a logon trigger in sql 2k ? For ex. We need to check things
like how many connections he already has before we allow another connection.
TIA
Moe
No, login triggers was introduced in 2005 sp2. Prior to that you could either do such checks on the
connecting session or use some polling mechanism. Neither very elegant...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"MO" <MO@.discussions.microsoft.com> wrote in message
news:E7CC7761-64F0-46D6-99FF-39B8B3287CE8@.microsoft.com...
> Hi,
> We have a reqt to do certain things as soon as a user logins into our
> server. Is there a logon trigger in sql 2k ? For ex. We need to check things
> like how many connections he already has before we allow another connection.
> TIA
> Moe
|||TQ Tibor. I am not sure what you mean by polling mechanism. Could you give me
more details on that ?
TIA
Moe
"Tibor Karaszi" wrote:

> No, login triggers was introduced in 2005 sp2. Prior to that you could either do such checks on the
> connecting session or use some polling mechanism. Neither very elegant...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "MO" <MO@.discussions.microsoft.com> wrote in message
> news:E7CC7761-64F0-46D6-99FF-39B8B3287CE8@.microsoft.com...
>
|||> TQ Tibor. I am not sure what you mean by polling mechanism. Could you give
> me
> more details on that ?
Well, let's say you don't want more than 10 connections per user, ok? Then
you can create a SQL Server Agent job that wakes up every minute, and does
this:
SELECT
SUSER_SNAME([sid]),
COUNT(DISTINCT spid)
FROM master..sysprocesses
GROUP BY SUSER_SNAME([sid])
HAVING COUNT(*) > 10
ORDER BY 2 DESC;
If rows are found, then take action! I'm not sure what that means in your
case, do you want an e-mail every time this happens so you will know about
it immediately, do you want to try to kill the oldest spid from each login,
do you want to log the information to a table, etc. etc. etc. If you are
using a specific database and there are one or more procedures that are hit
frequently, you could log the information to a table (deleting them every
time, of course), check if the current user is in the "too many connections"
list, and immediately return an error instead of performing the work.
A
|||Whoops, you'd probably also want to filter on spid > 50
"MO" <MO@.discussions.microsoft.com> wrote in message
news:DBF47D85-244F-423C-B873-816CF1DB725E@.microsoft.com...
> TQ Tibor. I am not sure what you mean by polling mechanism. Could you give
> me
> more details on that ?

Logon trigger in sql 2000 ?

Hi,
We have a reqt to do certain things as soon as a user logins into our
server. Is there a logon trigger in sql 2k ? For ex. We need to check things
like how many connections he already has before we allow another connection.
TIA
MoeNo, login triggers was introduced in 2005 sp2. Prior to that you could either do such checks on the
connecting session or use some polling mechanism. Neither very elegant...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"MO" <MO@.discussions.microsoft.com> wrote in message
news:E7CC7761-64F0-46D6-99FF-39B8B3287CE8@.microsoft.com...
> Hi,
> We have a reqt to do certain things as soon as a user logins into our
> server. Is there a logon trigger in sql 2k ? For ex. We need to check things
> like how many connections he already has before we allow another connection.
> TIA
> Moe|||TQ Tibor. I am not sure what you mean by polling mechanism. Could you give me
more details on that ?
TIA
Moe
"Tibor Karaszi" wrote:
> No, login triggers was introduced in 2005 sp2. Prior to that you could either do such checks on the
> connecting session or use some polling mechanism. Neither very elegant...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "MO" <MO@.discussions.microsoft.com> wrote in message
> news:E7CC7761-64F0-46D6-99FF-39B8B3287CE8@.microsoft.com...
> > Hi,
> > We have a reqt to do certain things as soon as a user logins into our
> > server. Is there a logon trigger in sql 2k ? For ex. We need to check things
> > like how many connections he already has before we allow another connection.
> > TIA
> > Moe
>|||> TQ Tibor. I am not sure what you mean by polling mechanism. Could you give
> me
> more details on that ?
Well, let's say you don't want more than 10 connections per user, ok? Then
you can create a SQL Server Agent job that wakes up every minute, and does
this:
SELECT
SUSER_SNAME([sid]),
COUNT(DISTINCT spid)
FROM master..sysprocesses
GROUP BY SUSER_SNAME([sid])
HAVING COUNT(*) > 10
ORDER BY 2 DESC;
If rows are found, then take action! I'm not sure what that means in your
case, do you want an e-mail every time this happens so you will know about
it immediately, do you want to try to kill the oldest spid from each login,
do you want to log the information to a table, etc. etc. etc. If you are
using a specific database and there are one or more procedures that are hit
frequently, you could log the information to a table (deleting them every
time, of course), check if the current user is in the "too many connections"
list, and immediately return an error instead of performing the work.
A|||Whoops, you'd probably also want to filter on spid > 50
"MO" <MO@.discussions.microsoft.com> wrote in message
news:DBF47D85-244F-423C-B873-816CF1DB725E@.microsoft.com...
> TQ Tibor. I am not sure what you mean by polling mechanism. Could you give
> me
> more details on that ?|||Thanks. That would work for that reqt. Another reqt is this
If a user connects to the DB say Via MS-Access, I don't want them to access
the DB via MS-access, so kill that process. Same user if he accesses the DB
via SQL Query analyzer, that's ok, allow it. If I set up a job to run every
min, the user could be in the DB before the check happens, damage is already
done. Any ideas on that ?
TIA
Mo
"Aaron Bertrand [SQL Server MVP]" wrote:
> > TQ Tibor. I am not sure what you mean by polling mechanism. Could you give
> > me
> > more details on that ?
> Well, let's say you don't want more than 10 connections per user, ok? Then
> you can create a SQL Server Agent job that wakes up every minute, and does
> this:
> SELECT
> SUSER_SNAME([sid]),
> COUNT(DISTINCT spid)
> FROM master..sysprocesses
> GROUP BY SUSER_SNAME([sid])
> HAVING COUNT(*) > 10
> ORDER BY 2 DESC;
> If rows are found, then take action! I'm not sure what that means in your
> case, do you want an e-mail every time this happens so you will know about
> it immediately, do you want to try to kill the oldest spid from each login,
> do you want to log the information to a table, etc. etc. etc. If you are
> using a specific database and there are one or more procedures that are hit
> frequently, you could log the information to a table (deleting them every
> time, of course), check if the current user is in the "too many connections"
> list, and immediately return an error instead of performing the work.
> A
>|||> Thanks. That would work for that reqt. Another reqt is this
> If a user connects to the DB say Via MS-Access, I don't want them to
> access
> the DB via MS-access, so kill that process. Same user if he accesses the
> DB
> via SQL Query analyzer, that's ok, allow it.
You can check application name by inserting the results of sp_who2 into a
#table, but bear in mind that this value can be spoofed.
> If I set up a job to run every
> min, the user could be in the DB before the check happens, damage is
> already
> done.
You can have it check every second if you want... or at least as many times
per minute as it is possible, given that it will likely take more than a
second to check.
> Any ideas on that ?
Upgrade to SQL Server 2005? Uninstall Access from this guy's machine? Hire
developers you can trust?|||That would work. Sometimes I have seen sp_who2 take a while. Isn't that a
concern ? If it is would I be better of just selecting the columns I want
from sysprocesses into # table ?
TIA
Moe
"Aaron Bertrand [SQL Server MVP]" wrote:
> > Thanks. That would work for that reqt. Another reqt is this
> > If a user connects to the DB say Via MS-Access, I don't want them to
> > access
> > the DB via MS-access, so kill that process. Same user if he accesses the
> > DB
> > via SQL Query analyzer, that's ok, allow it.
> You can check application name by inserting the results of sp_who2 into a
> #table, but bear in mind that this value can be spoofed.
> > If I set up a job to run every
> > min, the user could be in the DB before the check happens, damage is
> > already
> > done.
> You can have it check every second if you want... or at least as many times
> per minute as it is possible, given that it will likely take more than a
> second to check.
> > Any ideas on that ?
> Upgrade to SQL Server 2005? Uninstall Access from this guy's machine? Hire
> developers you can trust?
>|||> That would work. Sometimes I have seen sp_who2 take a while. Isn't that a
> concern ? If it is would I be better of just selecting the columns I want
> from sysprocesses into # table ?
Well, there is no program_name column in sysprocesses, for starters.
In SQL Server 2005, it seems to get this information from an internal view
(sys.sysprocesses_ex) which is off limits even to call, never mind view its
definition...
At this moment I don't have a 2000 system handy where I can check the
definition of sp_who2 on that edition, but you are more than welcome to see
where 2000 got its data for program name by viewing the definition of that
procedure in the master database...
But anyway, what is "a while"? How often is "sometimes"? What is the
likelihood that all three things will happen... a user will log on using
their 11th connection with MS Access, this slow symptom will appear, and
that will be the time they happen to decide to do "damage" and they will do
it before you are made aware that they used their 11th connection? While
more likely than winning the lottery, I don't think this will be a common
scenario at all.
If something is causing sp_who2 to return rows slowly, it will probably
affect a select from sysprocesses as well (either because it is specific to
sysprocesses or because it is environment-wide).
A

Logon to DTS from a different domain

I have a series of DTS's that need to be executed in a certain order and therefore have created a VB app that would execute the DTS's and perform a few administrative funtions for the user if successful.

The DBA for the SQL Server has given me a certain Windows Logon and I must logon via Terminal Server when I need to gain access to the SQL Server Manager. This is where I have created the DTS's - all created and owned by that user logon with certain rights. My VB application will be run from a different user machine on a different domain and therefore has a different logon- normal Windows logon. This user does not have access to the SQL Server and the DTS's need to logon with the same logon as under which it was created.

BUT...
LoadFromSQLServer offers only a SQL or Trusted connection by which one can let the user connect. By using the Windows connection flag, the LoadFromSQLServer function now ignores the logon username & password and posts the normal Windows logon. Obviously, the logon now fails for the user.

How can I let the LoadFromSQLServer funtion logon to the DTS Package with a different domain, username & password than the local Windows Logon information ?

GrahamAnybody out there ?

login with security on records based on data (not tables)

Hi,
Is it possible to define a login on an SQL SERVER that has only access to
certain records in a table based on the data in the records?
For example login LoginA can should only be able to
update/read/insert/delete records with the Field Company = 'A'. LoginB only
those for Company = 'B'.
Is there some way for doing this?
I should be able to do something like this with views (one view for each
login), and define in the views the records they can use. But I would really
need something directly to the table, or if not with only one view (one view
for all the login's).
Is there some way, soem trick or I don't knwo what the produce such a
behaviour?
Any help or hint would be really aprpeciated!
Thanks a lot in advance,
PieterSee if this helps:
http://vyaskn.tripod.com/row_level_security_in_sql_server_databases.htm
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:ugcklHoiEHA.2356@.TK2MSFTNGP10.phx.gbl...
Hi,
Is it possible to define a login on an SQL SERVER that has only access to
certain records in a table based on the data in the records?
For example login LoginA can should only be able to
update/read/insert/delete records with the Field Company = 'A'. LoginB only
those for Company = 'B'.
Is there some way for doing this?
I should be able to do something like this with views (one view for each
login), and define in the views the records they can use. But I would really
need something directly to the table, or if not with only one view (one view
for all the login's).
Is there some way, soem trick or I don't knwo what the produce such a
behaviour?
Any help or hint would be really aprpeciated!
Thanks a lot in advance,
Pieter|||Thanks man! You did a nice job there!! This wil be very helpfull!!!
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:esiJ5RoiEHA.212@.TK2MSFTNGP10.phx.gbl...
> See if this helps:
> http://vyaskn.tripod.com/row_level_security_in_sql_server_databases.htm
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:ugcklHoiEHA.2356@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Is it possible to define a login on an SQL SERVER that has only access to
> certain records in a table based on the data in the records?
> For example login LoginA can should only be able to
> update/read/insert/delete records with the Field Company = 'A'. LoginB
only
> those for Company = 'B'.
> Is there some way for doing this?
> I should be able to do something like this with views (one view for each
> login), and define in the views the records they can use. But I would
really
> need something directly to the table, or if not with only one view (one
view
> for all the login's).
> Is there some way, soem trick or I don't knwo what the produce such a
> behaviour?
> Any help or hint would be really aprpeciated!
> Thanks a lot in advance,
> Pieter
>
>

Login setting by IP range?

Hello,
In MySQL we can set to allow a user to login and access certain
table from certain host, is there anything close to that in MSSQL 2000/2005?
Actually, we just need to control certain users unable to use the
Domain Creditial to access certain databases/tables except in the company's
IP, while we need to make sure for certain databases/tables, the client can
be accessed anywhere.
If not, is there any kind of database connection proxies that we can
use to control the connections, or this there any tutorial on how to relay
database datas so we can write a program to do access check?
Looking forward for reply.
Regards,
Lau Lei Cheong
Lau
How do they (users) access the database? Did you divide them into Groups?
You can DENY access on table for the user .
In addition , you can specify a hostname in connection string.
"Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
news:uXz12iGMGHA.3856@.TK2MSFTNGP12.phx.gbl...
> Hello,
> In MySQL we can set to allow a user to login and access certain
> table from certain host, is there anything close to that in MSSQL
> 2000/2005?
> Actually, we just need to control certain users unable to use the
> Domain Creditial to access certain databases/tables except in the
> company's IP, while we need to make sure for certain databases/tables, the
> client can be accessed anywhere.
> If not, is there any kind of database connection proxies that we
> can use to control the connections, or this there any tutorial on how to
> relay database datas so we can write a program to do access check?
> Looking forward for reply.
> Regards,
> Lau Lei Cheong
>
|||Yes, I know I can divide users into groups but that's not the problem.
The problem is, as the subject line suggested, I can't find way to limit
certain user's access to certain database/table from certain IP.
"Uri Dimant" <urid@.iscar.co.il> glsD:%23aeJm7GMGHA.500@.TK2MSFTNGP15.phx. gbl...
> Lau
> How do they (users) access the database? Did you divide them into Groups?
> You can DENY access on table for the user .
> In addition , you can specify a hostname in connection string.
>
> "Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
> news:uXz12iGMGHA.3856@.TK2MSFTNGP12.phx.gbl...
>
|||Well, have a look at APPLICATION ROLE in the BOL
"Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
news:O2MjaNHMGHA.3960@.TK2MSFTNGP09.phx.gbl...
> Yes, I know I can divide users into groups but that's not the problem.
> The problem is, as the subject line suggested, I can't find way to limit
> certain user's access to certain database/table from certain IP.
> "Uri Dimant" <urid@.iscar.co.il>
> glsD:%23aeJm7GMGHA.500@.TK2MSFTNGP15.phx. gbl...
>
|||Sorry, I've reviewed application role, but it don't help in this case
because:
1) I need to control access by IP, not by application
2) Some users under the control are application developers who can write
their own application.
But your information is really useful on other area, thanks.
"Uri Dimant" <urid@.iscar.co.il> glsD:%23TPXYYHMGHA.2696@.TK2MSFTNGP14.phx .gbl...
> Well, have a look at APPLICATION ROLE in the BOL
> "Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
> news:O2MjaNHMGHA.3960@.TK2MSFTNGP09.phx.gbl...
>

Login setting by IP range?

Hello,
In mysql we can set to allow a user to login and access certain
table from certain host, is there anything close to that in MSSQL 2000/2005?
Actually, we just need to control certain users unable to use the
Domain Creditial to access certain databases/tables except in the company's
IP, while we need to make sure for certain databases/tables, the client can
be accessed anywhere.
If not, is there any kind of database connection proxies that we can
use to control the connections, or this there any tutorial on how to relay
database datas so we can write a program to do access check?
Looking forward for reply.
Regards,
Lau Lei CheongLau
How do they (users) access the database? Did you divide them into Groups?
You can DENY access on table for the user .
In addition , you can specify a hostname in connection string.
"Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
news:uXz12iGMGHA.3856@.TK2MSFTNGP12.phx.gbl...
> Hello,
> In mysql we can set to allow a user to login and access certain
> table from certain host, is there anything close to that in MSSQL
> 2000/2005?
> Actually, we just need to control certain users unable to use the
> Domain Creditial to access certain databases/tables except in the
> company's IP, while we need to make sure for certain databases/tables, the
> client can be accessed anywhere.
> If not, is there any kind of database connection proxies that we
> can use to control the connections, or this there any tutorial on how to
> relay database datas so we can write a program to do access check?
> Looking forward for reply.
> Regards,
> Lau Lei Cheong
>|||Yes, I know I can divide users into groups but that's not the problem.
The problem is, as the subject line suggested, I can't find way to limit
certain user's access to certain database/table from certain IP.
"Uri Dimant" <urid@.iscar.co.il> glsD:%23aeJm7GMGHA.500@.TK2MSFTNGP15.phx.gbl...[vbc
ol=seagreen]
> Lau
> How do they (users) access the database? Did you divide them into Groups?
> You can DENY access on table for the user .
> In addition , you can specify a hostname in connection string.
>
> "Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
> news:uXz12iGMGHA.3856@.TK2MSFTNGP12.phx.gbl...
>[/vbcol]|||Well, have a look at APPLICATION ROLE in the BOL
"Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
news:O2MjaNHMGHA.3960@.TK2MSFTNGP09.phx.gbl...
> Yes, I know I can divide users into groups but that's not the problem.
> The problem is, as the subject line suggested, I can't find way to limit
> certain user's access to certain database/table from certain IP.
> "Uri Dimant" <urid@.iscar.co.il>
> glsD:%23aeJm7GMGHA.500@.TK2MSFTNGP15.phx.gbl...
>|||Sorry, I've reviewed application role, but it don't help in this case
because:
1) I need to control access by IP, not by application
2) Some users under the control are application developers who can write
their own application.
But your information is really useful on other area, thanks.
"Uri Dimant" <urid@.iscar.co.il> glsD:%23TPXYYHMGHA.2696@.TK2MSFTNGP14.phx.gbl...[vb
col=seagreen]
> Well, have a look at APPLICATION ROLE in the BOL
> "Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
> news:O2MjaNHMGHA.3960@.TK2MSFTNGP09.phx.gbl...
>[/vbcol]

Login setting by IP range?

Hello,
In MySQL we can set to allow a user to login and access certain
table from certain host, is there anything close to that in MSSQL 2000/2005?
Actually, we just need to control certain users unable to use the
Domain Creditial to access certain databases/tables except in the company's
IP, while we need to make sure for certain databases/tables, the client can
be accessed anywhere.
If not, is there any kind of database connection proxies that we can
use to control the connections, or this there any tutorial on how to relay
database datas so we can write a program to do access check?
Looking forward for reply.
Regards,
Lau Lei CheongLau
How do they (users) access the database? Did you divide them into Groups?
You can DENY access on table for the user .
In addition , you can specify a hostname in connection string.
"Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
news:uXz12iGMGHA.3856@.TK2MSFTNGP12.phx.gbl...
> Hello,
> In MySQL we can set to allow a user to login and access certain
> table from certain host, is there anything close to that in MSSQL
> 2000/2005?
> Actually, we just need to control certain users unable to use the
> Domain Creditial to access certain databases/tables except in the
> company's IP, while we need to make sure for certain databases/tables, the
> client can be accessed anywhere.
> If not, is there any kind of database connection proxies that we
> can use to control the connections, or this there any tutorial on how to
> relay database datas so we can write a program to do access check?
> Looking forward for reply.
> Regards,
> Lau Lei Cheong
>|||Yes, I know I can divide users into groups but that's not the problem.
The problem is, as the subject line suggested, I can't find way to limit
certain user's access to certain database/table from certain IP.
"Uri Dimant" <urid@.iscar.co.il> ¼¶¼g©ó¶l¥ó·s»D:%23aeJm7GMGHA.500@.TK2MSFTNGP15.phx.gbl...
> Lau
> How do they (users) access the database? Did you divide them into Groups?
> You can DENY access on table for the user .
> In addition , you can specify a hostname in connection string.
>
> "Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
> news:uXz12iGMGHA.3856@.TK2MSFTNGP12.phx.gbl...
>> Hello,
>> In MySQL we can set to allow a user to login and access certain
>> table from certain host, is there anything close to that in MSSQL
>> 2000/2005?
>> Actually, we just need to control certain users unable to use the
>> Domain Creditial to access certain databases/tables except in the
>> company's IP, while we need to make sure for certain databases/tables,
>> the client can be accessed anywhere.
>> If not, is there any kind of database connection proxies that we
>> can use to control the connections, or this there any tutorial on how to
>> relay database datas so we can write a program to do access check?
>> Looking forward for reply.
>> Regards,
>> Lau Lei Cheong
>>
>|||Well, have a look at APPLICATION ROLE in the BOL
"Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
news:O2MjaNHMGHA.3960@.TK2MSFTNGP09.phx.gbl...
> Yes, I know I can divide users into groups but that's not the problem.
> The problem is, as the subject line suggested, I can't find way to limit
> certain user's access to certain database/table from certain IP.
> "Uri Dimant" <urid@.iscar.co.il>
> ¼¶¼g©ó¶l¥ó·s»D:%23aeJm7GMGHA.500@.TK2MSFTNGP15.phx.gbl...
>> Lau
>> How do they (users) access the database? Did you divide them into Groups?
>> You can DENY access on table for the user .
>> In addition , you can specify a hostname in connection string.
>>
>> "Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
>> news:uXz12iGMGHA.3856@.TK2MSFTNGP12.phx.gbl...
>> Hello,
>> In MySQL we can set to allow a user to login and access certain
>> table from certain host, is there anything close to that in MSSQL
>> 2000/2005?
>> Actually, we just need to control certain users unable to use the
>> Domain Creditial to access certain databases/tables except in the
>> company's IP, while we need to make sure for certain databases/tables,
>> the client can be accessed anywhere.
>> If not, is there any kind of database connection proxies that we
>> can use to control the connections, or this there any tutorial on how to
>> relay database datas so we can write a program to do access check?
>> Looking forward for reply.
>> Regards,
>> Lau Lei Cheong
>>
>>
>|||Sorry, I've reviewed application role, but it don't help in this case
because:
1) I need to control access by IP, not by application
2) Some users under the control are application developers who can write
their own application.
But your information is really useful on other area, thanks. :)
"Uri Dimant" <urid@.iscar.co.il> ¼¶¼g©ó¶l¥ó·s»D:%23TPXYYHMGHA.2696@.TK2MSFTNGP14.phx.gbl...
> Well, have a look at APPLICATION ROLE in the BOL
> "Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
> news:O2MjaNHMGHA.3960@.TK2MSFTNGP09.phx.gbl...
>> Yes, I know I can divide users into groups but that's not the problem.
>> The problem is, as the subject line suggested, I can't find way to limit
>> certain user's access to certain database/table from certain IP.
>> "Uri Dimant" <urid@.iscar.co.il> ¼¶¼g©ó¶l¥ó·s»D:%23aeJm7GMGHA.500@.TK2MSFTNGP15.phx.gbl...
>> Lau
>> How do they (users) access the database? Did you divide them into
>> Groups?
>> You can DENY access on table for the user .
>> In addition , you can specify a hostname in connection string.
>>
>> "Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
>> news:uXz12iGMGHA.3856@.TK2MSFTNGP12.phx.gbl...
>> Hello,
>> In MySQL we can set to allow a user to login and access certain
>> table from certain host, is there anything close to that in MSSQL
>> 2000/2005?
>> Actually, we just need to control certain users unable to use
>> the Domain Creditial to access certain databases/tables except in the
>> company's IP, while we need to make sure for certain databases/tables,
>> the client can be accessed anywhere.
>> If not, is there any kind of database connection proxies that we
>> can use to control the connections, or this there any tutorial on how
>> to relay database datas so we can write a program to do access check?
>> Looking forward for reply.
>> Regards,
>> Lau Lei Cheong
>>
>>
>>
>

Login Prompt after ~3min timeout

I have a report which I am running from a web front end, which, when exported
to excel with certain parameters, takes several minutes to run. After
something between 2.5mins and 3mins this will prompt for a username and
password. Providing a valid username and passoword gives you another 3mins
before you get prompted again and so on until you have been prompted 3 times,
after which you get an IIS permission denied message up.
Running a SQL Server trace against the server makes it appear as if the
report restarts running as the stored procedure is re-run (even though this
had completed successfully before the timeout was hit), so the report appears
to start again.
Looking at the Logfiles there are message about orphaned requests and Item
with session ... not found in database.
I have tried increasing the:
Database query timeout to 240 secs - no effect.
The report timeout to unlimited - no effect.
MaxMemoryLimit to 80% - no effect.
ASP timeout to 600 sec - no effect.
I can't see any error messages in the event log.
Does anybody have any idea what is actually timing out, and even where I can
set it.
Please through in random suggestions, I am willing to try anything. BTW
this is happening on both the server which is running SP2 and the one with no
service packs on.
Many thanksI should have added:
It is nothing to do with permissions as this is fine with a smaller dataset,
also I am administrator on, one of the machines on which this problem is
occuring.
Session timeout values are default of 20mins.
"Nicola Jones" wrote:
> I have a report which I am running from a web front end, which, when exported
> to excel with certain parameters, takes several minutes to run. After
> something between 2.5mins and 3mins this will prompt for a username and
> password. Providing a valid username and passoword gives you another 3mins
> before you get prompted again and so on until you have been prompted 3 times,
> after which you get an IIS permission denied message up.
> Running a SQL Server trace against the server makes it appear as if the
> report restarts running as the stored procedure is re-run (even though this
> had completed successfully before the timeout was hit), so the report appears
> to start again.
> Looking at the Logfiles there are message about orphaned requests and Item
> with session ... not found in database.
> I have tried increasing the:
> Database query timeout to 240 secs - no effect.
> The report timeout to unlimited - no effect.
> MaxMemoryLimit to 80% - no effect.
> ASP timeout to 600 sec - no effect.
> I can't see any error messages in the event log.
> Does anybody have any idea what is actually timing out, and even where I can
> set it.
> Please through in random suggestions, I am willing to try anything. BTW
> this is happening on both the server which is running SP2 and the one with no
> service packs on.
> Many thanks|||The problem was caused by the ConnectionTimeout value in machine.config.
This was set to 2mins.
"Nicola Jones" wrote:
> I should have added:
> It is nothing to do with permissions as this is fine with a smaller dataset,
> also I am administrator on, one of the machines on which this problem is
> occuring.
> Session timeout values are default of 20mins.
> "Nicola Jones" wrote:
> > I have a report which I am running from a web front end, which, when exported
> > to excel with certain parameters, takes several minutes to run. After
> > something between 2.5mins and 3mins this will prompt for a username and
> > password. Providing a valid username and passoword gives you another 3mins
> > before you get prompted again and so on until you have been prompted 3 times,
> > after which you get an IIS permission denied message up.
> >
> > Running a SQL Server trace against the server makes it appear as if the
> > report restarts running as the stored procedure is re-run (even though this
> > had completed successfully before the timeout was hit), so the report appears
> > to start again.
> >
> > Looking at the Logfiles there are message about orphaned requests and Item
> > with session ... not found in database.
> >
> > I have tried increasing the:
> >
> > Database query timeout to 240 secs - no effect.
> > The report timeout to unlimited - no effect.
> > MaxMemoryLimit to 80% - no effect.
> > ASP timeout to 600 sec - no effect.
> >
> > I can't see any error messages in the event log.
> >
> > Does anybody have any idea what is actually timing out, and even where I can
> > set it.
> >
> > Please through in random suggestions, I am willing to try anything. BTW
> > this is happening on both the server which is running SP2 and the one with no
> > service packs on.
> >
> > Many thanks|||Sorry that should have said metabase.xml not machine.config.
"Nicola Jones" wrote:
> The problem was caused by the ConnectionTimeout value in machine.config.
> This was set to 2mins.
> "Nicola Jones" wrote:
> > I should have added:
> >
> > It is nothing to do with permissions as this is fine with a smaller dataset,
> > also I am administrator on, one of the machines on which this problem is
> > occuring.
> >
> > Session timeout values are default of 20mins.
> >
> > "Nicola Jones" wrote:
> >
> > > I have a report which I am running from a web front end, which, when exported
> > > to excel with certain parameters, takes several minutes to run. After
> > > something between 2.5mins and 3mins this will prompt for a username and
> > > password. Providing a valid username and passoword gives you another 3mins
> > > before you get prompted again and so on until you have been prompted 3 times,
> > > after which you get an IIS permission denied message up.
> > >
> > > Running a SQL Server trace against the server makes it appear as if the
> > > report restarts running as the stored procedure is re-run (even though this
> > > had completed successfully before the timeout was hit), so the report appears
> > > to start again.
> > >
> > > Looking at the Logfiles there are message about orphaned requests and Item
> > > with session ... not found in database.
> > >
> > > I have tried increasing the:
> > >
> > > Database query timeout to 240 secs - no effect.
> > > The report timeout to unlimited - no effect.
> > > MaxMemoryLimit to 80% - no effect.
> > > ASP timeout to 600 sec - no effect.
> > >
> > > I can't see any error messages in the event log.
> > >
> > > Does anybody have any idea what is actually timing out, and even where I can
> > > set it.
> > >
> > > Please through in random suggestions, I am willing to try anything. BTW
> > > this is happening on both the server which is running SP2 and the one with no
> > > service packs on.
> > >
> > > Many thanks