Sql-server – SQL Server Schemas ownership

sql-server-2008

I'm currently learning SQL Server 2008 and have come across schemas and there is one thing I don't understand about them that they have to have an owner; why can't they own themselves?

My background is mostly with Java and it sounds like a schema is like a package which does not have an owner.

Best Answer

Every object in SQL Server has to be owned by someone. Ownership is used to control permissions. For example, any object that you own you have direct access to without it having to be granted to you. An other example is a procedure that has access to any object owned by the same owner. This allows someone with only access to the procedure to execute it even if it is accessing objects that are owned by someone else.

A schema is a grouping mechanism that most other RDBMSs dont have. (If an other RDBMS refers to a "schema" it means something more like what we call a "database".) Schemata allow to group objects of different types together into logical units within a single database. By default, the ownership of the schema is passed through directly to all objects in that schema. If you change the owner of the schema, the owner of all objects within changes too. (this can be changed on an object by object basis however.)

On the other end, you can grant access to a schema which in turn grants access to all objects within. This makes managing permissions a lot simpler.

Be aware, that you should always fully qualify every object with schemaname.objectname. The reason for this is two fold:

Performance: SQL Server tries to figure out the schema of an object that was referenced without schema specification based on different settings and rules. This burns precious cpu cycles unnecessarily.
While rare, it can lead to security and other access problems because the wrong object might be selected by SQL Server if multiple objects with the same name exist in different schemata.

Related Solutions

Sql-server – How to run multiple concurrent versions of the application against the same database

Interesting mess. Multiple options if you're looking for model refactoring (recommended). If it's not the case/there are time constraints you can try to exploit EXEC. There's neat feature that is not very popular (maybe it's good). Core concept:

DECLARE @cmd NVARCHAR(4000)
SET @cmd = N'dbo.YourProcedure_v11'

DECLARE @retcode INT
EXEC @retcode = @cmd @Param1 = 1,
                     @Param2 = 2,
                     @ParamN = 'N'

You have to create stub procedure ('master' accessed from DAL) and call your procedures depending on user e.g.:

CREATE PROCEDURE dbo.YourProcedure
(
    @Param1 INT,
    @Param2 INT,
    @ParamN CHAR(1),
    @UserID INT
)   
AS
BEGIN

    SET NOCOUNT ON
    DECLARE @cmd NVARCHAR(4000)

    IF @UserID = 1
        SET @cmd = N'dbo.YourProcedure_v11'
    ELSE
        SET @cmd = N'dbo.YourProcedure_v12'

    DECLARE @retcode INT
    EXEC @retcode = @cmd @Param1 = @Param1,
                    @Param2 = @Param2,
                    @ParamN = @ParamN
    RETURN @retcode

END

Instead of passing UserID every time/to all procedures - you can use suser_sname etc depending on your security model. Also user-version mappings should be somewhere in database - stored procedure names are hardcoded only for testing.

Sql-server – Giving selective permissions in a database

You'll have to grant them appropriate permissions at the database level to indicate what they should be able to do. Then grant ALTER permission (or make them the owner) on the schemas in which they can do these things.

GRANT CREATE TABLE, CREATE PROCEDURE TO [AD\dbusersgroup_limitedrights]
GRANT ALTER ON SCHEMA::[DIS] TO [AD\dbusersgroup_limitedrights]

Best Answer

Related Solutions

Sql-server – How to run multiple concurrent versions of the application against the same database

Sql-server – Giving selective permissions in a database

Related Question