how to check if user has system admin privileges in SQL Server 2008 R2

Question

I have a application that should only allow access to users who are system admins on the db. What sql script or query can I execute from the c# code that, given a username, can determine if he/she has system admin privileges system specs: sql2008R2 db, .net 2.0, c#

Solution

Use IS_SRVROLEMEMBER.

SELECT IS_SRVROLEMEMBER('sysadmin', 'YourLogin')

Comments

[meir]

This solution does not provide correct result if account is a member of some group that is in sysadmin role. DOMAIN\user1 is a member of DOMAIN\DBAs, DOMAIN\DBAs is in sysadmin role, hence DOMAIN\user1 is sysadmin. Checking SELECT IS_SRVROLEMEMBER('sysadmin', 'DOMAIN\user1') will result NULL.

[Steam]

Joe's query gives me 1 and meirs query gives me NULL.