tag:blogger.com,1999:blog-7725030.post-34643174025495265792008-04-10T06:32:00.000-07:002008-04-30T06:40:24.778-07:00<span style="font-family: verdana;">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.</span><br /><br /><br /><span style="font-family: verdana;">This SQL script (simply execute it against the database that has your WSS content) accepts either an email address (</span><span style="font-style: italic; font-family: verdana;">@search_email </span><span style="font-family: verdana;">= 'John.Doe@acme.org') or the Windows login name (</span><span style="font-style: italic; font-family: verdana;">@search_account</span><span style="font-family: verdana;">='ACME\JohnD').<br /></span><br /><br /><span style="font-family: verdana;">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.</span><br /><br /><br /><br /><br />DECLARE @search_email varchar(100)<br />DECLARE @search_account varchar(100)<br /><br />SET @search_email='John.Doe@acme.org<br />--SET @search_account='ACME\JohnD'<br /><br /><br />DECLARE @userid int<br /><br />-- Retrieve user ID<br />IF (@search_account is null) BEGIN<br /> select @userid=tp_ID from userinfo where tp_Email = @search_email<br />END ELSE BEGIN<br /> select @userid=tp_ID from userinfo where tp_Login = @search_account<br />END<br /><br />-- Show found user<br />SELECT '' as 'User Info',<br /> tp_ID,tp_Login,tp_Title from userinfo where tp_ID=@userid<br /><br />-- Show group membership<br />SELECT '' as 'Group membership',<br /> ID,Title,Description from groups where id in<br /> ( SELECT groupid from groupmembership where memberid=@userid )<br /> order by Title<br /><br />-- Show Access rights<br />SELECT '' as 'Access rights',<br /> Title, FulLURL from Webs where id in<br /> ( SELECT WebId FROM WebMembers WHERE (UserId = @userid) )<br /> order by Title<br />GOTeX HeXhttp://www.blogger.com/profile/14268105680697143201noreply@blogger.com