Interesting findings on schema owner permissions

I found out recently that a schema owner does not necessarily have right to create objects for its own schema, but can drop and grant permissions for its objects, which is kind of strange.

Try this:

1. Create 2 logins:

USE [master]
GO
CREATE LOGIN [Login1] WITH PASSWORD=N’xxx’, DEFAULT_DATABASE=[AdventureWorks], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
CREATE LOGIN [Login2] WITH PASSWORD=N’xxx’, DEFAULT_DATABASE=[AdventureWorks], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
GO
USE [AdventureWorks]
GO
CREATE USER [Login1] FOR LOGIN [Login1]
CREATE USER [Login2] FOR LOGIN [Login2]
GO

2. Grant create schema permissions to both Login1 and Login2.

use adventureworks
go
grant create schema to login1
grant create schema to login2
go

3. Connect to Sql Server as login1, do

create schema Login1Schema

4. With the same connection, do

create table Login1Schema.T1 (C1 int)

and it will fail. It is a little strange. Login1 is indeed the owner of the schema, yet it cannot create objects within the schema it owns.

5. Connect to Sql Server as sa, do

Use AdventureWorks
go

create table Login1Schema.TableCreatedBySa (C1 int)
go

grant create table to Login1
go

6. Connect to Sql server as Login1, do

create table Login1Schema.T1 (C1 int)
go

grant select on Login1Schema.T1 to Login2
grant select on Login1Schema.TableCreatedBySa to Login2
go

7. Connect to Sql server as Login2, do

select * from Login1Schema.T1
select * from Login1Schema.TableCreatedBySa
go

8. In the same session, do

create schema Login2Schema
go

9. Connect to Sql Server as sa, do

use AdventureWorks
go
create table Login2Schema.T1 (C1 int)
go

10. Connect to Sql Server as Login2, do

select * from Login2Schema.t1
go
drop table Login2Schema.T1
go
drop schema Login2Schema
go

So, what did our little test prove?

1. By default, a schema owner cannot create objects in its own schema;
2. By default, a schema owner can grant permissions to other users on objects it owns;
3. By default, a schema owner can drop objects it owns, even if it cannot create them in the first place;
4. By default, a schema owner can drop its own schema, provided it is empty.

To clean up, drop all those tables, users, schemas, and logins.

2 thoughts on “Interesting findings on schema owner permissions”

  1. The oddities with Schemas don’t stop there, but nice post. I know you have a significant amount of Oracle experience like me – don’t Schemas in SQL Server give you a headache, especially when trying to explain them to others?

    My personal advice to those out there – either use Schema level permission for *everything*, or just use the DBO schema and object-level permissions. I’ve already run into one case where a client had implemented both, with liberally use of DENYs. Talk about a tangled web…

  2. Absolutely, it sure does create a headache when explaining that to Oracle users 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.