locked
User added to the database when part of a domain group with database access RRS feed

  • Question

  • Hi All,

    I am running in to a strange issue. I have a database where i have added several domain groups only. One of my users, is part of one of these groups and this group has db_owner permission to it.

    When they install an application using his account, his domain account gets explicitly added to the database as a user and the objects get created with his domain name as the schema, instead of 'dbo.'

    Any idea why his account is getting added explicitly to the database when he should already be coming in / authorized as part of a domain group? Using SQL 2005..

    Thanks in advance

    Tuesday, August 28, 2012 5:21 PM

Answers

  • Because an object need to be owned by "somebody", and having a (windows) group as an an owner does jive (apparently, as seen by how SQL Server works). So, when a user whi in windows is a group creates an object, exactly this happens. If you want the object to be owned by dbo, you need to specify it, it doesn't just happen automatically (AUTHORIZATION clause of the CREATE command, or the ALTER AUTHORIZATION command for existing object).

    Tibor Karaszi, SQL Server MVP | web | blog

    • Proposed as answer by Shulei Chen Thursday, August 30, 2012 8:55 AM
    • Marked as answer by dnaman Tuesday, September 4, 2012 4:00 PM
    Tuesday, August 28, 2012 7:08 PM
  • Because an object need to be owned by "somebody", and having a (windows) group as an an owner does jive (apparently, as seen by how SQL Server works). So, when a user whi in windows is a group creates an object, exactly this happens. If you want the object to be owned by dbo, you need to specify it, it doesn't just happen automatically (AUTHORIZATION clause of the CREATE command, or the ALTER AUTHORIZATION command for existing object).

    Tibor Karaszi, SQL Server MVP | web | blog

    I think a little clarification is needed here.  An object needs to be created in a schema - not an owner.  When you add a windows group you cannot defined the default schema for the group.  So, when an object is created it is going to be created in the default schema for the user logged in - or explicitly based on the create statement.  If the user logged in does not have a default schema (because he is a member of a windows group), SQL Server has to create a schema and the user becomes that default schema.

    In other words:

    CREATE TABLE MyNewTable (column1 integer not null);

    In the above statement, since the schema was not stated - SQL Server will use the users default schema.  Since the user does not have a default schema, it creates one based on his user name - then creates the object.

    CREATE TABLE dbo.MyNewTable (column1 int not null);

    In the above statement, since we specified the schema - the object will be created in that schema regardless of the users default schema.

    The lesson here is: always use the schema when referencing objects in SQL Server 2005 or above.  Not only will it eliminate these kinds of issues, it has a performance benefit because non-schema qualified objects will create multiple execution plans for each user that has a different default schema.


    Jeff Williams

    • Proposed as answer by Shulei Chen Thursday, August 30, 2012 8:55 AM
    • Marked as answer by dnaman Tuesday, September 4, 2012 4:00 PM
    Thursday, August 30, 2012 2:24 AM

All replies

  • Because an object need to be owned by "somebody", and having a (windows) group as an an owner does jive (apparently, as seen by how SQL Server works). So, when a user whi in windows is a group creates an object, exactly this happens. If you want the object to be owned by dbo, you need to specify it, it doesn't just happen automatically (AUTHORIZATION clause of the CREATE command, or the ALTER AUTHORIZATION command for existing object).

    Tibor Karaszi, SQL Server MVP | web | blog

    • Proposed as answer by Shulei Chen Thursday, August 30, 2012 8:55 AM
    • Marked as answer by dnaman Tuesday, September 4, 2012 4:00 PM
    Tuesday, August 28, 2012 7:08 PM
  • Because an object need to be owned by "somebody", and having a (windows) group as an an owner does jive (apparently, as seen by how SQL Server works). So, when a user whi in windows is a group creates an object, exactly this happens. If you want the object to be owned by dbo, you need to specify it, it doesn't just happen automatically (AUTHORIZATION clause of the CREATE command, or the ALTER AUTHORIZATION command for existing object).

    Tibor Karaszi, SQL Server MVP | web | blog

    I think a little clarification is needed here.  An object needs to be created in a schema - not an owner.  When you add a windows group you cannot defined the default schema for the group.  So, when an object is created it is going to be created in the default schema for the user logged in - or explicitly based on the create statement.  If the user logged in does not have a default schema (because he is a member of a windows group), SQL Server has to create a schema and the user becomes that default schema.

    In other words:

    CREATE TABLE MyNewTable (column1 integer not null);

    In the above statement, since the schema was not stated - SQL Server will use the users default schema.  Since the user does not have a default schema, it creates one based on his user name - then creates the object.

    CREATE TABLE dbo.MyNewTable (column1 int not null);

    In the above statement, since we specified the schema - the object will be created in that schema regardless of the users default schema.

    The lesson here is: always use the schema when referencing objects in SQL Server 2005 or above.  Not only will it eliminate these kinds of issues, it has a performance benefit because non-schema qualified objects will create multiple execution plans for each user that has a different default schema.


    Jeff Williams

    • Proposed as answer by Shulei Chen Thursday, August 30, 2012 8:55 AM
    • Marked as answer by dnaman Tuesday, September 4, 2012 4:00 PM
    Thursday, August 30, 2012 2:24 AM
  • Jeff,

    This is just as much about the owner as it is about the schema. Yes, an object is in a schema. But an object is also owned by a user (user-schema separation in 2005).

    If the AUTHORIZATION clause isn't used in the CREATE command, then the user creating the object will be the owner, and if the user is in the database through a windows group, then that user will be created, as a user.

    The same principals applies to the schema.

    I just tested this, just to be 100% certain. Both a schema and a user was created.


    Tibor Karaszi, SQL Server MVP | web | blog

    Thursday, August 30, 2012 5:53 AM
  • Jeff,

    This is just as much about the owner as it is about the schema. Yes, an object is in a schema. But an object is also owned by a user (user-schema separation in 2005).

    If the AUTHORIZATION clause isn't used in the CREATE command, then the user creating the object will be the owner, and if the user is in the database through a windows group, then that user will be created, as a user.

    The same principals applies to the schema.

    I just tested this, just to be 100% certain. Both a schema and a user was created.


    Tibor Karaszi, SQL Server MVP | web | blog

    Objects are not owned by principals - the schema is owned by a principal and the schema owns the objects.  You are correct in the fact that the user will be created to own the schema though.

    The point is still the same - if you don't schema qualify the objects when they are created they will be created in the users default schema.  Since that doesn't exist because the user is logged in from a group - where you cannot define a default schema, SQL Server has to create the schema.  And, a schema must be owned by a principal so the principal is created.

    The way to avoid all of this is to always schema qualify all objects in your code.


    Jeff Williams

    Saturday, September 1, 2012 1:16 PM
  • Objects are not owned by principals - the schema is owned by a principal and the schema owns the objects. 

    Although usually the case in practice, this statement not technically correct.  Objects may be owned by principals.  If an explict object owner is not specified, object ownership defaults to the schema owner.

    CREATE USER SomeUser WITHOUT LOGIN;
    GO
    CREATE TABLE dbo.Foo(Bar int);
    GO
    EXECUTE AS USER = 'SomeUser';
    GO
    --this fails because SomeUser no object permissions
    SELECT Bar FROM Foo;
    GO
    REVERT;
    GO
    ALTER AUTHORIZATION ON OBJECT::dbo.Foo TO SomeUser;
    GO
    EXECUTE AS USER = 'SomeUser';
    GO
    --this succeeds because owner SomeUser has CONTROL permission on the object
    SELECT Bar FROM dbo.Foo;
    GO
    REVERT;
    GO


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

    Saturday, September 1, 2012 1:36 PM
  • Dan, it seems in your example the user is created with a default schema of dbo.  If you explicitly create the user with a default schema other than dbo - the example fails.

    Yes, you can explicitly grant ownership to a database principal - any database principal, but the default behavior is that objects are owned by a schema.

    The behavior the OP is seeing is due to the fact that a schema needs to exist so the object can be created in the default schema of the principal creating the object.  Unless explicitly stated in the create statement...

    Change your example to use a user database - create the user from a login without specifying a default schema and you will see that a default schema is created for that user and any objects created by that user where the schema is not specified will be created in that default schema.

    The point is still the same...you need to schema qualify objects and explicitly state what you want to happen.  Relying on the default behavior will cause unexpected issues.

    Your example is an explicit example of specifying object creation and permissions - which is not default behavior...


    Jeff Williams

    Saturday, September 1, 2012 2:05 PM
  • Jeff, I was just illustrating my point about that objects can be owned by principals, not the main topic of this thread.  My script creates an object in the dbo schema just to show that the object owner (SomeUser) can be different than the schema owner.  The name of the schema is not relevant. 


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

    Saturday, September 1, 2012 4:14 PM
  • Any idea why his account is getting added explicitly to the database when he should already be coming in / authorized as part of a domain group? Using SQL 2005..

    I suspect the appliction code is adding the current Windows user to the database using the legacy sp_grantdbaccess procedure.  That procedure adds the Windows account to the current database, creates a schema of the same name, and makes that schema the user's default one.  Any objects that are not schema-qualified will get created in the user's default schema.

    The proper way to create users in SQL 2005 onwards is CREATE USER.  This will not create a schema for the user.  Furthermore, the best-practices is to schema-qualify objects.


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

    Saturday, September 1, 2012 4:20 PM
  • Thank you everyone for the responses, i think i understand now, based on this statment:

    "When you add a windows group you cannot defined the default schema for the group.  So, when an object is created it is going to be created in the default schema for the user logged in - or explicitly based on the create statement.  If the user logged in does not have a default schema (because he is a member of a windows group), SQL Server has to create a schema and the user becomes that default schema."

    Tuesday, September 4, 2012 4:02 PM