What are the exact steps to set up Windows Authenticated users on database

Question

I want to know the exact steps to setting up a user for a database. When I go to LOGINS and selct new login, the login name seems to need to be specific. When I click search it is not accepting the login name I am typing in. The search button next to the field for the name entry is looking where to find the name?

Where is the name intially set up?

I am trying to use windows authentication. Is there a way to set it up so that anyone who has a login permission to the Windows server container will then be able to use the database without further setup?

Users need the usual permissions to read write append delete records.

Thank you for your assistance on this. I am using either SQL 2014 or SQL 2017 currently. I hope this process would also be the same for SQL 2019 or 2022.

Thank you

Answer 1

You need to provide DomainName\UserName in the Login name field:

If you know the partial name of the user, you can use the Search button to get the whole name and also you can verify if the name you typed in is correct.

Answer 2

the user name needs a domain. the default is local domain. on the search click location and pick the domain the user belongs to. then enter the name. you can also just include the domain with the user:

mydomain\username

if you want to add a group, change the object type to groups (or add groups) and use advanced search

Answer 3

To give a certain Windows user access to a certain database, you say:

CREATE LOGIN "Domain\User" FROM WINDOWS
USE MyDB
CREATE USER "Domain\User"

a lot easier than using the UI with all the search dialogs.

If you want to give all users on the server access to the database, I think you should be able to do that by using BUILTIN\Users instead of Domain\User. You can also use an AD Group.

Answer 4

Hi @Awicurrent ,

To set up a user for a SQL Server database using Windows authentication, you can follow these steps:

• Open SQL Server Management Studio and connect to the SQL Server instance.
• In Object Explorer, expand the Security folder, right-click on the Logins folder, and select New Login.
• In the Login - New dialog box, enter the Windows user or group account you want to add to the database. You can type the user or group name directly into the Login name field or click the Search button to search for it.
• If you cannot find the user or group you are looking for, click the Search button and select the location where you want to search for the user or group account. You can search locally or in Active Directory.
• Once you have found the user or group account, select it and click the OK button.
• In the Login - New dialog box, select the Default database for the user. This is the database that the user will connect to by default.
• Under Server Roles, you can assign the user to a server role or leave it blank if you want to assign database-level roles later.
• Under User Mapping, select the database you want the user to have access to, and then select the database roles you want to assign to the user. For example, you can assign the user to the db_datareader and db_datawriter roles to allow them to read, write, and modify data in the database.
• Click OK to save the new login and user settings.
• If you want to allow any Windows user with login permission to the server to access the database without further setup, you can create a Windows group, add the users you want to allow to the group, and then add the group as a login in SQL Server. You can assign the necessary database roles to the group to allow the users to access the database.

Best regards,

Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".