I want to validate whether a user has been added to a particular database role. I cannot use the is_member function since it only works with the current user.
Try: EXEC sp_helprolemember 'role_name_here' In SQL 2005, you can also query from sys.database_role_members catalog view. -- Hope this helps. Dan Guzman SQL Server MVP http://weblogs.sqlteam.com/dang/ "Peter" wrote in message news:CC980BB7-8654-4F7C-84EB-0CB51E1F860C@microsoft.com... >I want to validate whether a user has been added to a particular database > role. I cannot use the is_member function since it only works with the > current user.
Hi Dan, I actually have already tried them. The sp_helprolemember will require me to insert the result into a temporary table and then verify. The sys.database_role_members view will need to join with sys.database_principals view. I wonder why there is no built-in security function for such basic operation. "Dan Guzman" wrote: > Try: > > EXEC sp_helprolemember 'role_name_here' > > In SQL 2005, you can also query from sys.database_role_members catalog view. > > > > -- > Hope this helps. > > Dan Guzman > SQL Server MVP > http://weblogs.sqlteam.com/dang/ > > "Peter" wrote in message > news:CC980BB7-8654-4F7C-84EB-0CB51E1F860C@microsoft.com... > >I want to validate whether a user has been added to a particular database > > role. I cannot use the is_member function since it only works with the > > current user. > >
Hi Dan, I just realize that using those views are more complicated since the user and the desired role may be related thru a hierarchy not a direct relationship. "Dan Guzman" wrote: > Try: > > EXEC sp_helprolemember 'role_name_here' > > In SQL 2005, you can also query from sys.database_role_members catalog view. > > > > -- > Hope this helps. > > Dan Guzman > SQL Server MVP > http://weblogs.sqlteam.com/dang/ > > "Peter" wrote in message > news:CC980BB7-8654-4F7C-84EB-0CB51E1F860C@microsoft.com... > >I want to validate whether a user has been added to a particular database > > role. I cannot use the is_member function since it only works with the > > current user. > >
> I actually have already tried them. The sp_helprolemember will require > me > to insert the result into a temporary table and then verify. > The sys.database_role_members view will need to join with > sys.database_principals view. I wonder why there is no built-in > security > function for such basic operation. If you are going to use a query rather than SSMS GUI, I don't see why the join is a big deal. In any case, you can avoid the JOIN by using other security functions: SELECT USER_NAME(rm.member_principal_id) AS UserName, USER_NAME(rm.role_principal_id) AS RoleName FROM sys.database_role_members rm WHERE rm.member_principal_id = USER_ID('some_user') AND rm.role_principal_id = USER_ID('some_role') -- Hope this helps. Dan Guzman SQL Server MVP http://weblogs.sqlteam.com/dang/
Hi, Peter. > I just realize that using those views are more complicated since the user > and the desired role may be related thru a hierarchy not a direct > relationship. The CTE below ought to identify indirect membership: WITH role_hierarchy AS ( SELECT rm.member_principal_id, rm.role_principal_id FROM sys.database_role_members rm WHERE rm.role_principal_id = USER_ID('role_name') UNION ALL SELECT rm.member_principal_id, rm.role_principal_id FROM sys.database_role_members rm JOIN role_hierarchy rh ON rh.member_principal_id = rm.role_principal_id ) SELECT 'is a member' FROM role_hierarchy rh WHERE USER_NAME(rh.member_principal_id) = 'user_name'; -- Hope this helps. Dan Guzman SQL Server MVP http://weblogs.sqlteam.com/dang/
Thanks Dan. Will try it. "Dan Guzman" wrote: > Hi, Peter. > > > I just realize that using those views are more complicated since the user > > and the desired role may be related thru a hierarchy not a direct > > relationship. > > The CTE below ought to identify indirect membership: > > WITH role_hierarchy > AS > ( > SELECT > rm.member_principal_id, > rm.role_principal_id > FROM sys.database_role_members rm > WHERE > rm.role_principal_id = USER_ID('role_name') > UNION ALL > SELECT > rm.member_principal_id, > rm.role_principal_id > FROM sys.database_role_members rm > JOIN role_hierarchy rh ON > rh.member_principal_id = rm.role_principal_id > ) > SELECT 'is a member' FROM role_hierarchy rh > WHERE USER_NAME(rh.member_principal_id) = 'user_name'; > > -- > Hope this helps. > > Dan Guzman > SQL Server MVP > http://weblogs.sqlteam.com/dang/ > >
Hi Dan, The CTE works. I have never used CTE before. I create the following user function temporarily to see how I can use the CTE in a function. I just have the parameter for the user but will add the parameter for the role later. Does it look like the correct way to implement the function with the CTE? Thanks. CREATE function [dbo].[check_role](@user varchar(100)) returns bit as begin declare @bexist bit select @bexist = 0; WITH role_hierarchy AS ( SELECT rm.member_principal_id, rm.role_principal_id FROM sys.database_role_members rm WHERE rm.role_principal_id = USER_ID('SQLAgentUserRole') UNION ALL SELECT rm.member_principal_id, rm.role_principal_id FROM sys.database_role_members rm JOIN role_hierarchy rh ON rh.member_principal_id = rm.role_principal_id ) SELECT @bexist = 1 FROM role_hierarchy rh WHERE USER_NAME(rh.member_principal_id) = @user return @bexist end "Dan Guzman" wrote: > Hi, Peter. > > > I just realize that using those views are more complicated since the user > > and the desired role may be related thru a hierarchy not a direct > > relationship. > > The CTE below ought to identify indirect membership: > > WITH role_hierarchy > AS > ( > SELECT > rm.member_principal_id, > rm.role_principal_id > FROM sys.database_role_members rm > WHERE > rm.role_principal_id = USER_ID('role_name') > UNION ALL > SELECT > rm.member_principal_id, > rm.role_principal_id > FROM sys.database_role_members rm > JOIN role_hierarchy rh ON > rh.member_principal_id = rm.role_principal_id > ) > SELECT 'is a member' FROM role_hierarchy rh > WHERE USER_NAME(rh.member_principal_id) = 'user_name'; > > -- > Hope this helps. > > Dan Guzman > SQL Server MVP > http://weblogs.sqlteam.com/dang/ > >
> Does it look like the correct way to implement the function with the CTE? Yes, I think your function will return the desired result. -- Hope this helps. Dan Guzman SQL Server MVP http://weblogs.sqlteam.com/dang/ "Peter" wrote in message news:E0B312D6-6BF8-41F8-9C64-78BA7CEE5B3E@microsoft.com... > Hi Dan, > > The CTE works. I have never used CTE before. I create the following > user > function temporarily to see how I can use the CTE in a function. I just > have > the parameter for the user but will add the parameter for the role later. > Does it look like the correct way to implement the function with the CTE? > Thanks. > > CREATE function [dbo].[check_role](@user varchar(100)) > returns bit > as begin > declare @bexist bit > select @bexist = 0; > WITH role_hierarchy > AS > ( > SELECT > rm.member_principal_id, > rm.role_principal_id > FROM sys.database_role_members rm > WHERE > rm.role_principal_id = USER_ID('SQLAgentUserRole') > UNION ALL > SELECT > rm.member_principal_id, > rm.role_principal_id > FROM sys.database_role_members rm > JOIN role_hierarchy rh ON > rh.member_principal_id = rm.role_principal_id > ) > SELECT @bexist = 1 FROM role_hierarchy rh > WHERE USER_NAME(rh.member_principal_id) = @user > return @bexist > end > > "Dan Guzman" wrote: > >> Hi, Peter. >> >> > I just realize that using those views are more complicated since the >> > user >> > and the desired role may be related thru a hierarchy not a direct >> > relationship. >> >> The CTE below ought to identify indirect membership: >> >> WITH role_hierarchy >> AS >> ( >> SELECT >> rm.member_principal_id, >> rm.role_principal_id >> FROM sys.database_role_members rm >> WHERE >> rm.role_principal_id = USER_ID('role_name') >> UNION ALL >> SELECT >> rm.member_principal_id, >> rm.role_principal_id >> FROM sys.database_role_members rm >> JOIN role_hierarchy rh ON >> rh.member_principal_id = rm.role_principal_id >> ) >> SELECT 'is a member' FROM role_hierarchy rh >> WHERE USER_NAME(rh.member_principal_id) = 'user_name'; >> >> -- >> Hope this helps. >> >> Dan Guzman >> SQL Server MVP >> http://weblogs.sqlteam.com/dang/ >> >>
Peter (Peter@discussions.microsoft.com) writes: > I want to validate whether a user has been added to a particular database > role. I cannot use the is_member function since it only works with the > current user. In additions to Dan's suggestion, you can: EXECUTE AS LOGIN = 'thatuser' go SELECT is_member('somerole') go REVERT I think this is better than running queries as Dan suggested, particularly in the case that user may be have role membership through a Windows domain. If you think that there should be a function to retrieve permissions or role membership with going through impersonation - which is impractical if you want to check a suite of users - submit a suggestion on http://connect.microsoft.com/SqlServer/Feedback. (I wouldn't be surprised if there is such a suggestion already.) -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
