Home » Microsoft » 70-762 » How should you complete the Transact-SQL statements?
DRAG DROP
You maintain a Microsoft Azure SQL Database instance.
You grant User1 the SELECT and EXECUTE permissions for all objects in the dbo schema.
You must create a stored procedure that allows User1 to view the following information: details for each connection to the database a list of all active user connections and internal tasks
You need to create the stored procedure for User1 and ensure that User1 can run the stored procedure without any error.
How should you complete the Transact-SQL statements? To answer, drag the appropriate Transact-SQL segments to the correct locations. Each Transact-SQL segment may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
NOTE: More than one combination of answer choices is correct. You will receive credit for any of the correct combinations you select.
Select and Place:
Correct Answer:
Explanation/Reference:
Box 1: Sys.dm_exec_connections
Sys.dm_exec_connections returns information about the connections established to this instance of SQL Server and the details of each connection. Returns server wide connection information for SQL Server. Returns current database connection information for SQL Database.
Box 2: sys.dm_exec_sessions sys.dm_exec_sessions returns one row per authenticated session on SQL Server. sys.dm_exec_sessions is a server-scope view that shows information about all active user connections and internal tasks.
Box 3: GRANT VIEW DATABASE STATE To User1
SQL Database: Requires VIEW DATABASE STATE to see all connections to the current database. VIEW DATABASE STATE cannot be granted in the master database.
Incorrect Answers:
Sys.dm_exec_requests returns information about each request that is executing within SQL Server.
References: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-sessions-transact-sql?view=sql-server-2017