locked
SQL Server 2000 Delete user RRS feed

  • Question

  • Hi
    I am using the Sql Server 2000(Production). I have requirement like i have to delete a some of users. if i delete user, is there any effect on the DTS packages, stored procedures,and Views created by him,? If any effect, what it would be? Can other user open the packages and stored Procedures and modify them even after deleting the user ?

    Thanks in advance...

    Pratap
    Wednesday, August 31, 2011 4:16 AM

Answers

  • Hi GPreddy,


    In SQL Server 2000, you can’t delete the user who owns object. You should change the object owner before you delete it. After you delete the user, there will be no effect on the items which created by previous owner. You can use sp_changeobjectowner to change the object owner. Here is the usage: http://msdn.microsoft.com/en-us/library/aa259618(SQL.80).aspx
    Hope this helps!

     


    Best Regards,

    Iric

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Edited by Iric Wen Friday, September 2, 2011 6:28 AM
    • Marked as answer by Stephanie Lv Wednesday, September 14, 2011 1:39 AM
    Friday, September 2, 2011 6:26 AM

All replies

  • Do you see objects like User1.tblname? You won't be able to delete user if she/she owns objects
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, August 31, 2011 6:54 AM
  • Hallo GPreddy,

    as Uri has pointed out it wil only affect objects of the users itself.

    e.g.

    User a creates a proc ...

    CREATE PROC user.myproc
    AS
      ...
    GO
    

    - the user can not be dropped because he is owner of objects in this database

    Let's say the user has created an object which owns the database owner dbo

    CREATE PROC dbo.myproc
    AS
    ...
    GO
    

    Now you can drop the user and the login

    Please note that users will not automatically be dropped when you drop a login!

     


    Uwe Ricken

    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITS Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de
    Wednesday, August 31, 2011 7:47 AM
  • Hi GPreddy,


    In SQL Server 2000, you can’t delete the user who owns object. You should change the object owner before you delete it. After you delete the user, there will be no effect on the items which created by previous owner. You can use sp_changeobjectowner to change the object owner. Here is the usage: http://msdn.microsoft.com/en-us/library/aa259618(SQL.80).aspx
    Hope this helps!

     


    Best Regards,

    Iric

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Edited by Iric Wen Friday, September 2, 2011 6:28 AM
    • Marked as answer by Stephanie Lv Wednesday, September 14, 2011 1:39 AM
    Friday, September 2, 2011 6:26 AM