Home / server / Questions / 65599
Accepted
BradC
Asked: 2009-09-16 08:57:32 +0800 CST

Deny SQL rights to all system stored procs

  • 772

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?

sql-server

3 Answers

  1. 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.

    • 1
  2. Best Answer

    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.

    • 1

  3. 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?

    • 0