Re: Determining Membership in Role Chains

From: BP Margolin (bpmargo@attglobal.net)
Date: 08/24/02 

From: "BP Margolin" <bpmargo@attglobal.net>
Date: Sat, 24 Aug 2002 15:12:57 -0400

Geoffrey,

I just tested what you said, on SQL Server 2000 w/SP2, and I seem to be
getting different results than you.

I created two roles: Salesmen and SalesManager.
I added a SQL Server login ("test") to the Salesmen role.
I added the Salesmen role as a member to the SalesManager role.
When I login using the "test" login and query Is_Member for either the
Salesmen or the SalesManager role, I get a 1.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.

"Geoffrey Barnes" <spambuster@earthlink.net> wrote in message
news:xTK99.4153$ld4.391136@newsread2.prod.itd.earthlink.net...
> The security model for one of my client's database has a lot of roles that
> are members of other roles. For example, they have two roles called
> Salesmen and a SalesManager, with the SalesManager role being a member of
> the Salesmen. That way, all SalesManager's can do everything that the
> Salesmen can do, along with whatever additional permissions are granted
> directly to the SalesManager role.
>
> I thought this model was fairly clever until I tried to apply IS_MEMBER to
> it. IS_MEMBER (and sp_helprolemember, for that matter) only report direct
> role membership for the current user. In other words, when a SalesManager
> is logged on, IS_MEMBER('SalesManger') will return a 1, but
> IS_MEMBER('Salesmen') will not, even though all SalesManger's are members
of
> Salesmen.
>
> I know I could get around this by writing my own procedure and using
cursors
> to step through the sys tables, but I am wondering whether there is
already
> a ready-made solution out there.
>
> Thanks in advance.
>
>

Relevant Pages

  • Re: cannot acees two databases as owner
    ... it does not matter you are a member of the ... the sysadmin fixed server role or if there is no any other Login which is ... group if it's a domain) and you can login to your SQL Server with, ...
    (microsoft.public.sqlserver.setup)
  • Re: SQL Login
    ... The sa login account is a member of that role as well. ... Dejan Sarka, SQL Server MVP ... > But how do we add the User X to the sysadmin / ...
    (microsoft.public.sqlserver.security)
  • Re: Keeping User From Deleting Table Records
    ... Hugo - Your comment about the user being a member of an admin group did the ... - I have created a new Windows user login in Security/Logins that is ... SQL Server to obtain default settings for the additional configuration ...
    (microsoft.public.sqlserver.security)
  • Re: Other way of Changing Password if does not have Server Role
    ... A user can change his password without being a member of the sysadmin role. ... You just have to login as the user and then omit the 3rd parameter ... Dejan Sarka, SQL Server MVP ...
    (microsoft.public.sqlserver.security)
  • Re: cannot acees two databases as owner
    ... You are going to be able to login to your SQL Server as a sysadmin as long as you have Builtin\Administrators Login exists Otherwise, it does not matter you are a member of the Administrators group on your Windows or not... ... You need to be a member of the sysadmin fixed server role on SQL Server to be able to manage it as you wish. ... Well, if you do not know any other Login's password which is a member of the sysadmin fixed server role or if there is no any other Login which is a member of the sysadmin and you can login to your SQL Server with, then it means you locked yourself out of your SQL Server box. ...
    (microsoft.public.sqlserver.setup)