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