Security vs. Monitoring – Solution by using module signing

In this post, I provide my solution for the Security vs. Monitoring puzzle. I will use module signing.

First, let’s think a little bit proactive and I assume that there will be more special request in the future when we need to create some further code for monitoring, so I create a separate database called ‘Monitoring’. The ‘monitoring’ user will have rights only in this database, and just as much as it really needed.

After that I create a stored procedure called ‘usp_monitor’ with EXECUTE AS clause and in this user defined stored procedure the sp_monitor is called. I grant execute permission to the ‘monitoring’ user.

Nothing special so far. As I mentioned before, I use module signing to provide all the necessary permissions. For this I create a certificate in the ‘monitoring’ database then I sign the usp_monitoring with this certificate. After that I backup this certificate and then delete the primary key part so no-one can sign again the stored procedure in case of any modification without the primary key.

In the end, I restore the public part of the certificate to the master database, and I create a login from it, and grant authenticate server privilege to this login.

We are done. Only one thing left. Move the certificate (especially the private key part) to secure place. If you have any question feel free to contact me, leave a comment or drop me a mail /robertATsqlapprenticeDOTnet/.

Further on I will provide some very useful links/articles about this topic.

Posted in Security | Leave a comment

Security vs. Monitoring – Puzzle: SOLVED

In this post I will talk a bit about security and monitoring and show you one particular case.

There are many monitoring tool which is used, for example Tivoli or Nagios. These tools technically do the same thing: keep checking the servers and services like SQL Server services and in case of any problem in some way they notify the operator about the problem.

For doing this, these tools need to have access not just to the operating system but to the sql server as well. The real concern here is what kind of permission is needed for the monitoring tool’s user or what kind of permissions can be granted.

Servers which contain sensitive data, like credit card data have to meet  certain auditing requirements. Here is the main rule: Never give more permission to a user than it’s really necessary. Any deviation from the standards have to be explained, and usually some additional action has to be taken in this case!

Minimum permission for a monitoring user:
Membership in the public role in the databases which need to be monitored.

Most of the time these permissions are enough, but sometimes unfortunately not and that’s where our case come into the picture. So let’s see it.

Our customer implemented the Nagios to monitor the sql server instances and the databases as well. We set the minimum permissions to the nagios user. However there was one stored procedure sp_monitor, which is also used by Nagios and which has a strong restriction: executing this stored procedure requires membership in the sysadmin fixed server role.

Remember to the main rule! Granting sysadmin server role to a simple monitoring user not just ‘more than it’s necessary’ but this would be the biggest permission we can even grant. So obviously  we did not give that permission to the user mentioned.

I think the workaround is a well known one, a really easy one, unfortunately I did find comments in certain forums which said (Let me strikethrough this because it is evil!): ‘give sysadmin role to the user‘. NEVER EVER!

First I thought that I just post my solution, but I think it would be good if you share your ideas with me and then I will post every different solution.

So please send your solution to me / robertATsqlapprenticeDOTnet / or leave a comment and let’s show how easy to handle a situation like this.


My solution.

Posted in Security | Leave a comment

Project No. 1. – Universal foreach aka uforeach

Hi Everyone!

This is the first post of In this post I would like to introduce my universal foreach script.

So, here is the story:

There is a very good blog post about removing duplicated indexes posted by Kimberly L. Tripp. In this post there is a reference to a very useful sp_MSforeachdb replacement made by Aaron Bertrand.

It made me start thinking and I realized that lot of time we need not just to run the same script on different databases, but to run the same script on different objects such as logins, jobs. So Aaron Bertrand’s script inspired me to write an universal foreach stored procedure.

This stored procedure is very flexible.

Here is the syntax:

There are two options how you can provide the ‘looping objects’ which the script in the @command parameter have to run on.
First you can use an existing table name in the @table_name parameter (for example ‘sys.databases’) and in this case you can also provide a where clause without the word ‘where’ in the @where_clause parameter to filter the objects (for example ‘state = 0’).
Second, you can use a select statement with a where clause, like ‘select name from sys.databases where status = 0’. Using select statement the parameter @where_clause is ignored, but the parameter @column_name have to be provided, because the records of this column will be used as ‘looping objects’.

If you only provide the @command parameter, the stored procedure works like sp_MSforeachdb:

Let’s check some examples. You can get these examples using the @help=1 parameter as well. Pay close attention to the third example where I use sp_uforeach in nested way.


Download: sp_SQLApprenticeDOTnet_SQL2008_uforeach

I really hope that you can find this stored procedure useful. If so and you use sp_uforeach  then please drop me a mail (robertATsqlapprenticeDOTnet) and I post your real life example(s). Don’t hesitate to contact me in case of having any question or remark.



Posted in Project, Script, SQL | Tagged , , | Leave a comment