Sql-server – Query is slow for certain users

Taking @Mr.Brownstone's idea, you could definitely try this:

DECLARE @sql nvarchar(MAX) = N'';

SELECT @sql += N'
  ALTER SCHEMA dbo TRANSFER custom_schema.' + QUOTENAME(name) + ';'
  FROM sys.objects
  WHERE SCHEMA_NAME([schema_id]) = N'custom_schema';

EXEC sys.sp_executesql @sql;

Now, it may be the case that you have foreign keys etc. that will need to be dropped and re-created (and I've shown how to automate that too), or as I mentioned in a comment, you may have explicit references to custom_schema in your existing objects. You may also have duplicate objects (say, dbo.Customers and custom_schema.Customers) that you'll need to handle. But the general idea should be sound.

Another workaround would be to upgrade to SQL Server 2012, where you can set a DEFAULT_SCHEMA for a domain group. I haven't tested this, but this Connect item says it has been made so.

In either case, I still think the app should be fixed eventually. As I've mentioned many times before, you should ALWAYS use an explicit schema prefix when creating or referencing objects.

2 possible reasons:

1. User A receives necessary permissions via his membership in some other group, or explicitly being a member of server / database role, or has permissions granted directly.
2. User B is denied some permissions, again - either via his membership in some other group, or explicitly being a member of server / database role, or has these permissions denied to him directly.

You can compare their role memberships on the database / server level, but membership in Windows groups cannot be checked via SQL Server - you would have to look in local groups AND Active Directory, if the machine is a member of a domain.

EDIT: here is a set of queries that may help you track down actual origins of permissions. Just make sure that you run them under the user with appropriate credentials, otherwise you will never see anything. Membership in sysadmin fixed server role is preferable.

declare @ObjectName sysname = N'YourObjectName',
    @UserName sysname = N'YourUserName';

-- Grants on database securable
select schema_name(ao.schema_id) as [SchemaName], ao.name, ao.type_desc,
    pr.type_desc, pr.name as [Principal], dp.permission_name, dp.state_desc
from sys.database_permissions dp
    inner join sys.all_objects ao on ao.object_id = dp.major_id
    inner join sys.database_principals pr on pr.principal_id = dp.grantee_principal_id
where dp.class_desc = 'OBJECT_OR_COLUMN'
    and ao.name = @ObjectName;

-- Direct permissions of database principal
select schema_name(ao.schema_id) as [SchemaName], ao.name, ao.type_desc,
    pr.type_desc, pr.name as [Principal], dp.permission_name, dp.state_desc
from sys.database_permissions dp
    left join sys.all_objects ao on ao.object_id = dp.major_id
    inner join sys.database_principals pr on pr.principal_id = dp.grantee_principal_id
where pr.name = @UserName;

-- Membership in database roles
select rl.name as [DatabaseRole]
from sys.database_role_members rm
    inner join sys.database_principals rl on rl.principal_id = rm.role_principal_id
    inner join sys.database_principals pr on pr.principal_id = rm.member_principal_id
where pr.name = @UserName;

-- Membership in server roles
select rl.name as [ServerRole]
from sys.server_role_members rm
    inner join sys.server_principals rl on rl.principal_id = rm.role_principal_id
    inner join sys.server_principals pr on pr.principal_id = rm.member_principal_id
where pr.name = @UserName;

The only thing not covered here is a membership in Windows / Active Directory security groups, but, as I've said, it is impossible to track it within SQL Server.