which country user step here?

Tag Cloud

MOSS (47) SharePoint 2007 (37) SharePoint 2013 (23) SharePoint 2010 (22) MOSS admin (17) admin (17) PowerShell (16) developer (16) List (15) WSS (14) sql query (14) MOSS SP2 (13) end user (11) scripting (11) wss V3 (11) Moss issue (8) search (8) permission (7) sql (7) Service Pack (6) database (6) reportadmin (6) workflow (6) Excel (5) client object model (5) CU (4) Client Code (4) Command (4) Cumulative Updates (4) Patch (4) SharePoint designer (4) stsadm (4) ASP.NET (3) Content Database (3) Groove (3) Host Named Site Collections (HNSC) (3) IIS (3) RBS (3) Tutorial (3) alert (3) batch file (3) codeplex (3) error (3) incomming email (3) restore (3) upload (3) Caching (2) Folder (2) Index (2) Internet (2) News (2) People Picker (2) Share Document (2) View (2) Web Development with ASP.NET (2) add user (2) authentication (2) coding (2) column (2) deploy solution (2) domain (2) download (2) enumsites (2) exam (2) export (2) issue (2) june CU (2) mySites (2) network (2) office 365 (2) orphan site (2) performance (2) profile (2) project server (2) query (2) server admin (2) theme (2) timer job (2) training (2) user porfile (2) web master (2) web.config (2) wsp (2) 70-346 (1) 70-630 (1) AAM (1) Anonymous (1) Approval (1) Cerificate (1) Consultants (1) Content Deployment (1) Content Type (1) DOS (1) Document Library (1) Drive Sapce (1) Excel Services (1) Export to Excel (1) Feature (1) GAC (1) Get-SPContentDatabase (1) Get-WmiObject (1) HTML calculated column (1) ISA2006 (1) IT Knowledge (1) ITIL (1) Install (1) Link (1) MCTS (1) Macro (1) Migration (1) My Site Cleanup Job (1) My Sites (1) NLBS (1) Nintex (1) Office (1) Open with Explorer (1) ROIScan.vbs (1) Reporting Services (1) SPDisposeCheck.exe (1) SQL Instance name (1) SSRS (1) SharePoint farm (1) Shared Services Administration (1) Site Collection Owner (1) Site template (1) Steelhead (1) URLSCAN (1) VLOOKUP (1) WSS SP2 (1) XCOPY (1) admi (1) app (1) application pool (1) aspx (1) audit (1) availabilty (1) backup (1) binding (1) blob (1) branding sharepoint (1) cache (1) calendar (1) connection (1) copy file (1) counter (1) crawl (1) custom list (1) event (1) excel 2013 (1) facebook (1) filter (1) fun (1) group (1) iis log (1) import (1) import list (1) improment (1) interview (1) keberos (1) load balance (1) log in (1) metada (1) migrate (1) mossrap (1) onedrive for business (1) operation (1) process (1) publishing feature (1) resource (1) security (1) send email (1) size (1) sps2003 (1) sql201 (1) sql2012 (1) sub sites (1) system (1) table (1) task list (1) today date (1) vbs (1) video (1) web part (1) widget (1) windows 2008 (1) windows 2012 R2 (1) windows Azura (1) windows account (1) windows2012 (1) wmi (1)

Tuesday, December 14, 2010

querying membership data

Found one very usefull query from internet :

http://johnlivingstontech.blogspot.com/2009/11/sharepoint-2007-five-very-helpful-yet.html

thanks for the creator to share this!!  Below is the SQL query

1. Select Groups By URL

USE [WSS_Content]

CREATE PROCEDURE [dbo].[usp_SelectGroupsByUrl]

@url NVARCHAR(255)

AS

BEGIN

SELECT Groups.ID AS "GroupID", Groups.Title AS "GroupTitle", Webs.Title AS WebTitle, Webs.FullURL AS WebURL, Roles.Title AS "RoleTitle"

FROM RoleAssignment WITH (NOLOCK)

INNER JOIN Roles WITH (NOLOCK) ON Roles.SiteId = RoleAssignment.SiteId

AND Roles.RoleId = RoleAssignment.RoleId

INNER JOIN Groups WITH (NOLOCK) ON Groups.SiteId = RoleAssignment.SiteId

AND Groups.ID = RoleAssignment.PrincipalId

INNER JOIN Sites WITH (NOLOCK) ON Sites.Id = RoleAssignment.SiteId

INNER JOIN Perms WITH (NOLOCK) ON Perms.SiteId = RoleAssignment.SiteId

AND Perms.ScopeId = RoleAssignment.ScopeId

INNER JOIN Webs WITH (NOLOCK) ON Webs.Id = Perms.WebId

WHERE ((Webs.FullUrl = @url) OR (@url IS NULL))

ORDER BY

Groups.Title, Webs.Title, Perms.ScopeUrl

END

Sample Query

EXEC usp_SelectGroupsByUrl 'Sites/HR/Pay'

Results

GroupID

GroupTitle

WebTitle

WebURL

RoleTitle

71

Manager

Accounting

Sites/HR/PAY

View Only

74

Acct Sr Manager

Accounting

Sites/HR/PAY

Full Control

75

Member

Accounting

Sites/HR/PAY

View Only

76

Viewer

Accounting

Sites/HR/PAY

View Only

 

 

 

2. Select Groups By URL and User

USE [WSS_Content]

CREATE PROCEDURE [dbo].[usp_SelectGroupsByUrlUser]

@login NVARCHAR(255),

@url NVARCHAR(255)

AS

BEGIN

SELECT G.ID AS "GroupID", G.Title AS "GroupTitle", W.Title AS WebTitle, W.FullURL AS WebURL, R.Title AS "RoleTitle"

FROM RoleAssignment AS RA WITH (NOLOCK)

INNER JOIN Roles AS R WITH (NOLOCK) ON R.SiteId = RA.SiteId

AND R.RoleId = RA.RoleId

INNER JOIN Groups AS G WITH (NOLOCK) ON G.SiteId = RA.SiteId

AND G.ID = RA.PrincipalId

INNER JOIN Sites AS S WITH (NOLOCK) ON S.Id = RA.SiteId

INNER JOIN Perms AS P WITH (NOLOCK) ON P.SiteId = RA.SiteId

AND P.ScopeId = RA.ScopeId

INNER JOIN Webs AS W WITH (NOLOCK) ON W.Id = P.WebId

WHERE ((W.FullUrl = @url) OR (@url IS NULL))

AND G.ID IN

(

SELECT Groups.ID FROM GroupMemberShip WITH (NOLOCK)

INNER JOIN Groups ON GroupMembership.GroupID = Groups.ID

INNER JOIN UserInfo ON GroupMembership.MemberID = UserInfo.tp_ID

WHERE ((tp_Login = @login) OR (@login IS NULL))

)

ORDER BY

G.Title, W.Title, P.ScopeUrl

END

Sample Query

EXEC usp_SelectGroupsByUrlUser 'acmedomain\jdoe', 'Sites/HR/Pay'

Results

GroupID

GroupTitle

WebTitle

WebURL

RoleTitle

74

Sr Manager

Accounting

Sites/HR/PAY

Full Control

 

 

 

3. Select Groups By User Login

USE [WSS_Content]

CREATE PROCEDURE [dbo].[usp_SelectGroupsByUserLogin]

@login NVARCHAR(255)

AS

BEGIN

SELECT DISTINCT Groups.ID AS "GroupID", Groups.Title AS "GroupTitle"

FROM GroupMemberShip WITH (NOLOCK)

INNER JOIN Groups WITH (NOLOCK) ON GroupMembership.GroupID = Groups.ID

INNER JOIN UserInfo WITH (NOLOCK) ON GroupMembership.MemberID = UserInfo.tp_ID

WHERE ((tp_Login = @login) OR (@login IS NULL))

ORDER BY Groups.Title

END

Sample Query

EXEC usp_SelectGroupsByUserLogin 'acmedomain\jdoe'

Results

GroupID

GroupTitle

74

Sr Manager

52

Corporate Viewer

14

HR Team Member

4. Select Users By Group

USE [WSS_Content]

CREATE PROCEDURE [dbo].[usp_SelectUsersByGroup]

@groupTitle NVARCHAR(255)

AS

BEGIN

SELECT dbo.UserInfo.tp_ID AS UserID, dbo.UserInfo.tp_Title AS UserTitle, dbo.UserInfo.tp_Login AS UserLogin, dbo.UserInfo.tp_Email AS UserEmail, dbo.Groups.ID AS GroupsID, dbo.Groups.Title AS GroupsTitle

FROM UserInfo WITH (NOLOCK)

INNER JOIN GroupMembership WITH (NOLOCK) ON UserInfo.tp_ID = GroupMembership.MemberID

INNER JOIN Groups WITH (NOLOCK) ON GroupMembership.GroupID = Groups.ID

WHERE ((dbo.Groups.Title = @groupTitle) OR (@groupTitle IS NULL))

ORDER by dbo.UserInfo.tp_Login

END

Sample Query

EXEC usp_SelectUsersByGroup 'HR Team Member'

Results

UserID

UserTitle

UserLogin

UserEmail

GroupsID

GroupsTitle

23

Doe, John

acmedomain\jdoe

jdoe@acmedomain.com

14

HR Team Member

20

Doe, Jane

acmedomain\jadoe

jadoe@acmedomain.com

14

HR Team Member

2

Smith, Bob

acmedomain\bsmith

bsmith@acmedomain.com

14

HR Team Member

24

Yamamoto, Kazue

acmedomain\kyamamoto

kyamamoto@acmedomain.com

14

HR Team Member

46

Nakamura, Ichiro

acmedomain\inakamura

inakamura@acmedomain.com

14

HR Team Member

5 Select Users By Groups

USE [WSS_Content]

CREATE PROCEDURE [dbo].[usp_SelectUsersByGroups]

@groupTitles VARCHAR(1000)

AS

BEGIN

DECLARE @idoc INT

EXEC sp_xml_preparedocument @idoc OUTPUT, @groupTitles

SELECT dbo.UserInfo.tp_ID AS UserID, dbo.UserInfo.tp_Title AS UserTitle, dbo.UserInfo.tp_Login AS UserLogin, dbo.UserInfo.tp_Email AS UserEmail, Groups.Title AS GroupTitle

FROM UserInfo WITH (NOLOCK)

INNER JOIN GroupMembership WITH (NOLOCK) ON UserInfo.tp_ID = GroupMembership.MemberID

INNER JOIN Groups WITH (NOLOCK) ON GroupMembership.GroupID = Groups.ID

JOIN OPENXML(@idoc, '/Root/Group', 0) WITH (Name VARCHAR(100)) AS g

ON dbo.Groups.Title = g.Name

ORDER by dbo.Groups.Title, dbo.UserInfo.tp_Title

END

Sample Query

EXEC usp_SelectUsersByGroups '<Root><Group Name="HR Team Member"></Group><Group Name="Acct Sr Managers"></Group></Root>'

Results

UserID

UserTitle

UserLogin

UserEmail

GroupsID

GroupsTitle

23

Doe, John

acmedomain\jdoe

jdoe@acmedomain.com

14

HR Team Member

20

Doe, Jane

acmedomain\jadoe

jadoe@acmedomain.com

14

HR Team Member

2

Smith, Bob

acmedomain\bsmith

bsmith@acmedomain.com

14

HR Team Member

24

Yamamoto, Kazue

acmedomain\kyamamoto

kyamamoto@acmedomain.com

14

HR Team Member

46

Nakamura, Ichiro

acmedomain\inakamura

inakamura@acmedomain.com

14

HR Team Member

7

Horowitz, Rick

acmedomain\rhorowitz

rhorowitz@acmedomain.com

15

Acct Sr Managers

3

Davis, Jiro

acmedomain\jdavis

jdavis@acmedomain.com

15

Acct Sr Managers

user's list of web sites in SharePoint : SharePoint

SELECT DISTINCT w.Title, r.WebId, r.Title AS accessLevel, ui.tp_Login,
ui.tp_DomainGroup, ui.tp_Title, w.FullUrl, w.AlternateCSSUrl,
p.ScopeUrl
FROM RoleAssignment AS ra INNER JOIN
Roles AS r ON r.SiteId = ra.SiteId AND r.RoleId
= ra.RoleId INNER JOIN
UserInfo AS ui ON ui.tp_SiteID = ra.SiteId AND
ui.tp_ID = ra.PrincipalId INNER JOIN
Sites AS s ON s.Id = ra.SiteId INNER JOIN
Perms AS p ON p.SiteId = ra.SiteId AND p.ScopeId
= ra.ScopeId INNER JOIN
Webs AS w ON w.Id = p.WebId

 

copy from Forum

Discovering all workflows in SharePoint farm

I got a question from user to identify all workflow in my Sharepoint environment. I tried to identify some out of box option but could not find one. After that I have to put my DB cap on and write query on backend content database of MOSS farm to get list all sites and sub containers which are having active workflow running in them. Here is the query :


select distinct(w.fullurl) as SiteName, L.tp_Title as ListName from webs w,workflow wf , Alllists L where w.id=wf.webid and w.siteId=wf.siteId and wf.listId=L.tp_Id

 

Copy from Here

Wednesday, December 8, 2010

Clean Up Event ID 7888 and 5553.

see the error at your event viewer ?

 

 

failure trying to synch site 51d6d8b3-9ee0-4af3-af2f-fa5f78cc9651 for ContentDB 840eac13-c23e-4123-9fa2-9d2975d0ff13 WebApp 53ea7eb2-ec0f-4250-9d87-1550224223b8.  Exception message was Cannot insert duplicate key row in object 'dbo.UserMemberships' with unique index 'CX_UserMemberships_RecordId_MemberGroupId_SID'.
The statement has been terminated..

 

 

Solution here:

http://social.technet.microsoft.com/Forums/en-US/sharepointadmin/thread/11af8a85-0fca-466d-beac-d29d03297edf

 

solution summary :

 

  • Run 'stsadm- o preparetomove -Site <http://whatevertheguiltysiteis/> -OldContentDb <GUID of the contentdb listed in event log error>'
  • Run 'stsadm -o sync -deleteolddatabases 0'

FQA:

  • How do i know which site is it by GUID ?
    • -- QUERY#1 Run this against your config database, this will give you the content database name

      SELECT *
      FROM [SharePoint_Config].[dbo].[Objects]
      WHERE ID='<Content DB Id from error msg>'

      --QUERY#2
      -- Run followign queries against the database name returned by the QUERY#1

      SELECT [FullUrl]
      ,[Id]
      ,[RootWebId]
      FROM [WSS_Content_##_03].[dbo].[Sites]
      WHERE ID='<Site ID from event log message>'








      or you can run the query at sitemap table at the config database , then you will see all the site under this content db , then manually go to find out on the Path column of the site URL.



      SELECT [Id]

            ,[ApplicationId]


            ,[DatabaseId]


            ,[Path]


            ,[Pairing]


            ,[Status]


            ,[Version]


            ,[RedirectUrl]


            ,[HostHeaderIsSiteName]


        FROM [MOSS_CONFIG_DB].[dbo].[SiteMap]


      where [DatabaseId]='<Content DB Id from error msg>'

      Thursday, December 2, 2010

      Create an installation source that includes software updates (Office SharePoint Server 2007)

       

      is time to build another environment, to save some step to install the service pack so we can extract the sp2.exe to update folder at the installer folder then will help direct install during installed. **aslipstreamed

       

      http://technet.microsoft.com/en-us/library/cc261890(office.12).aspx

       

      In server farm deployments, all your Web servers must have the same software update version applied. This means that, before you add a new Web server to an existing server farm, this new Web server must have the same software updates as the rest of the Web servers in your server farm. To accomplish this, we recommend that you follow the procedures in this topic to create an installation source that contains a copy of the released version of the software, along with software updates that match those installed on your server farm (also known as aslipstreamed installation source). When you run Setup from this updated installation source, the new Web server will have the same software update version as the rest of the Web servers in your server farm.

      NoteNote:

      In this article, we use the term software update as a general term for all update types, including any service pack, update, update rollup, feature pack, critical update, security update, or hotfix used to improve or fix this software product.

      Use the updates folder

      To create an installation source, you must add software updates to the updates folder of the released version of the software.

      To use the updates folder
      1. Copy the files from the released version source media for the product to a folder that you can use as an installation point for the servers in your server farm.

      2. Download the appropriate software update package.

      3. Extract the software update files, by using this command:

        <package> /extract: <path>

        The /extract switch prompts you to provide a folder name for the files, for example, for x86 systems:

        wssv3sp2-kb953338-x86-fullfile-en-us.exe /extract: <C:\WSS> \Updates

        <C:\WSS> is the location to which you copied the files that you extracted from the Windows SharePoint Services 3.0 released version.

        NoteNote:

        You must use the default location for the updates folder. If you use the SupdateLocation="path-list" property to specify a different location, Setup stops responding.

      4. Copy the files that you extracted from the Windows SharePoint Services 3.0 software update package to the updates folder you created in the previous step.

      5. Extract the Microsoft Office SharePoint Server 2007 software update files, by using this command:

        officeserver2007sp2-kb953334-x86-fullfile-en-us.exe /extract: <C:\rtm_product_path> \Updates

        <C:\rtm_product_path> is the location to which you copied the files that you extracted from the Office SharePoint Server 2007 released version.

      6. Copy the files that you extracted from the Office SharePoint Server 2007 software update package to the updates folder containing the source for the released version. You must verify that the Svrsetup.dll file has been copied from the Office SharePoint Server 2007 software update package and you should delete the Wsssetup.dll file.

        ImportantImportant:

        Delete Wsssetup.dll because it may conflict with Svrsetup.dll. Having both Wsssetup.dll and Svrsetup.dll in the updates folder for a slipstreamed installation source is not supported.

      7. You can now use this location as an installation point, or you can create an image of this source that you can burn to a CD-ROM.

        NoteNote:

        If you extracted the software update files to a location to which you had previously copied the source for a released version, the source is updated and is ready to use.

      For more information about using enterprise deployment tools to deploy updates, see the article Distribute product updates for the 2007 Office system.

      You can download the 32-bit or 64-bit edition of Service Pack 2 (SP2) for Office SharePoint Server 2007 at the following location:

      Language template packs

      Use the following procedure to create an installation location that you can use to install the language template packs with software updates already applied.

      To use the updates folder with language template packs
      1. Download the language template pack package for the released product.

      2. Extract the files from the language template pack package.

      3. Copy the extracted files to a folder that you can use as an installation point for the servers in your server farm.

      4. Download the updated language template pack package for the released product.

      5. Extract the files from the updated language template pack package.

      6. Copy these extracted files to the updates folder, in the subfolder in which you stored the files for the released product in step 3.

        You can now use this location as an installation point, or you can create an image of this source that you can burn to a CD-ROM.

      7. To install the language template pack with the software update already applied, run Setup from this location, and then run the SharePoint Products and Technologies Configuration Wizard to complete the configuration.

      Wednesday, December 1, 2010

      Delete old folder script

      nice script from internet again :D http://www.eggheadcafe.com/software/aspnet/31061977/an-easy-script-to-delete-folders-on-a-network-share-that-are-older-than-14-days.aspx

      change at cint( XX ) > XX for how many days old
      Directory > location of the folder you want delete. Network drive also can :)


      Dim Directory
      Dim Noofdays
      Dim FSO
      Dim FSO2
      Dim LogFile
      Dim Folderlist
      Dim folders
      Directory ="C:\Program Files\Microsoft Office Servers\12.0\Logs\UAP\"
      Noofdays=cint(30)
      LogFile="C:\Program Files\Microsoft Office Servers\12.0\Logs\UAP\deleted.txt"
      Set FSO = CreateObject("Scripting.FileSystemObject")
      Set FSO2 = CreateObject("Scripting.FileSystemObject")
      Set oFSO = CreateObject("Scripting.FilesyStemObject")
      '
      If oFSO.FileExists(Logfile) Then
      Const ForAppending = 8
      Set ofile = oFSO.OpenTextFile(LogFile, ForAppending, True)
      Else
      Set oFile = oFSO.CreateTextFile(logfile, true)
      End If
      ofile.writeline "Delete Folders older than 30 days Started   --> " & now()
      Set Folderlist = FSO.GetFolder(Directory)
      Set folders = Folderlist.SubFolders
      For Each d In Folders
      '          msgbox d.name
      '          msgbox d.size
      '          msgbox d.dateCreated
      '          msgbox d.dateLastModified
      '          msgbox d.dateLastAccessed
      tempdirectory = Directory & d.name
      If  datediff("d",d.dateCreated,now()) > Noofdays Then
      FSO2.DeleteFolder(tempdirectory )
      ofile.writeline "Deleting Folder...." & tempdirectory
      if err.number <>0 then
      ofile.writeline cstr(now()) & "    " & Err.description
      err.clear
      end if
      End If
      Next
      ofile.writeline "Delete Folders older than 30 days Completed --> " &now()
      ofile.writeline "--------------------------------------------"
      ofile.close