stored procedure to delete user and login

  • SZ

    SSC Veteran

    Points: 273

    I was trying these but it does not work:

    USE [MyDatabase]

    GO

    /****** Object: StoredProcedure [dbo].[sp_deletelogin] Script Date: 4.7.2020 3:29:23 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER proc [dbo].[sp_deletelogin] @szLogin varchar(50),@defaultdb varchar(50)

    as

    if not exists ( select 0 from sys.sql_logins where name = @szlogin)

    begin

    EXECUTE AS LOGIN='sa';

    exec ('delete login [' + @szLogin + '],default_database='+@defaultdb );

    exec ('delete user [' + @szLogin + '] for LOGIN [' + @szLogin + ']');

    exec ('alter role HVP drop member [' + @szLogin + ']');

    end

    any idea why ?   🙂

  • Jeff Moden

    SSC Guru

    Points: 996843

    It would be real helpful if you were to describe what you mean by "it does not work".  What isn't it doing and what, if any, are the error messages?

    Also, just at a cursory glance, I think your IF NOT EXISTS needs to be an IF EXISTS and your DELETEs need to be DROPS.

    I'll also state the a USER doesn't need to exist just because there's a LOGIN and vice versa so you're going to need more than one IF EXISTS.

    I suggest you spend some quality time the Microsoft Documention for SQL Server before you write code to try to manage something as important as privs.

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • Jonathan AC Roberts

    SSCoach

    Points: 17321

    Why the not?:

    if not exists ( select 0 from sys.sql_logins where name = @szlogin)

    Also I think you might need:

    EXECUTE AS LOGIN='sa';

    before the line: if not exists ( select 0 from sys.sql_logins where name = @szlogin)

    As to view other users in sys.sql_logins you need permission ALTER ANY LOGIN, or a permission on the login.

  • Mr. Brian Gale

    SSC-Insane

    Points: 23075

    Just to add a small little bit to what everyone else said, the user may map to the login with a different name.  For example, the user may be named "user1" whereas the login is "login1".

    On top of that, the login may map to users in multiple databases.  So unless you ONLY have 1 user database, dropping the login may result in orphaned users on other databases in that instance.

    If you REALLY need a stored procedure for dropping a login, I would recommend using a loop and dynamic SQL query to go through each database to drop the USER on each database prior to dropping the login as it is easy to map those  2 together WHILE the login still exists.  Once the login is gone, you may end up with orphan users in other databases that will be tricky to fix as it is hard to determine which are orphaned and which are intentionally users without logins.

  • Jeff Moden

    SSC Guru

    Points: 996843

    I'd also recommend scripting out the login and all related users in all databases so that when you actually do (and you will sometime) crush a user in such a fashion, you can easily and quickly restore the critical access you just dropped.

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

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

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