Monday, February 13, 2012 2:38 PM
we have migrated our database from mssql 2005 to mssql 2008 r2 x64 SP1. We have a problem with this query which takes 18 seconds. It causes that our application hangs for 18 seconds.
This is the query:
select v.name, schema_name(v.schema_id), v.type, cast(case when v.is_ms_shipped = 1 then 1when (select major_id from sys.extended_properties where major_id = v.object_id and minor_id = 0 and class = 1 and name = N'microsoft_database_tools_support')is not null then 1 else 0 end as bit),ObjectProperty(v.object_id, N'IsSchemaBound') from sys.all_objects AS v where v.type = N'V' and permissions(v.object_id) & 4096 <> 0
I think, that this has somethig to do to with security. If users are dboweners so this query is not being called and performance is good. For authetication we use domain groups. We didn't have these problems in mssql 2005. Our database compatibility level is set to SQL Server 2000, because we started on mssql 2000. According to developers it shouldn't be a problem.
Monday, February 13, 2012 10:39 PM
Tuesday, February 14, 2012 2:56 PM
You mentioned migrating to SQL Server 2008 R2. What is your frontend program coded in? Is it a Microsoft Access frontend?
The PERMISSIONS function is being deprecated and other faster functions are being provide now. See:
I asked about Microsoft Access since even Access 2010 still uses the PERMISSIONS function to determine to which objects the login has access.
Wednesday, February 15, 2012 9:11 AM
You are right. Our aplication is written in MS ACCESS. We started in Access 2003, and now we have Access 2010.
I will try to investigate if they use deprecated function.
Thursday, February 16, 2012 1:30 PM
I can assure you that they are using the deprecated function and that they know that they are still using it. (It is doubtless embedded in the Microsoft Access code somewhere and would take time to replace and redistrubute the code.)