stored procedure to delete user and login

Question

stored procedure to delete user and login

SZ

Old Hand

Points: 338
More actions
July 4, 2020 at 1:34 am

#3767428

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 ? 🙂

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

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

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 1

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.

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

Jonathan AC Roberts SSCoach Points: 18356 More actions · Answer 2

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: 24800 More actions · Answer 3

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.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 4

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.

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