Remove SQL Logins from All Databases and SQL Server

Question

Post reply

Remove SQL Logins from All Databases and SQL Server

Rudy Panigas

SSChampion

Points: 10702
More actions
August 10, 2008 at 8:50 pm

#189184

Comments posted to this topic are about the item Remove SQL Logins from All Databases and SQL Server
Rudy

Viewing 9 posts - 1 through 8 (of 8 total)

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

Rudy Panigas SSChampion Points: 10702 More actions · Answer 1

Hello. Please let me know what you think about this code, changes, etc.

Thanks,

Rudy

ryan.hobbs SSC Enthusiast Points: 130 More actions · Answer 2

Rudy,

First thanks for posting the sp. The code it generates uses sp_dropuser and that does not work for usernames that contain periods. One of the applications we use creates usernames in the following format: vdb_First.Last

EXEC sp_droplogin vdb_First.Last

results in:

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near '.'.

Looks like you need to enclose your output like you did with the sp_dropuser:

PRINT 'EXEC sp_droplogin ['+ @SpecificName +']'

Rudy Panigas SSChampion Points: 10702 More actions · Answer 3

Hello Ryan,

Thanks for the update. Guess I didn't think of this when I wrote the script as I have not seen anyone using the "." in the login name.

Thanks,

Rudy

ryan.hobbs (4/29/2011)
Rudy,
First thanks for posting the sp. The code it generates uses sp_dropuser and that does not work for usernames that contain periods. One of the applications we use creates usernames in the following format: vdb_First.Last
EXEC sp_droplogin vdb_First.Last
results in:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '.'.
Looks like you need to enclose your output with single quotes:
PRINT 'EXEC sp_dropuser ''['+ @DatabaseUser +']'''
and
PRINT 'EXEC sp_droplogin '''+ @SpecificName +''''

Rudy

ryan.hobbs SSC Enthusiast Points: 130 More actions · Answer 4

No worries.

FYI: I just edited my original post as the sp_dropuser is okay. It is just the sp_droplogin that needs to be enclosed. Looks like [ ] works as good as ' '.

Tim Lehner SSC Enthusiast Points: 189 More actions · Answer 5

Nice script. I had to add SID matching for my purposes. It's possible that the name in sysusers is different from the login that it points to in syslogins.

@SQLFRNDZ SSCrazy Eights Points: 9095 More actions · Answer 6

This script generates the script to drop users only for the users who don't own a schema.

It would have been better if it can generate like that too. I'll try to edit this script.. and post here

[font="Tahoma"]
--SQLFRNDZ[/url]
[/font]

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 7

Thanks for sharing your knowledge. I haven't tried it but it looks like the code won't actually work if the user owns something like a schema, etc.

--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)

gleb.zalt SSC Enthusiast Points: 143 More actions · Answer 8

DROP LOGIN [domainName\userName] is good for both SQL and Windows users, and deletes also related users, for me this simple syntax more usable than your SP.