CODEWITHSUNDEEP
sql-server
markaaronky
markaaronky

Reputation: 1305

Why won't SQL Server allow creation of a user with SQL Server Authentication?

I've get a fresh install of SQL Server 2012 and do not want to use integrated security. Instead I'd like to use SQL Server authentication, where I create logins in SQL server and assign passwords. However, this option doesn't seem to be available (see screen snapshot below). Every option available to me under "User Type" (other than those that aren't related to accounts at all) want me to map to existing Windows Domain accounts.

In the old days, I'd specify a desired login name and be prompted for a password to go along with it, and I'd be done. Now there's no option to specify a password--I'm forced to map my new login to an existing windows domain account. Not what I want. What am I missing here? Thanks in advance.

** edit ** New screen snapshots added after RB's comment. I did indeed have an option disabled that would allow BOTH Windows and SQL Server authentication modes, but it's turned on now. After doing so and restarting SQL Server, it is NOT making a difference: enter image description here

Here is what I am presented with when I try to create a new user. None of these options simply allow me to create a login and password in the context of SQL server (well, one allows creation of a login with no password at all... useless!) All others are linked to existing Windows logins. Ideas?

enter image description here

Upvotes: 0

Views: 1342

Answers (2)

binki
binki

Reputation: 8308

You are trying to create database users. You need to create a server login first. This is where the connection credentials (e.g., password) are specified.

In SSMS’s Object Explorer, you have to make sure you are at «server name» / Security / Logins. You may want to collapse the Databases folder to avoid confusion. From there, you can right-click on the Logins folder (note this is Logins, not Users—if you see Users, you are in the Database folder—please collapse that and find the right Security / Logins folder instead) and select “New Login…”.

Upvotes: 0

Ben Thul
Ben Thul

Reputation: 32707

Let's do this in a way that doesn't require pictures and right-clicking.

  1. CREATE LOGIN [yourUserName] WITH PASSWORD = 'someStrongPassword';
  2. CREATE USER [yourUserName];

Upvotes: 3

Related Questions