Posted by Erland Sommarskog on 07/11/05 11:23

Wolfgang Kreuzer (wolfgang.m.kreuzer@gmx.de) writes:
> My attempt was to use INSERT EXEC to fetch te results of several
> sp_helprolemember calls in a temp table (in some stored proc;
> up_EnumSuperUsers; up_EnumApplAdmins ...) and return collected data as
> a record set. In a wrapping stored proc I intended to collect data
> from some of the proc's above and check if current user or specified
> user is in the list - but as far as I know evaluating data from a
> stored proc record set requires INSERT EXEC which does not allow
> nesting for whatever reason.
>
> On the other hand, UDF's do not allow usage of non-deterministic
> functions, call of stored proc, INSER, CREATE etc.

You can also use temp tables, as I discuss in the article I pointed you to.

> What I have done is, I took the SQL statement out of sp_helprolemember
> put it in several UDF's which just returns a BIT value indicating if
> someone is member of a role or some of them.

I don't know exactly what you are doing, but have you looked at the
built-in function is_member()?



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]