Question about using system stored procedure in SQL7

[Winla]

In order to retrieve all usergroup names for a user (myusername), I am creating my own stored procedure, which calls a system procedure sp_helpuser which returns a table containing what I want

The syntax in SQL query would looks like


Use myDatabase
go
Execute sp_helpuser 'myusername'
go


I got two problems in stored procedure with it.

1) I cann't use 'USE mydatabase' in stored procedure, How to instruct sp_helpuser to check 'sysusers' table for 'mydatabase' instead of for 'master' database?

2)sp_helpuser returns a table, one of whose columns contains usergroup infomation, How to select that column out of the table and return them to my VB program(should be done in stored procedure as well, sample code please).

Your helps are greatly appreciated.

[bsmith]

Take a look at the sp_helpuser SP. You may want to take the select out of it and modify it to return what you are wanting. The master database is the correct one to be accessing. The master contains the tables that hold the user information. I am not exacly sure what you are wanting or I might be able to give a little bit more help.

[Winla]

Thanks bsmith.

In sp_helpuser SP, three tables are involved: sysusers, sysmembers, and master.dbo.syslogins. The first two tables are database-dependent(each database has its own users, members. In fact the sysusers table in master db is for master db itself, but i don't know where these tables for other DBs are stored, only way that I can check this is to select DB first and then use "select * from sysuser " to see result and the returned table is database-dependent).

So, before calling sp_helpuser, I have to specifiy the database somehow. I can use USE function in SQL statement, but it is not permitted in SP. Any way to go around this?


Another question is how to filter a table returned from calling a system SP. I know I should use "select - from " structure, but I don't know how to combine select -from with a SP Call in SP. something like

select * from (execute sp_helpuser myusername)

What is correct syntax to do it in SP?

Help I have clarify it a bit. Thanks

[bsmith]

I see now what your problem is. Nothing has come to me on how to do what you are asking. The only thing I can think of is to rewrite the sp_helpuser SP. If you create a new SP and base the code on the sp_helpuser SP you can get what your looking for. I know this isn't the best solution, but I am not sure how to do what you are asking.

Sorry I wasn't much help.

[Winla]

Thanks Bsmith for your replay.

While, sp_helpuser sp is a very long, complicated SP, with some syntax I am not fully understanding. I might get right result through changing it, but I want to know the answer behind this question. I believe this is a basic question in order to use system SP.

I was trying to make my own SP using syntax like

Create procedure MyProcedure
@username varchar
as
select distinct u.name from sysusers u, sysmembers m
where u.uid=m.groupuid
and u.name = @username

It works fine when executed in Query Analyzer, but doesnot work When I call it from ADO. Same problem! I have to specify DB first before calling SP.

Anyone else has an answer? thx

[bsmith]

Why can't you change your ADO connection to the database that you want the results for?

[Winla]

My program is always connected to mydatabase. When I call the SP I wrote through ADO, I get a error msg " The name supplied (s) is not a user, role, or aliased login" , which means that the server doesn't find that username in the related system table, an exactly similar message when I use it in Query Analyzer without specifying Database(by default,the msater database is checked). So I guess I have to specify DATAbase in my SP somehow. How?



[This message has been edited by Winla (edited 02-10-2000).]

[JHausmann]

Set the "Initial Catalog" property to the database you want to use, in your connection.

[Winla]

Yes, that is exactly what coded in my program! thx

[bsmith]

Are you sure that the value being passed into your stored procedure is the value you are comparing against in the WHERE clause? I was thinking I read some where that if define a parameter as varchar then you only get one byte. See what happens if change the varchar to varchar(20) in your stored procedure.

Brad

[Winla]

Thanks, Bsmith, Varchar(20) is mandatory! I was wrong! That might be part of the problem. Here is the Right code for Getting all usergroup names from a username:


CREATE PROCEDURE sp_FindUserGroup
@Username varchar(20)
as
SELECT
usu.name
,case
when (usg.uid is null) then 'public'
else usg.name
end
from
sysusers usu left outer join
(sysmembers mem inner join sysusers usg on mem.groupuid = usg.uid) on usu.uid = mem.memberuid left outer join master.dbo.syslogins lo on usu.sid = lo.sid
where
usu.islogin = 1 and usu.isaliased = 0 and usu.hasdbaccess = 1) and
(usg.issqlrole = 1 or usg.uid is null) and
usu.name = @username

This is based on system sp_helpuser sp.


But I am still waiting for an answer on how to specify a Database in SP.

[bsmith]

I thought that what ever database the "Initial Catalog" property was set to would be where your results would come from.

Is this not the way the SP is working from VB?

What database are the results coming from, the database where the SP is located?

Is the SP located in the same database as the results that you want?

[Winla]

Thanks Bsmith, I think you are right, you don't have to specify the database in the SP. You choose DB through your connection and location of SP. I jsut tried again using following sp,

create procedure sp_myfindsp
@username varchar(20)
as
execute sp_helpuser @username

it works, much simple!

thx

[bsmith]

Sorry for the confusion earlier.