Validating if a user has the sysadmin role
Posted on 2003-02-28
In my job the database is created through an install program that ulitimately runs a .bat file with lots of osql commands in it.
As part of the install program the user is asked to enter the userid which will run the database install. This user may be either OS or SQL server authenticated.
As part of the install I want to check that the credentials supplied correspond to a login that has the System Administrator server role.
I had a look in BOL and the most promising system proc looks like EXEC sp_helpsrvrolemember 'sysadmin'. This will return me the members of the sysadmin role.
My problem is a programmatic one. How can I use the result set of this system proc to validate a login? I am thinking that what I need to do is write a procedure that has an input parameter of P_username, trap the result set of sp_helpsrvrolemember in a temp table and then validate the the p_username exists in that temp table and return a sucess/fail flag.
So in summary, the problem is how do I trap the result set from a system proc.
Also does anyone have any better ways of doing this - I need to ultimately check whether the credentials have the sysadmin role or not.