Re: Default schema not working properly for AD groups

Hi Dan
Thanks for the repro
I added
CREATE SCHEMA test AUTHORIZATION [MyDomain\Domain Users]

and then

EXECUTE AS LOGIN = 'MyDomain\SomeUser';
CREATE TABLE test.Test(TestCol int);






"Dan Guzman" <guzmanda@xxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:DC00A4AB-4EBA-470C-8A1D-A8C4D9E2421D@xxxxxxxxxxxxxxxx
Can you post the script to be tested?

Here's a repro from my SQL 2005 SP2 system;

USE MyDatabase
CREATE LOGIN [MyDomain\Domain Users] FROM WINDOWS;
CREATE USER [MyDomain\Domain Users];
GRANT CREATE TABLE TO [MyDomain\Domain Users];
EXECUTE AS LOGIN = 'MyDomain\SomeUser';
CREATE TABLE Test(TestCol int);
SELECT * FROM sys.tables;
SELECT * FROM sys.schemas WHERE name = user;
REVERT;


--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Uri Dimant" <urid@xxxxxxxxxxx> wrote in message
news:uN528dhbIHA.5976@xxxxxxxxxxxxxxxxxxxxxxx
Camilo
According to SQL 2005 BOL, if a database user is mapped to an AD group,
the
default schema cannot be set and it will default to dbo.

A database user is mapped to Windows/SQL Login. Windows Login is a
domain account.
By deafult the objects are created with DBO schema. Can you post the
script to be tested?




"Camilo" <Camilo@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F66B31E7-B929-4BD5-8BA2-A209CD03DB5A@xxxxxxxxxxxxxxxx
I have a customer that is trying to use AD groups to manage permissions
for a
SharePoint solution.

According to SQL 2005 BOL, if a database user is mapped to an AD group,
the
default schema cannot be set and it will default to dbo.

We have done several tests and this is NOT the case. What really happens
is
that a new schema is created for every member (account) of the group
that
logs in and that schema becomes the default schema for each user.

We are running SQL 2005 SP2.

Is this problem addressed in some hotfix or is the product working as
designed? Any ideas?

Thanks!

Camilo






.

Relevant Pages

  • Re: How many objects are owned by schema
    ... CREATE USER no_login WITHOUT LOGIN ... Schema and objects must be owned by a valid database principal but the database principal doesn't necessarily need to be associated with a login for ownership purposes. ... Tibor Karaszi, SQL Server MVP ... ALTER AUTHORIZATION ON OBJECT::dbo.testtable TO myself; ...
    (microsoft.public.sqlserver.programming)
  • Re: How many objects are owned by schema
    ... "Dan Guzman" wrote in message ... CREATE USER no_login WITHOUT LOGIN ... Schema and objects must be owned by a valid database principal but the database principal doesn't necessarily need to be associated with a login for ownership purposes. ... Tibor Karaszi, SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: How many objects are owned by schema
    ... CREATE USER no_login WITHOUT LOGIN ... "Note that an owner can be any database principal and does not necessarily need to be associated with a login." ... ALTER AUTHORIZATION ON OBJECT::dbo.testtable TO myself; ... I am assuming a principle owning an object is different that a schema owing an object. ...
    (microsoft.public.sqlserver.programming)
  • Re: Default schema not working properly for AD groups
    ... CREATE LOGIN FROM WINDOWS; ... SQL Server MVP ... By deafult the objects are created with DBO schema. ...
    (microsoft.public.sqlserver.security)
  • user/schema problem in SQL Server 2005!
    ... 1:Use Manage Studio login the server with Integrated security. ... 2:Create a dabase named testdb; ... 4:Create a SQL Server login named guxiaobo,set it's default databse to ... 7:In database testdb create a schema schema1 owned by dbo; ...
    (comp.databases.ms-sqlserver)
Loading