User Schema separation SQL Server 2005

As we know Schemas are very important in SQL Server. They play an important part in names of objects, Schemas work like container and contain DB Objects and they are securable. Due to the separation of users and schemas in SQL Server, many functions, design rules and other things of SQL Server that seem familiar have changed a lot and need to be re-considered.

For Novice users, while in earlier versions a schema owner could not change because it was linked permanently to a specific user, now the schema ownership can be changed whenever you want. This doesn’t mean that we were not able to change object ownership in earlier versions, but in doing so, the schema itself needs to change which was resulting in to changing the fully qualified name of the object.

In addition, now in SQL Server 2005 onwards a schema can contain objects owned by multiple users. As we’ve already discussed Schema in SQL Server 2005 is working as a container and acting as a securable as well. This means you can have three tables in a single schema where each table owner is different and still you can assign the ownership of the schema to the fourth User.

In earlier versions of SQL Server whenever a user was created SQL was also creating a corresponding schema for it, where both of them were tightly coupled and the corresponding schema was the default schema of the user. However, a new user in SQL Server 2005 onwards does not result into a new schema creation, but you must set a default schema for the user. It is also possible to set the same schema as the default for multiple users.

CREATE SCHEMA

CREATE SCHEMA statements that do not specify a schema name do not actually create a schema inside the database, but they do create tables and views, and grant permissions. Principals do not need CREATE SCHEMA permission to execute this earlier form of CREATE SCHEMA, because no schema is being created.

The syntax for SQL Server 2000

<pre class="brush:c-sharp">CREATE SCHEMA AUTHORIZATION owner 
    [ < schema_element > [ ...n ] ]
 
< schema_element > ::= 
   { table_definition | view_definition | grant_statement }</pre>

But in SQL Server 2005 & onwards you can use CREATE SCHEMA statement just to create a schema.

Example: (SQL Server 2005)

The following example creates schema Sales owned by Sarab that contains table North_Saletarget. The statement grants SELECT to Tom and denies SELECT to Peter. Note that Sales and North_Saletarget are created in a single statement

USE Testdb;
 
CREATE SCHEMA Sales AUTHORIZATION Sarab
 
    CREATE TABLE North_salestarget (product_id int, cos_pricet int, partnumber int..n)
 
    GRANT SELECT TO Tom

    DENY SELECT TO Peter;
 
GO

Statements that contain CREATE SCHEMA AUTHORIZATION but do not specify a name are still permitted for backward compatibility only.

CREATE SCHEMA transactions are atomic. If any error occurs during the execution of a CREATE SCHEMA statement, none of the specified securable will be created and no permissions will be granted. (This applies for both SQL Server 2000 & 2005)

New Catalog Views

Beginning with SQL Server 2005, schemas are explicit entities; and as a result, schemas can only have one owner but a single user can own many schemas. This complex relationship is not reflected in the SQL Server 2000 system tables, so SQL Server 2005 introduced new catalog views, which accurately reflect the new metadata.

The following table below shows the mapping between the SQL Server 2000 system tables and the equivalent SQL Server 2005 and later catalog views.

In SQL Server 2005 onwards if any application creates objects dynamically they can do so without dbo permissions, so the object would be added to the dbo schema. This strictly follows and allows us to implement the principle of least privilege.

Attention:

SP_adduser: Please do not use this sp to create users in SQL Server 2005, because this acts in the same way as it was working in earlier versions. i.e., it will create a new user as per your details and automatically creates corresponding schema with the same name and assign ownership of this schema to the user.

Even if you are using following SPs to do the job it will do the same.

EXEC master.dbo.sp_addlogin @loginame = @user, @passwd = @user, @defdb = testdb
 
use testdb

EXEC dbo.sp_grantdbaccess @loginame = @user, @name_in_db = @user

Solution

I would recommend you to use Create User T-SQL Command to create new users, which will create the user only.

Regards

Sarabpreet Anand

Like us on FaceBook  |  Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook