locked
Ensure that the user is the owner of their default schema - what does that mean? RRS feed

  • Question

  • We are setting up IBM Cognos BI software. The SQL permissions for the login say the following. I am stumbling over the third point: Ensure that the user is the owner of their default schema. We only use the default schema dbo. How do we make the user the dbo owner of their default schema, or does it not apply since all objects use the default dbo schema?

    SQL Server
     - Give Create and Drop table privileges for the database to the User account.
     - Ensure that the user account is a member of the db_ddladmin, db_datareader, and db_datawriter roles.
     - Ensure that the user is the owner of their default schema.

    Wednesday, May 10, 2017 1:35 PM

Answers

  • If all your objects are in the dbo schema you are ok, as long as the user is in the db_owner role, or have been granted alter permissions to every object with that schema, or is a member of a role which has been granted to every objects with that schema.

    It is possible that there is a user who is not an owner of that role and has been granted alter permission on individual objects with that schema. When you run your script to modify objects you might encounter failure.

    To ensure that the user can modify all objects with that schema you will need to add them as owners to that schema or add them to the db_owner role.

    In your case the simplest thing to do as all of your objects are in the dbo schema, all you do is add your user to the db_owner role.

    • Marked as answer by TheBrenda Friday, May 12, 2017 5:30 PM
    Wednesday, May 10, 2017 1:55 PM

All replies

  • If all your objects are in the dbo schema you are ok, as long as the user is in the db_owner role, or have been granted alter permissions to every object with that schema, or is a member of a role which has been granted to every objects with that schema.

    It is possible that there is a user who is not an owner of that role and has been granted alter permission on individual objects with that schema. When you run your script to modify objects you might encounter failure.

    To ensure that the user can modify all objects with that schema you will need to add them as owners to that schema or add them to the db_owner role.

    In your case the simplest thing to do as all of your objects are in the dbo schema, all you do is add your user to the db_owner role.

    • Marked as answer by TheBrenda Friday, May 12, 2017 5:30 PM
    Wednesday, May 10, 2017 1:55 PM
  • Since we only use the default dbo schema, I am not very literate with schemas. BUt I guess what threw me is "Ensure that the user is THE owner of their default schema." I would think more of it as "Ensure that the user is AN owner of their default schema." So there is more than one owner of a schema? Everybody who is db_owner is an owner of their default schema - or am I mixing oranges and apples?
    Wednesday, May 10, 2017 3:27 PM
  • This is probably bad advice. The requirement seems to be that the user can create and drop tables. It is sufficient to have the ALTER permission on the users default schema. GRANT ALTER ON SCHEMA::dbo TO <user>; should do it. However, that would allow that user to mess around anything in the dbo schema.

    But the db_ddladmin role already has the ALTER ANY SCHEMA permission, so I can't see what benefit there is to being the owner of the default schema.

    Consider setting up a separate schema or group of schema's for those users who are not really database owners. Set their default schema to such a new schema and grant the users ALTER on that schema instead. But I don't know anything about Cognos BI so I can't guarantee this would work.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Wednesday, May 10, 2017 3:48 PM
  • The simplest way to achieve the part that every user owns their default schema is to create the users with the old sp_adduser. It will create a schema with the same name as the user and make the user owner of that schema. Way back in SQL 2000 owners and schema was the same thing.

    I don't know about Cognos BI, but it sounds as if the software permits the users to creates their own tables. In that case, it is probably a good idea that all users have their own schema, so that they don't stomp on each other in dbo.
    Rick raises a point about db_ddladmin. It seems a little pointless that they have their schema, if they also have rights to alter any schema. Maybe CREATE TABLE permission would do.

    Wednesday, May 10, 2017 9:39 PM