Home » Blog » CONTROL SERVER vs. sysadmin – Differences when Creating a Schema

CONTROL SERVER vs. sysadmin – Differences when Creating a Schema

2014-03-07 - CONTROL SERVER vs. sysadmin, General, Security, Security Pitfalls

Introduction

In SQL Server 2005 Microsoft introduced the CONTROL SERVER permission. It was supposed to replace the sysadmin fixed server role. However, even in SQL Server 2012 there are still significant differences between the two.

In this post, we are going to look at one difference that shows, when creating an object or other securable inside a database.

CREATE SCHEMA

For the examples in this post I am going to use the CREATE SCHEMA statement. However, everything you will see, applies to other database securables too.

To get started we need to logins to play with:

[sql] CREATE LOGIN TestLogin1 WITH PASSWORD='********',CHECK_POLICY=OFF;
CREATE LOGIN TestLogin2 WITH PASSWORD='********',CHECK_POLICY=OFF;
[/sql]

In Who is the Default Owner of your Database and Server Objects? we discovered that a schema created by a member of the sysadmin fixed server role has the user dbo as owner. That seems to be a good idea, as it allows the administrator to create objects in any database without having to have an associated user inside that database. Let us quickly rerun that example:

[sql] ALTER SERVER ROLE sysadmin ADD MEMBER TestLogin1;
GO
EXECUTE AS LOGIN='TestLogin1';
GO
CREATE SCHEMA TestSchema1;
GO
REVERT
GO
SELECT S.name,USER_NAME(S.principal_id) AS owner_name
FROM sys.schemas AS S WHERE name LIKE 'Test%';
[/sql]

This produces the following result:

The schema is created and dbo is set as the owner.

Now let us see what happens if we do the same, but with a login that has the CONTROL SERVER permission and is not member of the sysadmin fixed server role:

[sql] EXEC('USE master; GRANT CONTROL SERVER TO TestLogin2;');
GO
EXECUTE AS LOGIN='TestLogin2';
GO
CREATE SCHEMA TestSchema2;
GO
REVERT
GO
SELECT S.name,USER_NAME(S.principal_id) AS owner_name
FROM sys.schemas AS S WHERE name LIKE 'Test%';
[/sql]

The GRANT statement in this example is wrapped in an EXEC statement, as the current database has to be master to be able to grant server level permissions. The effect of USE master; inside a dynamic SQL string is local to that string. After the EXEC statement finishes, the original database context is reestablished.

The result produced by the above SQL block looks like this:

Hold on, we created only one schema. Why do we have suddenly three? TestSchema1 was created in the previous example, so that one is accounted for. TestSchema2 is the one we wanted to create so that one is good too. TestLogin2 is the login we used. - But wait, why do we have a login show up in the sys.schemas catalog view?

To explain what is going on here we need to look at the owner of TestSchema2. TestSchema2 is owned by TestLogin2. TestLogin2 however does not refer to our login, as a login cannot own a database securable. Therefore, it must be a user. We did not create that user explicitly and it did not exist before we executed the CREATE SCHEMA statement. That means that the CREATE SCHEMA statement, when executed by a login with CONTROL SERVER permission, sets the owner of the new schema to the user associated with that login. However, if that login does not exist, it is automagically created.

That implicit creation of the user goes an additional step and creates a schema with the same name too. I assume this was done, because every user has to have a default schema. Instead of just selecting an existing one (like dbo) a new one is created, one that the new user owns and controls.

Summary

When creating a database securable, a login with CONTROL SERVER permission behaves different from a sysadmin member. Instead of assigning dbo as the user of the new securable, the associated user of the login is used. If that user does not yet exist, it is created automatically. This step also creates a new schema under the same name and sets it as default schema for the new user.

While the examples in the post used the CREATE SCHEMA statement, the same behavior can be observed when creating other securables in the database.