Post reply

order by predefined list

  • September 21, 2017 at 3:35 am

    #349209

    Hi 

    I would like to issue the following command to bring back alter login statements:

    SELECT 'ALTER LOGIN ' + name + ' DISABLE;' FROM sys.server_principals where name in ('ted,'alice','simon')
    order by name;

    but bring them back in the order there are in the IN clause...so

    alter login ted disable;
    alter login alice disable;
    alter login simon disable;

    Is this possible? in reality there is a long list. 

    Thanks in advance

  • September 21, 2017 at 3:53 am

    #1960327

    Why is it important to alter the logins in a particular order?  If you really need to, you could try something like this:

    SELECT 'ALTER LOGIN ' + p.name + ' DISABLE;'
    FROM sys.server_principals p
    JOIN (VALUES
         ('ted', 1)
    ,    ('alice', 2)
    ,    ('simon',3)
        ) v(name, seq)
    ON p.name = v.name
    ORDER BY v.seq;

    John

  • September 21, 2017 at 4:15 am

    #1960329

    Great thanks John M, the only issue with that is I have an excel list with 100's of users and I dont want to have to put in a number after each one.  Its not easy format it.

  • September 21, 2017 at 4:23 am

    #1960330

    It's dead easy if you've got it in Excel.  Add a new column with a sequence number in, then a second new column with a formula something like ="('"&A1&"', "&A2&"),".  I'm still puzzled about why you need to do this, though?

    John

  • September 21, 2017 at 6:10 am

    #1960344

    Alternatively, if you've got your list in Excel, it's presumably sorted in the order you want?

    In which case, why not just build the ALTER LOGIN statements in a new column in Excel, and then copy & paste the lot into SSMS?

    Thomas Rushton
    blog: https://thelonedba.wordpress.com

  • September 21, 2017 at 7:18 am

    #1960358

    ThomasRushton - Thursday, September 21, 2017 6:10 AM

    Alternatively, if you've got your list in Excel, it's presumably sorted in the order you want?

    In which case, why not just build the ALTER LOGIN statements in a new column in Excel, and then copy & paste the lot into SSMS?

    This would probably be easier. The formula would be:
    =("ALTER LOGIN [" & $A1 & "] DISABLE;")
    Assuming your first user is in cell A1 and the rest are below. Simply put the formula in Cell B1 and "drag" the formula down,. Then copy and paste the resulting forumla's into SSMS and execute. 🙂

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • September 21, 2017 at 9:28 am

    #1960387

    Still curious about the requirement for a specific order.

  • September 21, 2017 at 1:19 pm

    #1960408

    thanks everyone....

    the reason I want to do it this way is the original list comes from excel ....so I want to add back to the excel sheet the sql code used for reference...esp if its a password change I run instead of a disable.

    I guess I will have to do it all in excel from the start...

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

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