Post reply

auto script for auto-run to change sql login from British English to English language setting

  • May 17, 2016 at 5:29 am

    #400171

    Hello,

    We have an application which depends heavily on SQL login's security Language setting (date related) and wouldn't display the contents if the dates are wrongly set on the Login Security. I change them manually for new starters from British English to English (US) and then it works fine.

    I need to write an overnight script, which can identify new Starters and change them from British English to English (US).

    Will this System SP do the job? "sp_defaultlanguage"

    How can I query the Sys table first for British English language users before changing them?

    Thank you,

    B.K.

  • May 17, 2016 at 5:45 am

    #1879444

    interesting issue datsun; here's how i would do it.

    the detection for me would simply be to query sys.server_principals:

    select 'EXEC sp_defaultlanguage @loginame=''' + name + ''',@language=''us_english''',*

    from sys.server_principals

    where type_desc in('SQL_LOGIN','WINDOWS_LOGIN','WINDOWS_GROUP')

    AND default_language_name <> 'us_english'

    i would use FOR XML to generate a suite of statements like this as a final resource:

    SELECT @command = s.Colzs

    FROM(SELECT

    Colzs = STUFF((SELECT ';' + 'EXEC sp_defaultlanguage @loginame='''

    + name

    + ''',@language=''us_english'''

    FROM sys.server_principals

    WHERE type_desc in('SQL_LOGIN','WINDOWS_LOGIN','WINDOWS_GROUP')

    AND default_language_name <> 'us_english'

    FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'')

    ) s

    PRINT @command

    if ISNULL(@command,'') <> ''

    EXEC(@command)

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • May 17, 2016 at 6:08 am

    #1879452

    Lowell,

    Perfect. This will do. I will restrict it to Windows Login only and since there is a create_date, that's quite useful for new starters from this month only.

    five ***** stars badge for you!

    B.K.

Viewing 3 posts - 1 through 2 (of 2 total)

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