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

questions about CREATE LOGIN Expand / Collapse
Author
Message
Posted Thursday, June 20, 2013 9:49 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, June 20, 2013 8:24 AM
Points: 41, Visits: 3
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?
Post #1465794
Posted Friday, June 21, 2013 3:48 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 9:26 AM
Points: 1,297, Visits: 998
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

Post #1466105
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse