Post reply

questions about CREATE LOGIN

  • June 20, 2013 at 9:49 am

    #395009

    I know I can create a login as follows:

    CREATE LOGIN test_login

    WITH PASSWORD = 'some_password',

    DEFAULT_DATABASE = AdventureWorksDW,-- or whatever database

    DEFAULT_LANGUAGE = us_english,

    CHECK_POLICY = OFF;

    I also know that, if I'm in SQL Server Management Studio and I enable Query | SQLCMD Mode, instead of

    CREATE LOGIN test_login

    I can use

    :setvar LoginName "test_login"

    CREATE LOGIN $(LoginName)

    in the above SQL code.

    • What is the advantage of using the latter method?

    • Is there a way I can use the latter method from outside of SQL Server Management Studio (like when I'm using SQL commands in some app)?

    • In SQL Server Management Studio, I don't see any indication when SQLCMD Mode is enabled or it's not, so if I forget whether I’ve enabled it, I just have to play with it to find out for myself. Am I missing something? How can I tell?

    • I know that in the above example, AdventureWorksDW can be enclosed in square brackets as follows:

    DEFAULT_DATABASE = [AdventureWorksDW],

    and the command will still work. But what effect, if any, does this have on the CREATE LOGIN command?

  • June 21, 2013 at 3:48 am

    #1626254

    in the above SQL code.

    • What is the advantage of using the latter method?

    This allows the script to be called from the cmd line tool sqlcmd.exe with variables passed in e.g.

    sqlcmd -S localhost -d Master -q "SELECT '$(var1)' as [$(var2)]" -v var1="Mooooo" var2="Cows Say"

    and also allows you to use a script against multiple instances using the :connect option

    :setvar word "now"

    :SETVAR server1 "SERVERNAME01"

    :SETVAR server2 "SERVERNAME02"

    :connect $(server1)

    SELECT @@servername,'$(Word)' as [Word]

    GO

    :connect $(server2)

    SELECT @@servername,'$(Word)' as [Word]

    GO

    • Is there a way I can use the latter method from outside of SQL Server Management Studio (like when I'm using SQL commands in some app)?

    yes using the sqlcmd line as above

    • In SQL Server Management Studio, I don't see any indication when SQLCMD Mode is enabled or it's not, so if I forget whether I’ve enabled it, I just have to play with it to find out for myself. Am I missing something? How can I tell?

    the :setvar will go grey when enabled

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

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