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 9 (of 9 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: 1004619 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.

Remove SQL Logins from All Databases and SQL Server

Cookies on SQLServerCentral