I had this notion that the only thing a user permission influenced was whether or not the user can perform a certain operation.
A few months ago we ran into a problem when migrating one of the applications and its associated database. Once we had moved to the new SQL server 2012 instance, a SSIS package was running about 100% slower than before.
The only difference seemed to be the infrastructure (Storage and Compute) and the fact that we had hardened the security on the SQL Server instance – reducing unnecessary permissions by removing user from the db_owner role.
Who did this???
Several profiler runs later we narrowed down the difference on just 1 query –
if not exists (SELECT 1 FROM sys.indexes WHERE index_id=1 and OBJECT_NAME(object_id) = '[schemaname].[tablename]' ) /* where [schemaname] and [tablename] change between each call.*/
This query was taking about a second on the SQL Server 2k8 R2 instance and about 8 seconds on the new environment L [the application was creating multiple schema and thousands of tables on the fly and applied indexes to help reporting. The above query was called about 100 times during execution..]
If I moved the user back to db_owner role then the query performed again at sub 1sec.
What is happening here?
When the user is in the db_owner role, the db engine is able to skip a number of steps to validate if the user has access to the table and if it can query the indexes.
Bonus Round
The query itself has a bug and will always return an empty recordset.
Object_Name(object_id) only returns the object name and not a 2 part notation that is mentioned on the right hand side (Schema.TableName)
The correct query should really have been
if not exists (SELECT 1 FROM sys.indexes WHERE index_id=1 and object_id = Object_ID('[schemaname].[tablename]')
This will also help to improve the execution time for the query.
Recent Comments