Remove SQL Logins from All Databases and SQL Server

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

    Rudy

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

    Thanks,

    Rudy

    Rudy

  • 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 +']'

  • 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

  • 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 ' '.

  • 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.

  • 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]

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

  • 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.

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

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