- Login into the SSMS with the respective user
- Go to the Object Explorer section and click on the Security folder.
- Right-click on the Logins. Select the New Login option and click on it.
- Once you click on it, a new window will be opened with the name Login - New. In the login name field, enter the name which you want to use as a new user. Here, we have entered Accutest as an example.
- Select the authentication method which you want to use for the new user. Here, we have selected SQL Server authentication as an example.
- In the password field, enter the password for the new user.
- In the confirm password field, re-enter the same password which you have typed in the password field.
- In the default database field, select the database which you want to set as a default. Here, we have selected master as an example.
- Click on the Server Roles option located on the upper left side of the Login-New screen. Go to the Server roles section and select sysadmin role. If you do not want to assign the sysadmin role to the user, then you should grant minimum permissions to the user from the User Mapping section.
- Go to the Select a page section and click on the User Mapping option located on the upper left side of the Login-New window. In the Users Mapped to this login section, select the database which you want to assign to the new user. Here, we have selected database example as an example.
- Next is Database role membership. Here, you need to select the role for your user. Here, we have selected db_owner an example. Please note that by default, the public role is assigned to the new login ID.
- Again, go to the Select a page section and click on the Securables option. After then click on the Search option.
- Once you click on it, a pop-up will be opened with the name Add Objects. Select the third option and click on the OK button.
- Under the Securables section, you will get the same option which you have selected under Add Object pop-up window.
- In the Explicit section, select the permission which you want to assign to new user and ticked the box under the Grant column. Here, we have assigned View any database permission to Accutest user. Click on the OK button to create the new user and assign permission.
- Again, click on the Logins folder. You can see that the newly created user is created. Now, you can access your database with the newly created user. That's all.
