When an instance of SQL Server is running in a Clustered Environment, Microsoft Cluster Service performs two checks to confirm if the SQL Server resource is still available. One is LooksAlive and the other one is IsAlive check.
LooksAlive is a basic check in which the Cluster service queries the Windows Service Control Manager to check if the SQL Server service is still running. By default this check happens every 5 seconds. During IsAlive check the Cluster Service connects to the SQL Server instance with the help of c:\windows\system32\sqsrvres.dll and runs SELECT @@SERVERNAME against the instance. This check does not check if the user databases are online or not. It just checks the SQL Server instance availability. This check happens every 60 seconds by default.
During the IsAlive check the Cluster Service connects to the SQL Server Instance. What privileges does the Cluster Service Service Account needs to have on the SQL Server instance?
The cluster service account needs privileges sufficient to execute SELECT @@SERVERNAME command against the SQL Server instance. Any user who has Public rights on the instance can execute this query. By default, the Cluster Service startup account is part of the Local Administrators group on the cluster nodes. Until SQL Server 2005, the Builtin\Administrators group was granted SA privileges on the instance during SQL Server installation. Hence the Cluster Service startup account had System Administrator privileges on the instance. Usually during the hardening process it is a common practice to “revoke” access to Builtin\Administrators login on the instance. If this is done on an instance running in Clustered environment, the cluster service service account will not have access to the instance and as a result the SQL Server resource will not come online after hardening! Similar issue occurred in some other team in my organization. They tightened the instance too hard. The issue was resolved after the Builtin\Administrators login was granted Public or higher privileges on the instance.
On a closing note, starting Windows Server 2008 the Cluster Service does not use a domain account to start, by default it uses the Local System Account (NTAUTHORITY\SYSTEM). One should be careful not revoke the access for NTAUTHORITY\SYSTEM login on an instance running on Windows Server 2008.
@Maha, hope this post answers your question.