Please see the T-SQL below:

create database db;

use [db]

create login [standard] with password ='dkdkdkqajHGgbh56'
CREATE USER [standard] FROM LOGIN [standard];

exec sp_addrolemember 'db_owner', 'standard'

After running this I try to login to SQL Server Management Studio as standard and I am prompted with this error:

login failed for user: standard

What am I doing wrong?

PS: I cannot even login as sa. I have enabled 'SQL Server Windows Authentication mode'. I am a developer (not a DBA) trying to setup a local instance of SQL Server.

  • 3
    Can you verify your authentication mode using SELECT CASE SERVERPROPERTY('IsIntegratedSecurityOnly') WHEN 1 THEN 'Windows Authentication' WHEN 0 THEN 'Windows and SQL Server Authentication' END as [Authentication Mode] . SA is disabled by default so it's not expected to work out of the box. – Tom V Feb 25 '16 at 15:03
  • When you use the Connect to Server box to connect, what is in the Authentication box? Is it "Windows Authentication" or "SQL Server Authentication"? – MHSQLDBA Feb 25 '16 at 16:20

I had to enable: 'SQL Server and Windows Authentication Mode', then restart the SQL Server 2012 service.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.