Deny create schema still does not work in 2k8

You can't assign a default schema for a user that maps to a Windows group. OK fine. But if you create a user for that group anyway with no default schema, any objects created by members of that group will automatically cause a schema to be created for them, EVEN WHEN THAT USER HAS BEEN EXPLICITLY DENIED THE CREATE SCHEMA PERMISSION IN THE DATABASE. This is the same as it was in 2K5, but has still not been fixed in 2k8.

To reproduce, do the following steps:
Create a windows group, either in the doman or local to the box.
Add at least one Windows user to that group.
Create a SQL login for that Windows group.
Map that login to a new user in some database.
Explicitly 'deny create schema to ' that group/user.
Grant 'create procedure' to that group/user.
Log onto SQL with a Windows login that is a member of that Windows group
Use the database that we set the permissions in above.
Create a dummy stored procedure without qualifying the name with a schema ie: 'create prodedure test1 as select 'hello world').
Look to see what you ended up with. You will have a new schema named the same as the user who created the sproc. This will now be their default schema implicitly, and the test1 stored procedure will be in that schema, EVEN WHEN THAT USER WA EXPLICITLY DENIED THE CREATE SCHEMA PERMISSION IN THE DATABASE.

Could you please fix this?

Actually what you are describing is implicit user creation. It is working by design and as described in BOL (please refer to the remarks section in USER_ID topic http://msdn2.microsoft.com/en-us/library/ms181466.aspx). This functionality is there mainly to preserve backwards compatibility when a Windows user doesn’t have an explicitly created user.

During the implicit user creation, it is the system creating a Windows principal and schema (following the SQL Server 2000 behavior) for a user that is taking an action on the database that requires an owner (in your scenario creating a SP) in order to mark the appropriate owner. Even more, this newly created schema is the default schema for the implicit user (again, for backwards compatibility).

For example, if you look at the newly created SP you mentioned, you will notice that the proper owner is a newly created user (Windows user) that maps to the Windows principal who created it:

CREATE DATABASE [db_Demo_ImplicitUser]

go

USE [db_Demo_ImplicitUser]

go

CREATE USER [DOMAIN\windows_group]

go

DENY CREATE SCHEMA TO [DOMAIN\windows_group]

go

GRANT CREATE PROCEDURE TO [DOMAIN\windows_group]

go

-- Using execute as login to mimic the behavior of

-- connecting as this principal

--

EXECUTE AS LOGIN = 'DOMAIN\windows_user'

go

-- It will return 0 == public

-- but user_name() knows this principal got access via a group

-- and handles the scenario using the Windows name directly

--

SELECT user_id(), user_name( user_id() ), user_name()

go

CREATE PROC sp_demo

as

print 'test'

go

-- In which schema was it created?

--

EXEC [DOMAIN\windows_user].[sp_demo]

go

-- Try the user_id test again and see that now this principal

-- is mapped to a DB principal

--

SELECT user_id(), user_name( user_id() ), user_name()

go

SELECT * FROM sys.database_principals

SELECT * FROM sys.objects

go

-- This call should fail

--

CREATE SCHEMA [myOwnSchema]

go

One point that is important to emphasize is that the user doesn’t have the ability to create arbitrary schemas (i.e. call CREATE SCHEMA).

Hopefully this brief explanation will help to explain the reasons behind this behavior; but please let us know is you have any additional feedback on this topic, we really appreciate it.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Yes, I am aware that it is now documented since it was done so in part due to my opening an incident with MS that this behavior happened at all in 2K5. There "solution" was simply to document it and close the indicent. But that doesn't fix it. It's illogical and breaks security. I have no way to deny schema creation. That's broken and needs to be fixed. Period.