Users with Passwords?

[Darkbob]

Hey people. Super stupid noobish question here. I am trying to create a new user for a SQL Server 2017 Database. I'd like to assign a password to the user. Seems simple, right? But I can't figure out how to do it. D'oh.

I'm using Microsoft SQL Management Studio v18.5. I open my database. Right click on Security. The interwebz say I should see Login but nope. I see Users. Ok... so I right click Users then click New. I can do several types of login but nothing there about a user with a password. Any ideas?

Thanks in advance for any assistance.

[jmcilhinney]

A login is at the instance level and a user is at the database level. Generally speaking, you would create a login first and then map that to a user. Use the Security node for the instance, not the database. Create a login and provide a password there. There's a User Mapping tab in the login properties and you can simply check a box for each database you want to create a user in mapped to that login. That's how I've always done it, anyway. I'm guessing that selecting SQL user with login in that dialogue you've shown would do the same thing. Microsoft always provide multiple cat-skinning methods.

[techgnome]

Yeah, security with sql server is two-layered... login, which gets you connected to the server instance, and then the user, which then contains the actual database and privileges associated with it. More often than not, both the login and the user are the same, but they don't have to be. And yes, a single login could have different users associated with it - providing they are for different databases. In this case it sounds like you want the "SQL User with Login" option... you can then specify a new login, or select an existing one (using the ... button).

What's the difference? When you create the connectionstring, use the login... the user is used for logging, or when you use sp_who to see users in the database.

-tg

[Darkbob]

In this case it sounds like you want the "SQL User with Login" option... you can then specify a new login, or select an existing one (using the ... button).

What's the difference? When you create the connectionstring, use the login... the user is used for logging, or when you use sp_who to see users in the database.

-tg

Ya... I'm not understanding any of that at all. Sorry. I must be dense. And I think I failed to provide some critical information when I posed my question. Starting over...

The database resides on a virtual Windows machine cloud hosted. I want to connect to the database using SQL Studio from my own personal PC over the internet.

I want the option to allow other people to also connect to the database through the internet using SQL Studio.

I don't want EVERYBODY able to connect to the database - users must know the database password to access the database.

How do I specify the user name and password? This used to be so easy... maybe just a change with the new Management Studio?

Part of my problem is that I can't seem to make a new Login. The menu option doesn't show up. Only New User... I'm missing something big I think.

[techgnome]

I told you... you want the "SQL User with login".... it's the first option in the drop down in your screen shot back in post #1.... I said as much....
Since db access is through the user, it doesn't really make sense to have a login w/o a user.

If you need to, create the user and login with the same name, and then grant thaat user access to all the dbs you want the to have access to.

-tg

[Darkbob]

I told you... you want the "SQL User with login".... it's the first option in the drop down in your screen shot back in post #1.... I said as much....
Since db access is through the user, it doesn't really make sense to have a login w/o a user.

If you need to, create the user and login with the same name, and then grant thaat user access to all the dbs you want the to have access to.

-tg

I can do that and I did do that. But I've still got a problem. I can't add a password - there's no spot for one. Any ideas?

[techgnome]

OH.... Gotcha... sounds like you are setup for integrated security... you want SQL security... or mixed security.
Integrated Security - that's where the security of the login & user it tied to an account, either on the local machine, or through active directory.
SQL Security - Also known as mixed because it allows integrated security AND SQL Security through being able to create logins/users which are not tied to a windows account...

It sounds like your system is setup with Integrated security.... It used to be one of those things that you had to decide at the time of installation... I think you can add mixed mode, but once it is set, you can't go back to the pure integrated ssecurity model. I've always done mixed mode right from the start, so I don't know how to activate that. Sorry. It should be possible.... hopefully someone else will be able to come along and help.

-tg