locked
Database Role Hierarchies RRS feed

  • Question

  • Hi All,

    Based on 2005/2008 SQL Server.

    In my testing, I want to create role based hierarchies for multiple schemas, and find for some reason members get added to parent roles when they are part of child roles.

    Here's what I am doing (schema names have been changed for simplicity)...

    I have a schema which would contain a collection of tables that relate to a application let's call this payroll for now.

    I have a second schema which would contain a collection of tables that relate to another application let's call this HR for now.

    If I wanted to control write access to both of these schemas, I would like to create a "write" (owned by dbo) database role where I would add the users, and then add the role to specific database roles that is for the schemas mentioned above.

    Payroll Schema tables would have insert granted to database role named "payroll_write" and so on for HR ("hr_write").

    You would then make database role "write" member of "payroll_write" and "hr_write", so they obtain the insert permissions of the group.

    Now why does a member of the role "write" get added to the "payroll_write" and "hr_write" when it's part of the "write" role, this appears in the list of members within the role, I can see the database role "write" and the members of that role in the list of members under "payroll_write" and "hr_write", so when you remove "write" from "payroll_write" the members are left in the "payroll_write".

    I would preferably like to implement something like active directory does.

    Kind Regards,

    Paul
    Wednesday, March 23, 2011 4:36 PM

Answers

  • The GUI (SSMS) is playing a trick with you. Apparently it thinks it is a good ides to list the "effective" membership, not the explicitly stated memobership. See my (cleaned up) scrppt below. The sys.database_role_members view clearly states that rwPKettley is only member of the Write role. And after you remove rwPKettley from the Write role, you will find that rwPKettley is not listed at all...

    NOTE: The script drops the database x and the login rwPKettley!!!!!

    USe master
    GO
    IF SUSER_SID('rwPKettley') IS NOT NULL DROP LOGIN rwPKettley
    GO
    CREATE LOGIN rwPKettley WITH PASSWORD = 'lhfdh.wqe3.'
    IF DB_ID('x') IS NOT NULL DROP DATABASE x
    GO
    CREATE DATABASE x
    GO
    USE x
    GO
    
    
    CREATE ROLE HR_Write AUTHORIZATION dbo
    CREATE ROLE Payroll_Write AUTHORIZATION dbo
    CREATE ROLE Write AUTHORIZATION dbo
    
    CREATE USER rwPKettley FOR LOGIN rwPKettley WITH DEFAULT_SCHEMA=dbo
    GO
    CREATE SCHEMA HumanResources AUTHORIZATION dbo
    GO
    CREATE SCHEMA Payroll AUTHORIZATION dbo
    GO
    
    CREATE TABLE HumanResources.Employee(
     EmployeeID int IDENTITY(1,1) NOT NULL PRIMARY KEY
    ,Title nvarchar(50) NOT NULL,
    )
    CREATE TABLE Payroll.Slips(
     SlipID int IDENTITY(1,1) NOT NULL
    ,EmployeeID int NOT NULL REFERENCES HumanResources.Employee (EmployeeID)
    ,Amount numeric(18, 3) NOT NULL
    )
    
    EXEC sys.sp_addrolemember @rolename=N'HR_Write', @membername=N'Write'
    EXEC sys.sp_addrolemember @rolename=N'Payroll_Write', @membername=N'Write'
    EXEC sys.sp_addrolemember @rolename=N'Write', @membername=N'rwPKettley'
    
    GRANT SELECT ON HumanResources.Employee TO HR_Write
    GRANT SELECT ON Payroll.Slips TO Payroll_Write
    
    --rwPKettley is only member of the Write role
    SELECT USER_NAME(role_principal_id) AS roleName, USER_NAME(member_principal_id) AS member_name
    FROM sys.database_role_members
    
    EXEC sp_droprolemember 'Write', 'rwPKettley'
    
    --rwPKettley is not listed at all
    SELECT USER_NAME(role_principal_id) AS roleName, USER_NAME(member_principal_id) AS member_name
    FROM sys.database_role_members
    

    Tibor Karaszi, SQL Server MVP | web | blog
    • Marked as answer by P.Kettley Thursday, March 24, 2011 2:00 PM
    Thursday, March 24, 2011 8:38 AM

All replies

  • Paul,

    I think I understand what you are descibing, and I agree it sounds fishy. However, I think that most of us would need to play with this to try to sort your what you are seeing (and see if we can repro it or not). Also, there is always the risk that that GIU is doing something strange here. So, my suggestion is that you post a full repro here, which we can use. Thay would include all create commands (login, user, schema, table, role) and the GRANT commands. Probably not more than a handful, I can imagine. (We can do this ourselves, of course, but haing this available reduces the time for us before we can try to get o the bottom of the problem). Remember you can scrip these things in SSMS.


    Tibor Karaszi, SQL Server MVP | web | blog
    Wednesday, March 23, 2011 6:46 PM
  • Hi Tibor,

    Please find below the code I was using to test grouping database roles, the tables are made up and does not represent an actual system. You should notice that the rwPKettley user is part of the Write database role, that database role is part of the Payroll_Write and HR_Write role.

    What I believe I should see is the write role appear only in the members list of HR_Write or Payroll_Write, however I see all the users in the Write role within the members list along with the Write role for database roles Payroll_Write and HR_Write. You should see this going to the properties window of Payroll_Write and HR_Write.

     

    USE [TEMPODS]
    
    GO
    
    /****** Object: ForeignKey [FK_Slips_Employee] Script Date: 03/23/2011 19:04:58 ******/
    
    IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[Payroll].[FK_Slips_Employee]') AND parent_object_id = OBJECT_ID(N'[Payroll].[Slips]'))
    
    ALTER TABLE [Payroll].[Slips] DROP CONSTRAINT [FK_Slips_Employee]
    
    GO
    
    /****** Object: Table [Payroll].[Slips] Script Date: 03/23/2011 19:04:58 ******/
    
    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Payroll].[Slips]') AND type in (N'U'))
    
    DROP TABLE [Payroll].[Slips]
    
    GO
    
    /****** Object: Table [HumanResources].[Employee] Script Date: 03/23/2011 19:04:58 ******/
    
    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[HumanResources].[Employee]') AND type in (N'U'))
    
    DROP TABLE [HumanResources].[Employee]
    
    GO
    
    /****** Object: Schema [HumanResources] Script Date: 03/23/2011 19:04:58 ******/
    
    IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'HumanResources')
    
    DROP SCHEMA [HumanResources]
    
    GO
    
    /****** Object: Schema [Payroll] Script Date: 03/23/2011 19:04:58 ******/
    
    IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'Payroll')
    
    DROP SCHEMA [Payroll]
    
    GO
    
    /****** Object: User [rwPKettley] Script Date: 03/23/2011 19:04:58 ******/
    
    IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'rwPKettley')
    
    DROP USER [rwPKettley]
    
    GO
    
    /****** Object: Role [HR_Write] Script Date: 03/23/2011 19:04:58 ******/
    
    IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'HR_Write' AND type = 'R')
    
    DROP ROLE [HR_Write]
    
    GO
    
    /****** Object: Role [Payroll_Write] Script Date: 03/23/2011 19:04:58 ******/
    
    IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'Payroll_Write' AND type = 'R')
    
    DROP ROLE [Payroll_Write]
    
    GO
    
    /****** Object: Role [Write] Script Date: 03/23/2011 19:04:58 ******/
    
    IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'Write' AND type = 'R')
    
    DROP ROLE [Write]
    
    GO
    
    /****** Object: Role [HR_Write] Script Date: 03/23/2011 19:04:58 ******/
    
    IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'HR_Write' AND type = 'R')
    
    CREATE ROLE [HR_Write] AUTHORIZATION [dbo]
    
    GO
    
    /****** Object: Role [Payroll_Write] Script Date: 03/23/2011 19:04:58 ******/
    
    IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'Payroll_Write' AND type = 'R')
    
    CREATE ROLE [Payroll_Write] AUTHORIZATION [dbo]
    
    GO
    
    /****** Object: Role [Write] Script Date: 03/23/2011 19:04:58 ******/
    
    IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'Write' AND type = 'R')
    
    CREATE ROLE [Write] AUTHORIZATION [dbo]
    
    GO
    
    /****** Object: User [rwPKettley] Script Date: 03/23/2011 19:04:58 ******/
    
    IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'rwPKettley')
    
    CREATE USER [rwPKettley] FOR LOGIN [rwPKettley] WITH DEFAULT_SCHEMA=[dbo]
    
    GO
    
    /****** Object: Schema [HumanResources] Script Date: 03/23/2011 19:04:58 ******/
    
    IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'HumanResources')
    
    EXEC sys.sp_executesql N'CREATE SCHEMA [HumanResources] AUTHORIZATION [dbo]'
    
    GO
    
    /****** Object: Schema [Payroll] Script Date: 03/23/2011 19:04:58 ******/
    
    IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'Payroll')
    
    EXEC sys.sp_executesql N'CREATE SCHEMA [Payroll] AUTHORIZATION [dbo]'
    
    GO
    
    /****** Object: Table [HumanResources].[Employee] Script Date: 03/23/2011 19:04:58 ******/
    
    SET ANSI_NULLS ON
    
    GO
    
    SET QUOTED_IDENTIFIER ON
    
    GO
    
    IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[HumanResources].[Employee]') AND type in (N'U'))
    
    BEGIN
    
    CREATE TABLE [HumanResources].[Employee](
    
    	[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
    
    	[Title] [nvarchar](50) NOT NULL,
    
     CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED 
    
    (
    
    	[EmployeeID] ASC
    
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    
    ) ON [PRIMARY]
    
    END
    
    GO
    
    /****** Object: Table [Payroll].[Slips] Script Date: 03/23/2011 19:04:58 ******/
    
    SET ANSI_NULLS ON
    
    GO
    
    SET QUOTED_IDENTIFIER ON
    
    GO
    
    IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Payroll].[Slips]') AND type in (N'U'))
    
    BEGIN
    
    CREATE TABLE [Payroll].[Slips](
    
    	[SlipID] [int] IDENTITY(1,1) NOT NULL,
    
    	[EmployeeID] [int] NOT NULL,
    
    	[Amount] [numeric](18, 3) NOT NULL
    
    ) ON [PRIMARY]
    
    END
    
    GO
    
    /****** Object: ForeignKey [FK_Slips_Employee] Script Date: 03/23/2011 19:04:58 ******/
    
    IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[Payroll].[FK_Slips_Employee]') AND parent_object_id = OBJECT_ID(N'[Payroll].[Slips]'))
    
    ALTER TABLE [Payroll].[Slips] WITH CHECK ADD CONSTRAINT [FK_Slips_Employee] FOREIGN KEY([EmployeeID])
    
    REFERENCES [HumanResources].[Employee] ([EmployeeID])
    
    GO
    
    ALTER TABLE [Payroll].[Slips] CHECK CONSTRAINT [FK_Slips_Employee]
    
    GO
    
    
    EXEC sys.sp_addrolemember @rolename=N'HR_Write', @membername=N'Write'
    
    GO
    
    
    EXEC sys.sp_addrolemember @rolename=N'Payroll_Write', @membername=N'Write'
    
    GO
    
    EXEC sys.sp_addrolemember @rolename=N'Write', @membername=N'rwPKettley'
    
    GO
    
    GRANT DELETE ON [HumanResources].[Employee] TO [HR_Write]
    
    GO
    
    GRANT INSERT ON [HumanResources].[Employee] TO [HR_Write]
    
    GO
    
    GRANT SELECT ON [HumanResources].[Employee] TO [HR_Write]
    
    GO
    
    GRANT UPDATE ON [HumanResources].[Employee] TO [HR_Write]
    
    GO
    
    GRANT DELETE ON [Payroll].[Slips] TO [Payroll_Write]
    
    GO
    
    GRANT INSERT ON [Payroll].[Slips] TO [Payroll_Write]
    
    GO
    
    GRANT SELECT ON [Payroll].[Slips] TO [Payroll_Write]
    
    GO
    
    GRANT UPDATE ON [Payroll].[Slips] TO [Payroll_Write]
    
    GO
    
    
    
    

    Wednesday, March 23, 2011 7:13 PM
  • The GUI (SSMS) is playing a trick with you. Apparently it thinks it is a good ides to list the "effective" membership, not the explicitly stated memobership. See my (cleaned up) scrppt below. The sys.database_role_members view clearly states that rwPKettley is only member of the Write role. And after you remove rwPKettley from the Write role, you will find that rwPKettley is not listed at all...

    NOTE: The script drops the database x and the login rwPKettley!!!!!

    USe master
    GO
    IF SUSER_SID('rwPKettley') IS NOT NULL DROP LOGIN rwPKettley
    GO
    CREATE LOGIN rwPKettley WITH PASSWORD = 'lhfdh.wqe3.'
    IF DB_ID('x') IS NOT NULL DROP DATABASE x
    GO
    CREATE DATABASE x
    GO
    USE x
    GO
    
    
    CREATE ROLE HR_Write AUTHORIZATION dbo
    CREATE ROLE Payroll_Write AUTHORIZATION dbo
    CREATE ROLE Write AUTHORIZATION dbo
    
    CREATE USER rwPKettley FOR LOGIN rwPKettley WITH DEFAULT_SCHEMA=dbo
    GO
    CREATE SCHEMA HumanResources AUTHORIZATION dbo
    GO
    CREATE SCHEMA Payroll AUTHORIZATION dbo
    GO
    
    CREATE TABLE HumanResources.Employee(
     EmployeeID int IDENTITY(1,1) NOT NULL PRIMARY KEY
    ,Title nvarchar(50) NOT NULL,
    )
    CREATE TABLE Payroll.Slips(
     SlipID int IDENTITY(1,1) NOT NULL
    ,EmployeeID int NOT NULL REFERENCES HumanResources.Employee (EmployeeID)
    ,Amount numeric(18, 3) NOT NULL
    )
    
    EXEC sys.sp_addrolemember @rolename=N'HR_Write', @membername=N'Write'
    EXEC sys.sp_addrolemember @rolename=N'Payroll_Write', @membername=N'Write'
    EXEC sys.sp_addrolemember @rolename=N'Write', @membername=N'rwPKettley'
    
    GRANT SELECT ON HumanResources.Employee TO HR_Write
    GRANT SELECT ON Payroll.Slips TO Payroll_Write
    
    --rwPKettley is only member of the Write role
    SELECT USER_NAME(role_principal_id) AS roleName, USER_NAME(member_principal_id) AS member_name
    FROM sys.database_role_members
    
    EXEC sp_droprolemember 'Write', 'rwPKettley'
    
    --rwPKettley is not listed at all
    SELECT USER_NAME(role_principal_id) AS roleName, USER_NAME(member_principal_id) AS member_name
    FROM sys.database_role_members
    

    Tibor Karaszi, SQL Server MVP | web | blog
    • Marked as answer by P.Kettley Thursday, March 24, 2011 2:00 PM
    Thursday, March 24, 2011 8:38 AM
  • Hi Tibor,

     

    That all sounds good, just one thing if you open up the GUI for the properties of a role, the user will appear and stay there.

     

    It's almost like the GUI does an update to the members list fo that role when you press ok, have you found that at all? For example open up the GUI click OK when you see rwPKettley, REMOVE rwPKettley from Write role and then go back to the other role you were just in and you will see rwPKettley still in the list.

     

    Kind Regards,

     

    Paul

     

    Thursday, March 24, 2011 8:48 AM
  • Hi Paul,

    Probably just a refresh thing in the GUI. You can try refresh (right-click, ...) or even disconnect Object Explorer and connect again. that should empty the cache in SSMS and make sure that you don't see any strange leftovers from prior operations.


    Tibor Karaszi, SQL Server MVP | web | blog
    Thursday, March 24, 2011 3:19 PM