Celebrate Our 22nd Anniversary with Huge Savings! Up to 70% Off

How to create login, user, role and Assign permission from SSMS?

In this article, we will guide you on creating a 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

1. Login into the SSMS with the respective user.

2. Go to the Object Explorer section and click on the Security folder.

3. Right-click on the Logins. Select the New Login option and click on it.

Security - New Login

4. Once you click on it, a new window with the name Login - New will be opened. Select the Securables option located on the left side of the screen.

5. 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.

6. Enter the password in the Password and Confirm password field. Click on the OK button to close the window.

New Password

7. Click on the Logins folder. You can see the newly created Login.

New Created Login

User

1. In the Object Explorer section, expand the databases folder.

2. Select the database on which you want to create a user. Here, we have selected SolidcP as an example.

3. Expand the selected database and select the Security folder.

4. Expand the security folder. Right-click on Users and select the New User option.

New User

5. In the Database User - New window, enter your desired username in the username field. In the login name field, enter the Login name you 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.

Database User - New

6. Click on the Users folder. It will show the newly created database user.

Users folder

Assign Permission: (SQL Server allows to Grant, Revoke permission

1. To assign permission, right-click on the database user and select the property option.

User Properties

2. In the Database User window, select the Securables option located on the left side of the database user screen. After that, click on the Search button.

Securables

3. 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?".

4. In the Schema name, select dbo from the drop-down list. Then click on the Ok button to proceed further.

What objects do you wish to add?

5. 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.

Grant Permission

6. 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 Okay to apply the changes.

The testuser_Accu_sample is granted SELECT permission on the table CRMuser.

Role

1. In the object explorer, Expand the Databases. Select the databases on which you want to add the Role. Here, we have selected SolidCP as an example.

2. Expand the selected database and click on the Security folder.

3. Expand the roles folder. Right-click on the Database Role and select New Database Role option.

New Database Role

4. 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 the OK button.

Enter Role Name

5. Again, click on the Database Roles folder. You can see the newly created Role. That's all.

New Created Role


Was this answer helpful?

« Back

chat