This process is essential in the DP-300 Administering Microsoft Azure SQL Solutions exam and involves setting up security measures that control user access to data and other resources. This article breaks down the steps to configure these permissions using the most reliable graphical tools, namely the SQL Server Management Studio (SSMS) and Azure portal.
Using SQL Server Management Studio (SSMS)
SQL Server Management Studio, also known as SSMS, is a comprehensive suite of tools providing integrated visual environments for SQL infrastructure. It facilitates the task of managing Azure SQL database permissions.
- Database Level Permissions
Here’s how you can configure database level permissions using SSMS:
- Connect to your SQL server and expand the ‘Databases’ folder.
- Right-click on the specific database you’d like to configure, and select ‘Properties’.
- In the ‘Database Properties’ dialog box that appears, select the ‘Permissions’ page.
- Here, you can add or remove users, and also set their permissions by choosing from the options such as ‘Grant’, ‘With Grant’, or ‘Deny’.
- Object Level Permissions
Just like managing database level permissions, SSMS provides a graphical user interface for setting object-level permissions:
- Navigate to the database containing the object you want to manage.
- Expand the ‘Security’ and ‘Schemas’ folders to view the objects.
- Right click on the object and select ‘Properties’.
- In the ‘Object Properties’ dialog box, select the ‘Permissions’ page.
- Similarly, add or remove users and set their permissions by choosing ‘Grant’, ‘With Grant’, or ‘Deny’.
Using Azure Portal
Azure portal is another powerful graphical tool for managing Azure SQL Server and Database permissions.
- Database Level Permissions
To set up database level permissions, follow these steps:
- In Azure portal, go to the SQL databases page and select your database.
- In the settings menu, select ‘Access control (IAM)’.
- Click ‘+Add’ to assign a new role, select the role and assign access to the user or group.
- Object Level Permissions
At present, the Azure portal does not provide a GUI for setting object-level permissions directly, so you would need to use Transact-SQL (T-SQL) commands.
However, Azure does provide a Query editor (preview) tool in the portal:
- Select the ‘Query editor’ in the SQL database page.
- By executing the GRANT command, you are able to assign specific object permissions to the user or role. For example,
GRANT SELECT ON OBJECT::dbo.Tests TO TestRole;
In conclusion, Azure SQL database and object-level permissions settings are crucial routines in database administration. Using graphical tools such as SSMS and Azure portal streamlines the process and eliminates the necessity to memorize Transact-SQL commands for every single operation. However, please remember that for some tasks, like setting object level permissions in Azure portal, using T-SQL commands is still necessary.
Practice Test
True or False: Configuring database and object level permissions using graphical tools in Azure SQL solutions can be accessed by users with any level of permissions.
- Answer: False
Explanation: Not every user has the permission to configure database and object level permissions. Usually, only admins or users with high-level access permissions can configure these settings in Azure SQL solutions.
Which of the following tools can be used to configure database and object level permissions on Azure?
- a. Azure Bulk copy utility
- b. Microsoft SQL Server Management Studio (SSMS)
- c. Azure Devops service
- d. Microsoft Excel
Answer: b. Microsoft SQL Server Management Studio (SSMS)
Explanation: Microsoft SQL Server Management Studio (SSMS) is a software application that is used for configuring, managing, and administering all components within Microsoft SQL Server. It is the primary tool for Database Administrators (DBA’s) and developers.
True or False: Object-level permissions are more specific and can overrule database level permissions.
- Answer: True
Explanation: Object-level permissions allow for granular control of permissions within database objects such as a table, view, or stored procedure. If there’s a conflict between object-level and database level permissions, the object-level permissions are considered first.
What are the two levels of security managed in Azure SQL database?
- a. User-level and database-level
- b. Server-level and database-level
- c. Object-level and server-level
- d. Object-level and user-level
Answer: b. Server-level and database-level
Explanation: Azure SQL Database has its security architecture managed on two levels: Server-level security and Database-level security.
Which permissions allow a user to view records in a table of a database in Azure?
- a. Server-level permissions
- b. Reader permissions
- c. Object-level permissions
- d. Selector permissions
Answer: c. Object-level permissions
Explanation: Object-level permissions provide access at a lower level, which encompasses entities like schema, tables, views, and procedures. Therefore, to view records, object-level permissions are necessary.
True or False: Seperate permissions are required for performing DDL operations like CREATE, ALTER, and DROP.
- Answer: True
Explanation: For DDL operations, specific permissions such as CREATE, ALTER, and DROP are required. These permissions can be defined for a user or user group in Azure SQL.
What would be the most appropriate graphical tool to grant permissions to a user to perform DDL operations in the database?
- a. Microsoft Visio
- b. Microsoft SQL Server Management Studio (SSMS)
- c. Microsoft Project
- d. Microsoft Power Point
Answer: b. Microsoft SQL Server Management Studio (SSMS)
Explanation: The SQL Server Management Studio (SSMS) can be used to grant permissions, as it provides a user interface for managing and administrating Azure SQL databases.
True or False: We can assign roles to a user using Windows PowerShell.
- Answer: True
Explanation: Windows PowerShell can be scripted to execute T-SQL commands that would alter and manage permissions, including roles assignment to a user.
True or False: SQL Server Agent is used for configuring the database and object level permissions on Azure.
- Answer: False
Explanation: SQL Server Agent is a tool for job scheduling and is not used for configuring database and object level permissions.
In Azure SQL Database, which access level is always the owner of the schema?
- a. db_owner
- b. db_datareader
- c. db_ddladmin
- d. db_accessadmin
Answer: a. db_owner
Explanation: The db_owner has full capabilities and is the owner of the schema in an Azure SQL Database. This includes the ability to perform DDL operations.
Interview Questions
Which Azure graphical tools can be used to configure database and object-level permissions?
Azure Data Studio, SQL Server Management Studio (SSMS), and Azure Portal are main graphical interfaces for configuring database and object-level permissions.
How can database permissions be assigned to a user in SQL Server Management Studio (SSMS)?
In SSMS, open the Security folder, right-click on Users, click on New User, fill out the General, Membership, and Securables pages.
Can you configure object-level permissions directly through the Azure portal?
No, object-level permissions need to be configured through SQL scripts or tools like SSMS or Azure Data Studio.
What steps do you follow in SQL Server Management Studio (SSMS) to configure object-level permissions for a particular database object?
In SSMS, expand the database, then expand Security, Roles, Database Roles. Right-click desired role, select Properties, then select the Securables page. Click Add, select Specific Objects, choose your object, then select the permissions you want.
How can you assign a user to a role using Azure Data Studio?
Open the Server dashboard, navigate to “Security” and then “Roles”. Then edit the role to add or remove users. Alternatively, use the ALTER ROLE command.
What is the purpose of the “GRANT” command in SQL?
The “GRANT” command is used to give users access to a database or modify their permissions on an object within the database.
In SQL Server Management Studio (SSMS), where are object-level permissions managed?
Object-level permissions are managed under the database object’s Properties dialog, in the “Permissions” page.
If a user has been granted permission to a table in a database, can the user access that table without having database connect permissions?
No, a user must first have connect permissions to the database before they can access the tables, even if they have permissions for the table.
What is the “REVOKE” command in SQL used for?
The “REVOKE” command is used to remove a user’s access or permissions from a database or database object.
How can you check if a specific user in a database has a certain permission on an object using SQL Server Management Studio (SSMS)?
In SSMS, expand Security, then expand Users, select the user, and view the permissions tab.
Which command is used to remove permission but to ensure the permission cannot be granted again?
The “DENY” command is used to remove and prevent granting permission again.
Is it possible to configure database permissions for a group of users at once in SSMS?
Yes, you can assign permissions to a role, and then assign multiple users to that role.
Can Azure Data Studio be used to manage both database and object-level permissions?
Yes, you can use Azure Data Studio to manage both database and object-level permissions using SQL commands.
How do you handle permission conflicts, like when a user is part of a role that has permissions denied on an object, but the same user is granted permissions on the object directly?
In Microsoft SQL Server, a DENY takes precedence over a GRANT, which means if a user is part of a role that has permissions denied, it will overrule any other granted permissions.
What is the meaning of “EXECUTE AS” clause in Transact-SQL?
The “EXECUTE AS” clause allows you to execute a module (a stored procedure, function, or trigger) as though you were a different user, which is useful for testing permissions.
extutorials.com