?
Solved

Validating if a user has the sysadmin role

Posted on 2003-02-28
6
Medium Priority
?
1,929 Views
Last Modified: 2012-05-04
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

Meowsh

0
Comment
Question by:meowsh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 800 total points
ID: 8041236
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

CHeers
0
 
LVL 23

Expert Comment

by:adathelad
ID: 8041255
Hi,

CREATE TABLE #ptblRole
(
strServerRole VARCHAR(100),
strMemberName VARCHAR(100),
MemberSID VARBINARY(85)
)

INSERT #ptblRole
EXECUTE sp_helpsrvrolemember 'sysadmin'

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

DROP TABLE #ptblRole
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 8042325
If a login is being passed in, you can check it like this:

DECLARE @login VARCHAR(255)
IF NOT EXISTS(SELECT * FROM master.dbo.syslogins WHERE name= @login AND sysadmin = 1)
     --user is NOT a sysadmin, error out
0
What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

 
LVL 5

Expert Comment

by:dtomyn
ID: 8042629
Once logged on you can also do this...
IF ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0)  = 1
     PRINT 'User is a sysadmin'
ELSE
     PRINT 'User is NOT a sysadmin'
0
 
LVL 3

Author Comment

by:meowsh
ID: 8090205
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.

Meowsh
0
 
LVL 3

Author Comment

by:meowsh
ID: 8719115
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.

Meowsh
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question