Sql-server – what is the first schema checked

sql-server-2005sql-server-2008

I am a little confused right now. Can someone answer me with a simple answer to this question.

I have a user [user_test] and has [db_owner] role assigned to him for one database [database_test].
The user's default schema is [user_test] and almost all objects from the [database_test] are created under schema [user_test]. In the database exist 3 more objects with schema [dbo]

The user has rights to both schemas: [dbo] and [user_test] without explicitly specifying them in select statements in front of objects reference.

What is the first schema checked by SQL when an object is called by user [user_test] ?

I mean, If [user_test] issues a select for an object from dbo schema (without schema in front), will SQL first check in it's default schema specified and then in dbo?

thanks,

Best Answer

With SQL Server 2005 & 2008 the first schema that is searched is the users default schema.

from SQL Server Best Practices – Implementation of Database Object Schemas

Default Schema

Users can be defined with a default schema. The default schema is the first schema that is searched when it resolves the names of objects it references.

and

creating the base tables in the dbo schema will enable table objects to be referred to without an explicit schema name. This is because an object will be located by searching the default schema first, followed by the dbo schema. So:

SELECT * FROM Table1

Will assess the following statement first:

SELECT * FROM <defaultschema>.Table1

If it cannot find the object, the server will assess the following statement:

SELECT * FROM dbo.Table1

The assessment process can be improved by using either the fully qualified name or the DEFAULT_SCHEMA option described earlier. By setting a value for DEFAULT_SCHEMA for the user, the server will check the DEFAULT_SCHEMA first, removing an unnecessary ownership checking process.

This can improve performance considerably on heavily utilized systems.

Default Schema was introduced in SQL Server 2005 Upgrading to SQL Server 2005 and DEFAULT_SCHEMA setting

Related Solutions

Sql-server – The SELECT permission was denied on the (View) object after explicit grant select on view to user

Your response pointed me in the right direction.

After looking at the object and verifying the permissions many times, I shifted my focus to the Login and User.

Apparently the login is mapped to a different user. So when I checked the server_principal against the database_principal there was a mismatch.

The login is called appuser and i am also trying to grant permissions inside the database to appuser. Unfortunately someone created a different database user which is actually called appwebuser.

So once i discovered that we were granting permissions to the wrong user and update the code to the correct database user the problem was resolved.

I used the following query to track down the login. I add in the servername and dbname so i can check against my different environments and report to the customer.

SELECT 
@@servername  as [server_name]
,db_name()  as [database_name]
,sp.name as [server_principal_name]
,sp.type as [server_principal_type]
,sp.type_desc as [server_principal_type_desc]
,sp.create_date as [server_principal_create_date]
,sp.default_database_name as [server_principal_default_database_name]
,dp.name as [database_principal_name]
,dp.type as [database_principal_type]
,dp.type_desc as [database_principal_type_desc] 
,dp.default_schema_name as [database_principal_default_schema_name]
FROM 
    sys.database_principals  dp INNER JOIN
    sys.server_principals sp ON (dp.sid = sp.sid)
WHERE 
    sp.name like 'appuser'
    or dp.name like  'appuser'
    or sp.name like 'appwebuser'
    or dp.name like  'appwebuser'

Sql-server – Trying to better understand how SELECT rights are managed in a/the SQL Server db

You need to look at the Effective Permissions http://msdn.microsoft.com/en-us/library/ms178358.aspx

I've never understood why there isn't a version of fn_my_permissions http://msdn.microsoft.com/en-us/library/ms176097.aspx which simply takes a user.

To do that you have to EXECUTE AS the other user: http://sqlarea.blogspot.com/2008/04/fnmypermissions.html

Best Answer

Related Solutions

Sql-server – The SELECT permission was denied on the (View) object after explicit grant select on view to user

Sql-server – Trying to better understand how SELECT rights are managed in a/the SQL Server db

Related Question