How To Give Restrict Access To dmw and dmf To Logins Without Giving View_Server_State Permission

In the article “How To Authorize non-admin Logins To Read DMWs and DMFs“, I explained how to give dmw and dmf read privileges to logins.

However, with the authorization given in the related article, they can see queries from all databases in instance.

This is a security vulnerability if you have more than one database on the instance. In this article we will learn how to give enough authority to each application developer to see the queries requested from his own database.

First of all, let’s create a database named Test and create a login named TestLogin which is authorized in this database. The following articles will help if you don’t know how to do the related steps.

“How To Create a Database On SQL Server“,

“How To Create a Login On SQL Server(Manage Logins)”

First of all, we need a database with the TRUSTWORTH feature enabled on the instance. After you create the database, you can activate TRUSTWORTHY in your database but I do not recommend it. Because a TRUSTWORTHY-enabled database has been set reliably for SQL Server.

While this feature is active, someone with db_owner privilege in the database can execute a query through a user who has sysadmin privilege with the command EXECUTE AS.

In addition, “.NET” compiled code can be executed in a TRUSTWORTH-enabled database. This is a security vulnerability.

For these reasons, let’s create a new database for these processes. I set the database name as TRUSTDB (you can give another name).

Also, create a sql login named sysadminUser with sysadmin right on the instance.

Activate TRUSTWORTHY in this database with the following script.

Then, with the help of the following script, create a stored procedure for the related user to see only the queries requested from their database.

Instead of “sysadminUser” you must enter a login name with sysadmin right on your instance, and the database name of the corresponding login instead of TestDB. The relevant login will be able to see the queries that will be requested from the database you will be typing here.

You must give the execute rights to the user you want to authorize on the Stored Procedure.

First, we give public right to Testlogin(the user who will be execute this stored procedure) on the TrustDB database.

Then, right-click on the TrustDB and click Properties on the related stored procedure as follows.

You must give the execute rights to the user you want to authorize on the Stored Procedure.

First, we give public right to Testlogin(the user who will be execute this stored procedure) on the TrustDB database.

Then, right-click on the TrustDB and click Properties on the related stored procedure as follows.

On the screen that appears, click Permission tab and click Browse to select TestLogin as follows and click OK.

At the bottom screen, we click OK and we give Execute right as follows when TestLogin is selected.

We have authorized TestLogin to execute on a stored procedure that will only show queries reqeusted from database named TestDB.

To test in the next step, you can log in with TestLogin and run the stored procedure as follows.

You can make different examples with similar methods. For example, for another application developer, I created a stored procedure that shows locked queries.