dcsimg
Results 1 to 7 of 7

Thread: Users with Passwords?

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Oct 2005
    Posts
    382

    Users with Passwords?

    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.

    Name:  pic.jpg
Views: 39
Size:  20.7 KB

    Name:  user.jpg
Views: 37
Size:  18.1 KB
    Attached Images Attached Images  

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    104,719

    Re: Users with Passwords?

    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.

  3. #3
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,796

    Re: Users with Passwords?

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  4. #4

    Thread Starter
    Hyperactive Member
    Join Date
    Oct 2005
    Posts
    382

    Re: Users with Passwords?

    Quote Originally Posted by techgnome View Post
    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.

    Name:  login.jpg
Views: 23
Size:  29.1 KB
    Last edited by Darkbob; May 10th, 2020 at 12:33 PM.

  5. #5
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,796

    Re: Users with Passwords?

    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 don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  6. #6

    Thread Starter
    Hyperactive Member
    Join Date
    Oct 2005
    Posts
    382

    Re: Users with Passwords?

    Quote Originally Posted by techgnome View Post
    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?

  7. #7
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,796

    Re: Users with Passwords?

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width