Laboratory Exercise

User, Privileges, Roles

2. Next, right-click the server instance name and select Properties to open the
Property window.
3. From the property window, select the Security tab and enable the SQL User login to
SQL Server by selecting the SQL Server and Windows Authentication mode and
click OK. SQL Server is configured for mixed-mode login.
4. To create SQL Login, right-click the Security tab again and select New, then Login.
The Login dialog box shall appear.

5. Give a login name. Type User1.

6. Select SQL Server Authentication.

7. Provide the desired password. Type User1password.

8. Disable Enforce password policy.

9. Select the default database. Select master. Leave the default language.
10. Click OK to create a new SQL Server login.

11. To check the newly created login, expand the Security folder, and expand the Login
folder.

12. Create a database named ”demo” and locate the SQL file named 05 Laboratory
Exercise 1 Data_Insert. Execute the following commands inside the file by opening a
new query window and drag the file to the query window. If the data have been
successfully inserted, close the query window.

13. We can now use the newly created Login. To do this, right-click the server instance
name and select Connect.

14. Select SQL Server Authentication and insert the login credentials that we have
created previously, then click Connect.
15. We have two (2) logins that are now connected to SQL Server
16. Try to open/access the “demo” database in User1 login by expanding the Databases
folder and expand the “demo”. You will get an error like this. (See the image below)

17. In order to access it, you need to create a user for that database. To create a user,
under the Windows default login, expand the Databases, then expand demo. Expand
the Security folder and right-click the Users folder, then click New User.
18. Put User1 as username, and locate the User1 in Login name.

Note: Since we have created a user using User1 login for “demo” database, we can now
access the database using User1 login. However, User1 login does not have any
permission to access any data from the “demo” database.
19. To give permission, under the Windows default login, expand the Databases, then
expand demo. Expand the Security folder, right-click the Roles folder, then select New
Database Role.
20. Under the General tab, create a user-defined role named db_demo_access. Then
under the Securables tab, click Search.
21. Click All objects belonging to the schema: then click dbo or the name of the
database. Then, click OK.

22. We want to give the user-defined Role permission where he can only read and
update the data from the demo database. To do this, check the grant corresponding to
SELECT and UPDATE. Then click OK.
23. Locate the newly created Role in Roles folder, then double-click it. We want to add
User1 as a member of this role. After adding User1 to the role, User1 will now have a
privilege where he can read and update the data from the demo database.