Post reply

Need to drop a user without shooting myself in the foot

  • December 18, 2007 at 5:21 pm

    #201868

    Hi folks - I have a lot of database experience but am new to SQL Server. I need to drop a user login from our database, since that person has left our company. I noticed that the user account in question owns several scheduled jobs and DTS packages. I can, of course, change the owner of those things, but how do I find out whether the user owns some other things which might break the system if I drop the user? Thanks in advance for any advice.

  • December 20, 2007 at 3:15 pm

    #761929

    You may try the following script:

    SELECT su.uid, su.name, so.name FROM sysusers su, sysobjects so WHERE su.uid = so.uid AND su.uid <> 1 AND su.name = 'userName'

  • December 28, 2007 at 3:11 pm

    #763448

    Additionally I would recommend disabling the user for a period of time, say a month, before deleting. Giving you time to object change ownership to the proper account.

    In a pinch you would be have the option of re-enabling the user for that mission critical report or what-have-you.

    Best of Luck.

    "Key"
    MCITP: DBA, MCSE, MCTS: SQL 2005, OCP

  • December 28, 2007 at 4:12 pm

    #763461

    Damon is spot on... don't drop the user immediately... just disable it.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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