Post reply

schema and user

  • January 30, 2012 at 11:25 am

    #263563

    We have a user in a database, when he created a table, it adds his login before the table name, so his login is the schema of the table.

    Why is that, I suppose it should be dbo.table name.

    If I grant him db owner, then when he created table, it will be dbo.table name,

    is there a way that I don't grant him as dbowner, but the table created by him is still like dbo.tablename?

    Thanks

  • January 30, 2012 at 11:28 am

    #1440173

    This happens because in the Security--> Logins on the SERVER, the users default schema is set their own name for this database mapping. Change it to dbo.

    Jared
    CE - Microsoft

  • January 30, 2012 at 11:38 am

    #1440176

    Check this link for user schema separation.

    http://msdn.microsoft.com/en-us/library/ms190387(v=sql.90).aspx

  • January 30, 2012 at 1:50 pm

    #1440241

    Thanks all, I read the article, and changes the default schema to dbo for the login.

    My question is now when a login created what is its default schema, I guess it should be dbo.

    How come its default schema was its login name in this case?

    Someone else created this login, I want to know how this happened?

    Thanks

  • January 30, 2012 at 9:19 pm

    #1440335

    SQL Server 2000, automatically creates schema with users name if the user is not member of db_owner role.

    SQL Server 2008, by default creates objects on dbo schema.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply