Retrieving Windows SharePoint Services user access rights from the database

I case you ever need to know which access rights a given user has on your Windows SharePoint Services (WSS) site, you may find the following SQL scripts useful.


This SQL script (simply execute it against the database that has your WSS content) accepts either an email address (@search_email = 'John.Doe@acme.org') or the Windows login name (@search_account='ACME\JohnD').


If the given user is found, it will return three tables: The data of the user as WSS sees it, the groups to which the user belongs and finally to which sites the user has access to.




DECLARE @search_email varchar(100)
DECLARE @search_account varchar(100)

SET @search_email='John.Doe@acme.org
--SET @search_account='ACME\JohnD'


DECLARE @userid int

-- Retrieve user ID
IF (@search_account is null) BEGIN
select @userid=tp_ID from userinfo where tp_Email = @search_email
END ELSE BEGIN
select @userid=tp_ID from userinfo where tp_Login = @search_account
END

-- Show found user
SELECT '' as 'User Info',
tp_ID,tp_Login,tp_Title from userinfo where tp_ID=@userid

-- Show group membership
SELECT '' as 'Group membership',
ID,Title,Description from groups where id in
( SELECT groupid from groupmembership where memberid=@userid )
order by Title

-- Show Access rights
SELECT '' as 'Access rights',
Title, FulLURL from Webs where id in
( SELECT WebId FROM WebMembers WHERE (UserId = @userid) )
order by Title
GO