INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Regular user slow login

Regular user slow login

(OP)
Hi there,

Here is my problem:

SQL Server 2008 R2.
MS Access Project(adp) application connected to the server.
SQL Server Authentication.

If dbo logs in, first application query starts in 2-3 seconds
If regular user logs in, first query starts after 30 seconds. After that, everything is fine.
If regular user is promoted to dbo, 3 seconds. Then demoted to his original rights, 30 seconds.
New user, regular privileges, 30 seconds.
I'm talking about the same environment for both and even the same computer from where we log in to the database.

I can't understand why and it's really frustrating.

Any idea would be highly appreciated.

Thanks a lot.

pipe
Daniel Vlas
Systems Consultant

RE: Regular user slow login

Run a profiler trace on the server to see what statement is taking so long on the server side. You will likely want to monitor a dbo user as well to compare it with the non-dbo. My guess is some sort of error handling is involved due to the lower permissions, but I am not sure anyone would set up an error handler to soak up so much time.

RE: Regular user slow login

There might be a trigger that 'audits' non-dbo users. As yelworcm suggested, start a trace and see what it captures.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875: What should I know before I post?

RE: Regular user slow login

(OP)
Thank you for trying to help me out.

This is what Access seems to be sending to the server:

--=================================================
declare @data1 datetime, @data2 datetime, @ms int

select @data1=GETDATE()
select object_name(id), user_name(uid), type, ObjectProperty(id, N'IsMSShipped'), ObjectProperty(id, N'IsSchemaBound') from sysobjects where type = N'V' and permissions(id) & 4096 <> 0
select @data2=GETDATE()

select @ms=DATEDIFF(ms,@data1,@data2)
select @ms as ms
--==================================================

If connected as a dbo, it takes 243 miliseconds and returns 2321 rows
If connected as normal user, it takes 27143 miliseconds and returns 838 rows.

I suppose it's listing the views on which the user has Select permissions to display them in the database window.

I came across this article:
http://msdn.microsoft.com/en-us/library/ms186915(v...

But how on earth can I convince Access NOT to send this query containing PERMISSIONS(), since it does it totally behind the scene? And the database window is hidden, so I don't really need to display all SQL objects in it...

pipe
Daniel Vlas
Systems Consultant

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close