Find active node via fn_virtualservernodes...

So, I had to write a script today to grant localserver users access to a clustered dbserver. This presents an unusual situation, as the SQL VIP does not expose the correct servername for adding a local login. You cannot use @@SERVERNAME on a VIP to get the active cluster node....

So I wrote this whole big convoluted script, submitted it and five minutes later figured out that fn_virtualservernodes returns the active node as the first node in the list.

So, handy dandy tip for ya...

If you need to know the active node of a SQL cluster, query fn_virtualservernodes and whichever node it returns first is most likely the active node. I say most likely, because I have not had time to test it to 100% assurance. But I did failover several times and this was the behavior noticed.

This is from bol:
active node net name:SELECT
CONVERT(varchar(150), serverproperty('ComputerNamePhysicalNetBIOS'))
or for the cluster name:SELECT
CONVERT(varchar(150), serverproperty('MachineName'))