Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Anybody know about logins?

Author  Topic 

Charles Egan
Starting Member

21 Posts

Posted - 2013-06-20 : 11:50:24
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?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-20 : 14:53:30
1. SQLCMD is usually used for running queries from outside of SSMS. The SQLCMD mode in SSMS allows you to test such queries from within the IDE. I don't know of any other advantage to using SQLCMD mode.

2. You can run SQLCMD from a command window. There are a few examples here: http://msdn.microsoft.com/en-us/library/ms180944.aspx

3. You are right, I haven't seen any indicator or alert that tells you which mode you are in.

4. The square brackets are escape characters. So if you had some funny database name, for example something that started with a number, or something with a special character in it etc. use of escape characters is mandatory. Otherwise it is optional. But, it does not have any effect on the create login command.
Go to Top of Page

Charles Egan
Starting Member

21 Posts

Posted - 2013-06-20 : 17:45:49
Hi, James K -

Thanks for the informative answer.

However, from the link you provided it appears that there is perhaps no way to use SQLCMD Mode in a call to SQL made from within an app that I might write myself (should I ever wish to do so).

Is that your read on the situation as well?
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-20 : 17:51:40
The only way to call SQLCMD from your application (such as a C#/.Net app) is to invoke system command - i.e., you will be forking a process. However, if controlling/querying SQL from your application is your objective, then there are better ways - everything from SQL SMO to plain ADO.Net or Entity Framework. In that sense, SQLCMD is designed more as a commandline tool.
Go to Top of Page
   

- Advertisement -