Membership of a User in a Role 
Author Message
 Membership of a User in a Role

Hi there,
in an SP i am trying to figure a way of finding out
wheather a User that i receive as variable is a member in
a role (not a server role).  The Only means i found so far
where joins between the sysusers and sysmembers.

Here is an example:

The following script would create the User DB_CASU_OR
/*
BEGIN
*/
if not exists (select * from master.dbo.syslogins where
loginname = N'DB_CASU_OR')
BEGIN









END
/*
END
*/
GO

The follwoing Script would create the Role
/*
BEGIN
*/
if not exists (select * from dbo.sysusers where name =
N'ROL_DB_CASU_OR' and uid > 16399)
        EXEC sp_addrole N'ROL_DB_CASU_OR'
/*
END
*/
GO

The following script would add the user to the Role
/*
BEGIN
*/
exec sp_addrolemember N'ROL_DB_CASU_OR', N'DB_CASU_OR'
/*
END
*/
GO

SO my question is: if DB_CASU_OR (or an NT-User that is a
member of this role) is logged on, how can i proof that
this user is a member of this role.....

Thanks in advance!!!!

Greetings
N. Brake



Sat, 20 Nov 2004 00:52:36 GMT
 Membership of a User in a Role

Hi there,

rereading my oroginal posting i thought i should clear
some of the points in it:
- I am trying to implement a system of priviliges that is
not covered by the DB-Role.  What i need is to grant
certain users access to certain members of an entity.
- I have the DB-Schema for this with a table for users,
Roles, entities, and a cross-table that provides for the
allocation of a user in a certain role to a certain entity.

What i am looking for is a mean of proofing the given user
(not the logged on user) against the privlege (a standard
DB-Role) he is supposed to possess.

In case this is not possible, no damage will be done as
users without the rights privileges willnot be able to
execute the SPs any way, it just wouldn't be nice not to
be able to proof this before a row is inserted in the
allocation table

Greetings

Nasser Brake



Sat, 20 Nov 2004 01:50:14 GMT
 Membership of a User in a Role
FYI:

I suggest looking at IS_SRVROLEMEMBER ( 'role' [,'login'])
and IS_MEMBER ({'group' | 'role' }) in BOL

Tim S

Quote:
>-----Original Message-----
>Hi there,

>rereading my oroginal posting i thought i should clear
>some of the points in it:
>- I am trying to implement a system of priviliges that is
>not covered by the DB-Role.  What i need is to grant
>certain users access to certain members of an entity.
>- I have the DB-Schema for this with a table for users,
>Roles, entities, and a cross-table that provides for the
>allocation of a user in a certain role to a certain
entity.

>What i am looking for is a mean of proofing the given
user
>(not the logged on user) against the privlege (a standard
>DB-Role) he is supposed to possess.

>In case this is not possible, no damage will be done as
>users without the rights privileges willnot be able to
>execute the SPs any way, it just wouldn't be nice not to
>be able to proof this before a row is inserted in the
>allocation table

>Greetings

>Nasser Brake
>.



Sat, 20 Nov 2004 02:48:43 GMT
 Membership of a User in a Role
Here's an start of an way to do it under SQL2000

create table #tempmembers
(
DbRole sysname,
MemberName sysname,
MemberSID varbinary(85)
)



=  ''application'''

insert into #tempmembers (DbRole ,MemberName, MemberSID)


Tim S
PS I re-read what you asked for an realized that is_member
would not work.

Quote:
>-----Original Message-----
>FYI:

>I suggest looking at IS_SRVROLEMEMBER ( 'role' [,'login'])
>and IS_MEMBER ({'group' | 'role' }) in BOL

>Tim S

>>-----Original Message-----
>>Hi there,

>>rereading my oroginal posting i thought i should clear
>>some of the points in it:
>>- I am trying to implement a system of priviliges that
is
>>not covered by the DB-Role.  What i need is to grant
>>certain users access to certain members of an entity.
>>- I have the DB-Schema for this with a table for users,
>>Roles, entities, and a cross-table that provides for the
>>allocation of a user in a certain role to a certain
>entity.

>>What i am looking for is a mean of proofing the given
>user
>>(not the logged on user) against the privlege (a
standard
>>DB-Role) he is supposed to possess.

>>In case this is not possible, no damage will be done as
>>users without the rights privileges willnot be able to
>>execute the SPs any way, it just wouldn't be nice not to
>>be able to proof this before a row is inserted in the
>>allocation table

>>Greetings

>>Nasser Brake
>>.

>.



Sat, 20 Nov 2004 06:39:47 GMT
 Membership of a User in a Role

Hello ,

I would recommend you to redirect this question  to SQLServer:Security
newsgroup.

Thanks,

Vikrant Dalwale

Microsoft SQL Server Support Professional

This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.? 2002 Microsoft Corporation. All rights
reserved.

Additional support can be obtained at http://support.microsoft.com

Are you secure?  For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.

--------------------

Quote:
>Content-Class: urn:content-classes:message


>Subject: Membership of a User in a Role
>Date: Mon, 3 Jun 2002 09:52:36 -0700
>Lines: 64

>MIME-Version: 1.0
>Content-Type: text/plain;
>    charset="iso-8859-1"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>Thread-Index: AcILHxAApNxen8xISHCiwgBDCCxVSA==
>Newsgroups: microsoft.public.sqlserver.programming
>NNTP-Posting-Host: TKMSFTNGXA03 10.201.232.162
>Path: cpmsftngxa08!cpmsftngxa07
>Xref: cpmsftngxa08 microsoft.public.sqlserver.programming:263198
>X-Tomcat-NG: microsoft.public.sqlserver.programming

>Hi there,
>in an SP i am trying to figure a way of finding out
>wheather a User that i receive as variable is a member in
>a role (not a server role).  The Only means i found so far
>where joins between the sysusers and sysmembers.

>Here is an example:

>The following script would create the User DB_CASU_OR
>/*
>BEGIN
>*/
>if not exists (select * from master.dbo.syslogins where
>loginname = N'DB_CASU_OR')
>BEGIN











>END
>/*
>END
>*/
>GO

>The follwoing Script would create the Role
>/*
>BEGIN
>*/
>if not exists (select * from dbo.sysusers where name =
>N'ROL_DB_CASU_OR' and uid > 16399)
>    EXEC sp_addrole N'ROL_DB_CASU_OR'
>/*
>END
>*/
>GO

>The following script would add the user to the Role
>/*
>BEGIN
>*/
>exec sp_addrolemember N'ROL_DB_CASU_OR', N'DB_CASU_OR'
>/*
>END
>*/
>GO

>SO my question is: if DB_CASU_OR (or an NT-User that is a
>member of this role) is logged on, how can i proof that
>this user is a member of this role.....

>Thanks in advance!!!!

>Greetings
>N. Brake



Mon, 22 Nov 2004 06:02:36 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. Membership of a user in a role

2. Reading membership of all roles for a user

3. Database role membership for replication

4. Determining Database Role Membership

5. Determining Role membership from System_User

6. Minimal membership of fixed server roles

7. Checking role memberships programmatically

8. Determining Membership in Role Chains

9. User with server role / database role

10. Do built-in roles override user-defined roles?

11. user defined role and public role

12. Query LDAP user group membership frmo SQL Server


 
Powered by phpBB® Forum Software