How to login using sqlcmd

  • Hi,

    my rdbms version :2012 sqlexpress

    I created the login using the below commands:

    SETUSER

    GO

    USE master

    GO

    IF EXISTS( SELECT * FROM sysdatabases WHERE name='permissionsDB' )

    DROP DATABASE permissionsDB

    GO

    IF EXISTS( SELECT * FROM sys.sql_logins WHERE name='schemaUser' )

    DROP LOGIN schemaUser

    GO

    CREATE DATABASE permissionsDB

    GO

    CREATE LOGIN schemaUser WITH PASSWORD='Pass#123',DEFAULT_DATABASE=permissionsDB

    GO

    GRANT CONNECT SQL TO schemaUser

    GO

    USE permissionsDB

    GO

    CREATE USER schemaUser WITH DEFAULT_SCHEMA=dbo

    GO

    what is the correct option to login as schemaUser using sqlcmd?

    http://technet.microsoft.com/en-us/library/ms162773.aspx

    I try the following it fails

    C:\Users\user>sqlcmd -S user-pc\sqlexpress -U schemaUser

    Password: Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Login failed

    for user 'schemaUser'..

    C:\Users\user>sqlcmd -S user-pc\sqlexpress -U schemaUser

    Password: Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Login failed

    for user 'schemaUser'..

    any assistance is really appreciated!

    thanks

  • The code you have posted is working correct.

    Are you able to login with the credentials using Manamgement Studio?

    Does the SQL Error logging contain more information regarding these login failures? Please post all error messages...

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • Either you specify the password at the prompt, or you use the -P option.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • Are you able to login with the credentials using Manamgement Studio?

    Yes I'm able to login to Manamgement Studio using windows authentication as an admin

    But I'm not able to login as sa

    when i execute the following statement,

    CREATE LOGIN schemaUser WITH PASSWORD='Pass#123',DEFAULT_DATABASE=permissionsDB

    Is it creating a sql server account or windows account?

    error is as follow:

    TITLE: Connect to Server

    ------------------------------

    Cannot connect to USER-PC\SQLEXPRESS.

    ------------------------------

    ADDITIONAL INFORMATION:

    Login failed for user 'schemaUser'. (Microsoft SQL Server, Error: 18456)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476

    ------------------------------

    BUTTONS:

    OK

    ------------------------------

    Thanks a lot!

  • With your CREATE LOGIN statement a SQL account is created.

    If you can login as administrator but you can't login as 'sa' then it looks like the authentication mode of the instance is set to "Windows Authentication mode". This prevents the use of SQL accounts.

    If it is needed (according to your security policy) you can change this setting using SSMS. Navigate to the 'security' tab of the properties of the instance.

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • If you want to use Windows authentication with SQLCMD you can use the -E switch. This sets the security to 'integrated mode' and uses the current Windows account. You need to omit the -U and -P switch.

    SQLCMD -S {server\instance} -d {dbname} -E

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • after changing security to sql server and windows authentication and restarting the server,

    I was able to login using either sa or schemauser.

    thanks

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

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