Add a User in SQL Server

This article is intended for those who host their own database and use SQL authentication. 

  1. Open SQL Management Studio.
  2. Click the plus (+) sign next to Security to expand the Security folder.
  3. Click the plus (+) sign next to Logins to expand the Logins folder.

createsqluser1.png

  1. Right-click on Logins and select New Login. The Login - New screen opens.
  2. Enter the login name (i.e. first initial, last name = JSMITH) into the Login name field.
  3. Select the SQL Server authentication radio button.
  4. Enter the password that matches your SQL password policy into the Password and Confirm password fields. If you don't have a password policy, uncheck the Enforce password policy check box.

createsqluser2.png

  1. Click User Mapping on the left side of the screen, and locate the name of your Enterprise database within the list.
  2. Check the Map check box.
  3. Verify that Public and EBMSUser are checked in the Database Role Membership for the database.  

createsqluser3.png

  1. Click OK.
  2. Return to Enterprise Software and create a new user to match the SQL login that you created.  

Related articles

Comments

5 comments

Date Votes
  • Theresa Gorman

    We have migrated to a new server.  I am unable to create usiadmin to the security logins to map to restored database as it says user already exists

    0 upvotes

  • Sean Tame

    HI Theresa,

    You need to delete the user from within the DB Security itself, then add it under the Security folder at the Server level, then enable it on the DB.

    Shout if you need help.

    1 upvotes

  • Sara Noonan

    Hi Theresa, just poking my head in to second Sean's answer.  Thanks!

    0 upvotes

  • Theresa Gorman

    Thanks guys, I had done that but it would still not map.  I found a script to do it

    ALTER USER <user_name> WITH Login = <login_name>;

    0 upvotes

  • Lee Tickett (Tickett Enterprises Limited)
    • Edited

    The process I have always used is to create the login (at server level) without mapping to the database.

    Then run this to fix/attach to the db user;

    sp_change_users_login 'AutoFix', 'usiadmin'

    0 upvotes

Please sign in to leave a comment.