January 04, 2008

How to find out if the logged in user is a sysadmin member

'sysadmin' is one of the fixed server roles. As stated in SQL Server 2005 Books Online, each member of a fixed server role can add other logins to that same role.

The following query returns the list of available fixed server roles
EXEC sp_helpsrvrole;

Members belonging to the sysadmin server role have the permissions to perform any activity in the server.

In order to find out if the logged in user is a sysadmin:
SELECT IS_SRVROLEMEMBER( 'sysadmin', '' );

Returns 1 if user is a sysadmin, 0 if user is not.

In order to find out the permissions associated with the sysadmin fixed server role:
EXEC sp_srvrolepermission 'sysadmin';

About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

1 comment:

Hemant... said...

Administrators Group is Sysadmin... but member of Administrator group still returns 0 for this query.

Is there any other way to find if user\Group is having sysadmin permission or not ?