In this article, we will guide you on how to create login, user, role, and assign permission from SQL Server Management studio. Here, we have divided the article into four parts. Login, User, Assign permission, and Roles. Let's see it one by one.
- 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. Select the Securables option located on the left side of the screen.
- In the Login name field, enter the login name. Here, we have entered Accu_sample as an example. Select the authentication method which you want to set for the login name. Here, we have selected SQL Server authentication as an example.
- Enter the password in the Password and Confirm password field. Click on the OK button to close the window.
- Click on the Logins folder. You can see the newly created Login.
- In the object explorer section, expand the databases folder.
- Select the database on which you want to create a user. Here, we have selected SolidcP as an example.
- Expand the selected database and select the Security folder.
- Expand the security folder. Right-click on Users and select the New User option.
- In the Database User - New window, enter your desired username in the username field. In the login name field, enter the Login name which you have created in the Login section. Here, we have entered Accu_sample because we had created it under the login section. Click on the OK button to create the database user.
- Click on the Users folder. It will show the newly created database user.
Assign Permission: (SQL Server allows to Grant, Revoke permission
- To assign permission, right-click on the database user and select the property option.
- In the Database User window, select the Securables option located on the left side of the database user screen. After then click on the Search button.
- By clicking on the Search button, a new pop-up window will be opened. Select All objects belonging to the schema under What objects do you wish to add?.
- In the Schema name, select dbo from the drop-down list. Then click on the Ok button to proceed further.
- Once you click on it, you will get data in the Securables section. Here, we will select the table to grant permission. You can select the option as per your requirement. Here, we have selected CRMuser as an example.
- Click on the Explicit tab. Select the permission which you want to assign to the database user from the Permission column and tick the box under the Grant column. Here, we have selected Select permission as an example. Click on the Okay to apply the changes.
The testuser_Accu_sample is granted SELECT permission on the table CRMuser
- In the object explorer, Expand the databases. Select the databases on which you want to add Role. Here, we have selected SolidCP as an example.
- Expand the selected database and click on the Security folder.
- Expand the roles folder. Right-click on the Database Role and select New Database Role option.
- After then a new popup window will be opened with the name Database Role - New. Select the General option. In the Role name field, enter the role name and click on the ok button.
- Again click on the Database Roles folder. You can see the newly created Role. That's all.