Regular user slow login

[danvlas]

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.



Daniel Vlas
Systems Consultant

 

[yelworcm]

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.

 

[SQLBill]

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

 

[danvlas]

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=sql.110).aspx

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...




Daniel Vlas
Systems Consultant