Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Remove SQL Logins from All Databases and SQL Server Expand / Collapse
Author
Message
Posted Sunday, August 10, 2008 8:50 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 8:18 AM
Points: 318, Visits: 1,128
Comments posted to this topic are about the item Remove SQL Logins from All Databases and SQL Server


Post #549965
Posted Tuesday, August 12, 2008 9:29 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 8:18 AM
Points: 318, Visits: 1,128
Hello. Please let me know what you think about this code, changes, etc.

Thanks,

Rudy



Post #551120
Posted Friday, April 29, 2011 12:50 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 29, 2011 1:13 PM
Points: 2, Visits: 1
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 +']' 

Post #1101005
Posted Friday, April 29, 2011 1:04 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 8:18 AM
Points: 318, Visits: 1,128
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 +'''' 




Post #1101019
Posted Friday, April 29, 2011 1:26 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 29, 2011 1:13 PM
Points: 2, Visits: 1
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 ' '.
Post #1101029
Posted Friday, July 29, 2011 2:51 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 7, 2014 1:29 PM
Points: 15, Visits: 211
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.
Post #1151323
Posted Thursday, August 11, 2011 2:58 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 9:50 AM
Points: 477, Visits: 1,053
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



--SQLFRNDZ
Post #1158823
Posted Tuesday, February 21, 2012 7:40 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:27 AM
Points: 35,769, Visits: 32,437
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1255676
Posted Sunday, June 3, 2012 10:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 4:20 AM
Points: 3, Visits: 150
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.
Post #1310236
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse