Re: Membership of DB Role when User Gains Access via Active Direct
- From: "SimonDev" <SimonDev@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 14 Feb 2006 13:01:26 -0800
Hi Antonio
I found the IS_MEMBER function after I posted the question. It sort of
works. I say sort of because it returns membership based on the CURRENT_USER
rather than the SYSTEM_USER. For most people it will work but it doesn't
work for my login. I assume this is because I am a sysadmin.
I want to test whether someone is a member of the role 'OutletOwners'. I am
a member of the role through my login DOMAIN\simonlogin'. However my
CURRENT_USER name is 'dbo'. So IS_MEMBER thinks I am not a member of
'OutletOwners' because 'dbo' is not a member. I got around this by querying
the sysmembers table directly for users like me.
IS_MEMBER also doesn't work if the AD Group which has rights to the database
has a different name in the database to its Windows Group name (eg if grant
access to the DB via " EXEC sp_grantdbaccess 'DOMAIN\ADGroupName', 'NameInDB'
" then make 'NameInDB' a member of the role you want to test). I haven't
found a way around this except to put a warning in the documentation to only
add AD groups to the DB using their Windows names, not a new DB user name.
If it is any use, here is the complete code for the user-defined function I
wrote:
--=== CODE BEGINS ====
CREATE FUNCTION dbo.usf_IsRoleMember (
@pstrDBRole VARCHAR(30)
)
RETURNS SMALLINT AS
BEGIN
DECLARE @strUser VARCHAR(50),
@intUserID SMALLINT,
@intRoleID SMALLINT,
@intResult SMALLINT
SET @pstrDBRole = LTRIM(RTRIM(COALESCE(@pstrDBRole, '')))
SET @intResult = 0
-- ROLE EXISTS IN CURRENT DB.
IF EXISTS(SELECT * FROM sysusers WHERE name = @pstrDBRole)
BEGIN
-- Returns true if users' login is a member of a windows group that
-- is in turn a member of the specified database role.
SET @intResult = COALESCE(IS_MEMBER(@pstrDBRole), -1)
-- IS_MEMBER tests whether CURRENT_USER is a member of the role. Sometimes
-- a user may be a member of the role through their login while their
-- CURRENT_USER name is different from the login (eg CURRENT_USER may
-- be 'dbo'). In that case IS_MEMBER will indicate user is not a
-- member of the role.
-- DOUBLE CHECK WHETHER USER IS MEMBER OF ROLE.
IF @intResult = 0
BEGIN
-- NOTE: USER_ID with no argument will return the ID of the session user.
-- Normally this is the same as the id of the system user. Sometimes
-- they may differ, eg when user is a member of sysadmin. In that case
-- USER_ID() will return the ID of the database owner while
-- USER_ID(SYSTEM_USER) will return the ID of the user's login.
SET @intUserID = USER_ID(SYSTEM_USER)
SET @intRoleID = USER_ID(@pstrDBRole)
IF EXISTS(SELECT * FROM sysmembers WHERE groupuid = @intRoleID AND
memberuid = @intUserID)
SET @intResult = 1
END
END
-- ROLE DOES NOT EXIST IN CURRENT DB.
ELSE
SET @intResult = -1
RETURN @intResult
END
--=== CODE ENDS====
--
Cheers
Si
.
- References:
- Re: Membership of DB Role when User Gains Access via Active Directory
- From: Antonio Soto
- Re: Membership of DB Role when User Gains Access via Active Directory
- Prev by Date: Reporting Services RSExecRole
- Next by Date: Re: Who is using MSMQ?
- Previous by thread: Re: Membership of DB Role when User Gains Access via Active Directory
- Next by thread: Reporting Services RSExecRole
- Index(es):
Relevant Pages
|
|
