sql server security and schema questions? RRS feed

  • Question

  • sql server security is indepent of  schema, and how is this implemented, I hope that one expert can give me a good sample for this.

    for example, if  one user john leave the company , and need to delete this user, will this have impact on the tables or object he created?

    Thanks in advance.
    Wednesday, May 20, 2009 4:46 PM


  • Which version of SQL Server you have. Objects are owned by USER in SQL Server 2000 , where as in 2005 and 2008 Objects are owned by Schema.
    Read about schema - user separation in SQL Server 2005 here http://msdn.microsoft.com/en-us/library/ms190387.aspx . In SQL Server 2000 the user was tightly coupled with the object. If you want to delete the user you have to transfer (sp_changeobjectowner.) the objects to some other user. In 2005 , schemas are nothing but containers which contains objects. 2005, if user has to be droped you needs to transfer the schem owned by this user to another user and you can drop the user. Here you are transfering schema ownership not objects


    MCITP, MCTS, MCDBA,MCP-- Blog : http://experiencing-sql-server-2008.blogspot.com/
    • Marked as answer by Jake Wardley Thursday, May 21, 2009 1:20 AM
    Wednesday, May 20, 2009 4:53 PM