Minimum permissions for a SQL server "heartbeat" check?

Question

We are developing an application to do a "hearbeat" test of all our SQL servers (2000, 2005 and 2008), and we're trying to figure out the minimum permissions necessary on the SQL server to do that. (Platform involved is TBD, but should use a standard connection string).

We are considering using a "SELECT @@VERSION" query, which should be all that is necessary to determine that the sql server is up and running and responding to requests.

What are the minimum SQL permissions necessary to simply connect to the server with a connection string? (We don't even want to give DataReader, if at all possible) Will those minimum permissions allow me to do the above query? Is there a better/more standard technique for doing this? Will SQL 2000, 2005 and 2008 behave differently?

Answer 1

Consider SQLPing.

Quote: "SQLPing can be used to discover detailed information about the connectivity of SQL Server 2000 installations without authentication of any kind."

Or google for SQLPing yourself. I've seen several other utilities of the same name.

Answer 2

All that is required is a permission on the tempdb database. This database is guaranteed to be present across all servers and all versions.

This will also make sure that the audit requirements are met as you do not have to give access to any other database for the heartbeat check.

Answer 3

Just create the login used by the monitor code. On SQL Server 2005 and 2008, you'll also need GRANT CONNECT SQL TO (login), otherwise it's the same.

No other rights are needed for SELECT @@VERSION. The db_datareader you mention is within a database, not the server level.

SQLPing will detect an installation but not always tell you if it's running.

When do do run SELECT @@VERSION, make sure that you open and drop a connection. A connecion left open may still work even if the SQL Servr is not accepting new connections.