Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Following up on my original StackOverFlow question, we're trying to create a SQL user (on many 2000, 2005, and 2008 servers) with the minimum possible permissions for doing a simple "heartbeat check" to make sure the instance is still alive. We'd settled on the script executing a simple "SELECT @@VERSION".

In the name of "Minimum allowed permission" we'd like to prevent this user from doing (nearly) anything else, though. Unfortunately, even without adding any explicit permissions, the PUBLIC role (that all users are members of) can do quite a bit. Specifically:

SELECT * FROM SYSDATABASES
Ok, I can explicitly add the user to the db_denydatareader role in the MASTER database to prevent the reading of any system tables

SP_WHO
Ok, I can explicity DENY ALL ON SP_WHO, but what about SP_WHO2 and all the other system stored procs? Do I have to explicity deny all of these?

So since users can't be removed from the PUBLIC role, any other way to prevent the running of system stored procs rather than an explicit deny on each and every one?

Are the system stored procs even worth worrying about? Anything besides sp_who that I really shouldn't want this user to be able to see?

share|improve this question

3 Answers 3

up vote 1 down vote accepted

In SQL 2005 and up, while the user can query the system objects, they can only see information they have access to. For sys.databases you can only see the databases you have access to. When you run sp_who you'll only see information on your session.

share|improve this answer
    
Awesome, that should satisfy any remaining concerns... –  BradC Sep 16 '09 at 13:00

You're fine. Just don't grant your 'heart-beat' account access to any of the databases.

Just think about it-the SELECT @@VERSION command you're running is part of those stored procedures-so you would need to build a complete list of all the other inbuilt functions and specifically block access to them. A user with no write permissions to your database server and no read permissions on your databases aren't going to be able to cause any havoc.

share|improve this answer

To lock down the level you want (I answered your other question), you'll end up breaking things.

I really have to disagree with your intention.

I'd combine a heartbeat with a health monitor too. eg look for blocks, record CPU, use the dmv etc

I also hope you lavish as much attention on the rest of your network, server, PCs etc.

For example, SQL Server only, are you using Server side SQL Server protocol encryption? Only using tcp? Using group policy to limit who can access the server? SQL logins follow domain password policy? SQL service account locked down?

share|improve this answer
    
Well, we do have other monitor scripts that fill those other purposes, under the control of the DBA team. This new script, though, is being built into a new centralized monitoring app that is going to be used by the 24x7 help-desk. That's the reason for the tight security requirements. –  BradC Sep 15 '09 at 18:55

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.