• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1940
  • Last Modified:

Validating if a user has the sysadmin role

Hi experts,

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.

Thanks experts


1 Solution
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Look at the code of that stored proc, and you have the answer:
>>EXEC sp_helptext sp_helpsrvrolemember

select 'ServerRole' = spv.name, 'MemberName' = lgn.name, 'MemberSID' = lgn.sid
               from master.dbo.spt_values spv, master.dbo.sysxlogins lgn
               where spv.name = 'sysadmin' and
                      spv.low = 0 and
                      spv.type = 'SRV' and
                      lgn.srvid IS NULL and
                      spv.number & lgn.xstatus = spv.number

or, if you really want to use the stored procedure:

create table xy(ServerRole sysname, membername varchar(100))
insert into xy exec sp_helpsrvrolemember 'sysadmin'
select * from xy ...
drop table xy


strServerRole VARCHAR(100),
strMemberName VARCHAR(100),

INSERT #ptblRole
EXECUTE sp_helpsrvrolemember 'sysadmin'

IF EXISTS(SELECT 1 FROM #ptblRole WHERE strMemberName = 'me' AND strServerRole = 'sysadmin')
          -- User does have sysadmin rights.
          -- Code....
          -- User does not have sysadmin rights.
          -- Code....

DROP TABLE #ptblRole
Scott PletcherSenior DBACommented:
If a login is being passed in, you can check it like this:

IF NOT EXISTS(SELECT * FROM master.dbo.syslogins WHERE name= @login AND sysadmin = 1)
     --user is NOT a sysadmin, error out
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Once logged on you can also do this...
IF ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0)  = 1
     PRINT 'User is a sysadmin'
     PRINT 'User is NOT a sysadmin'
meowshAuthor Commented:
Hi experts,

Thanks for all your answers.  Due to changing priorities, I have yet to start the piece of work that this question is associated with.  Thus I havent yet decided how to solve this problem and thus havent picked a winning answer yet.

I will do so over the next couple of weeks and will then award the relevant expert the points.

meowshAuthor Commented:
Thanks to all for their participation.

In the end I let the install program call sp_helpsrvrolemember and interpret the results set.

Thus it seemed fair to give the points to angelIII who first mentioned the use of the sp.


Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now