search database problem causing the crawling hang.

 

Error message:

Event ID 9511 from Source Office Server Search on agent computer xxx.domain.com has triggered this Alert Description : Errors occurred during database operation, please make sure the SQL server is running and there is sufficient disk space available on the database drives.

Context: Application 'SharedServices1', Catalog 'Portal_Content'

Symptom:

index crawling is hang , can check at the search admin at SSP ( crawl log is not moving and stuck for many hour after DB problem)

Solution :

make sure DB server is back online and do recycling search service below :

Recycle Services

The crawl portion of search is a state machine and components sometimes get out of sync. Because the state of the crawl is stored in the Search databases, the search processes can be recycled. When restarted, these will simply regain state from the DB. Also, as noted above, there are cases where provisioning components may get hung up, so recycling the Timer and Administration service could also help:

• From services.msc on each of the Query Servers, recycle the following services:
  • Office Server Search
  • Windows SharePoint Timer

• From services.msc on the Indexer, then recycle:

  • Office Server Search
  • Windows SharePoint Services Administration
  • Windows SharePoint Services Timer

more information : http://blogs.msdn.com/b/sharepoint_strategery/archive/2012/09/10/troubleshooting-the-ssp-search-indexer-for-moss-2007.aspx

SharePoint 2007 Content Deployment

After so many year in SharePoint , finally I am trying to use Content Deployment function at SharePoint CA > operation.

Benefit I see at the function :

• can migrate the top level site without the sub sites
• easy to mange export import job , which is migration the sub sites to another site
• easy for administrator migrate the staging/QA sub sites to production
• can schedule the content deployment task
• easy to repeat the task for update the content and same destination

About site Template and site definition

 

Just notice the enterprise tab is not available when i try to create on our production , look like is someone hiding it. So try find some information on internet to see how to disable the settings .

 

some information to keep track about template topic.

 

What are site templates? > Here

 

Register site definitions > Here (below article )

 

Last night, Carl and I did end up recording the sequel to the first SharePoint 2007 DNRTV show we did earlier. In this episode, I had a chance to talk more about content types, and then I ran through a full-fledged example demonstrating an InfoPath Forms Services based solution solving a real life problem. And then after that I demonstrated creating KPI (key performance indicator) reports based on data collected via infopath forms running in a web browser.

All in all, the show I think, turned out quite well. I will blog about it when it is online.

But, that is not what this blog post is about. When Carl and I were recording, based on a conversation, I had alluded to the fact that you can create your own site definitions and register them within SharePoint 2007. How you ask? It’s a question of twiddling with a bunch of XML files. To which, of course the developer world comes to a screeching halt.

Yes, SharePoint does suffer from XMLITIS, but I have blogged about that as one of the criticisms, and let us get over that.

The real Q is, how can you, register your own site definitions. Well here is how.

1.

First you need a custom site definition. As you may be aware, a custom site definition is nothing but a logical grouping of features, lists, web parts, and a whole bunch of other things, that together make a site. For instance, I could create a custom site definition for a “Blog reader” application. (Speaking of which, in a week or two, the next issue of code-magazine will contain an article from me, demonstrating exactly how to create a blog-reader application a.k.a. bloglines/newsgator etc in SharePoint). All site definitions reside as a bunch of files at the following location.

C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\SiteTemplates

(Note: I am almost done reviewing Scot Hilier’s upcoming Apress book on SharePoint 2007. He explains this very well in Chapter 11, and I strongly recommend you picking a copy of it – the book is simply excellent, and yes I will post a full review later).

Now in that directory, there are a bunch of site definitions already created. You can create one of your own, but since this blogpost doesn’t talk about that – go ahead and copy paste, “BLANKINTERNET” to a directory called “Sahil”

2.

Now ideally, the directory “Sahil” which contains the details of your site definition, should contain something meaningful in it – so a blogreader site definition will contain the necessary lists, features etc. But ignoring that, let us next look at, how you can register this new Site definition, in SharePoint.

Now at this path –

C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\

You would note a curiously named directory called “1033”. 1033 is the culture number for en-us (US English). Under 1033\Xml, there are a bunch of XML files. Strangely enough, many of them are named as “WebTemp*.xml”. That is where you need to put this new site definition’s information, so SharePoint will understand it. Go ahead and copy paste webtempsps.xml and call the new file webtempsahil.xml. The name is important, since SharePoint will look for all files that match “WebTemp*.xml”.

3.

Now go ahead and edit the webtempsahil.xml file to look like as below.

<?xml version="1.0" encoding="utf-8"?>
<!-- _lcid="1033" _version="12.0.4407" _dal="1" -->
<!-- _LocalBinding -->
<Templates xmlns:ows="Microsoft SharePoint">
  <Template Name="Sahil" ID="10001">
    <Configuration ID="0" Title="Sahil's site" Hidden="FALSE"
ImageUrl="/_layouts/1033/images/IPPT.gif" Description="This is the custom site I was talking about." SubWebOnly="FALSE" DisplayCategory="Winsmarts">    </Configuration>
  </Template>
</Templates>

A few things that will jump out at you from the above XML file.

  - ID must be greater than 10000, so your IDs don’t screw up SharePoint’s.
  - You have a whole bunch of other control from the various possible elements, I think the above are self-explanatory.

4.

Now go ahead, and do an iisreset, and try creating a site collection under SCAW (SharePoint Central Web Administration), or a site under a site collection through various means. You should see something like the following in the page that prompts you to create new sites:

Note that the text, the image, and everything else has been picked from WebTempSahil.xml.

Now how cool is that J!!? Now you can merrily create your custom site definitions, register them, and create them on a per-demand basis.

In further blog posts, I will cover various other aspects of Site Definitions. Until then, Rock On!!

About Alert email issues

Issues : new item assign to task list , owner not receive alert

Step to check:

stsadm -o getproperty -pn job-immediate-alerts -url http://abc.com/sites/XXXX

this command is check the alert timer job status :

Restults:
<Property Exist="Yes" Value="every 5 minutes between 0 and 59" />
which  mean the alert timer will check every 5 minutes.
*normally this is standard setting

Next Step:

Run following command to check from SQL server whether new item created or not :
· use [content_DB
]
select top 50 itemname,EventData,ACL,* from EventCache order by EventTime desc

you should see the new created item , else which mean something wrong.

Solution :

create new list and try again.

ha ha..is not perfect solution but anyhow let me resolve mine problem.

just notice have good post about the alert :
http://msmvps.com/blogs/bradley/archive/2009/03/22/how-to-troubleshoot-alerts-in-wss-3-0-moss.aspx

Only with Document library permission is unable to subscribe Alert me!

Issues :

 

user only have full control permission at document library, when user try to click on alert me setting then will show Error: Access Denied

 

Root cause:

 

this is by design on Microsoft product or maybe is bug :D

When you click on the Alert me you will route to the page sites/abc/_layouts/XXXXX , which is needed read permission at site level.

 

Solution:

 

1. Grant user view permission at site level. > if allow user to see other information

2. If user using client Microsoft outlook 2007 then can direct sync the folder to outlook ( if have any new document upload then you will see unread document at outlook)

3. Admin can help user add the alert.

sub sites owner with Full Control unable to add Content Editor Web Part

after so many year at sharepoint , just notice web part also have permission setting.

 

below is the detail from the forum.

 

 

http://www.tek-tips.com/viewthread.cfm?qid=1444650&page=3

 

Background: SharePoint V3 on a hosted server. The user is a "site owner" and "site owners" have "full control" over the site.  "full control" has all the options that can be selected for security.  This is a sub-site to the main SharePoint site and it does not inhearit security from the parent site.  I want site owners to have full control over there individual sites, but not have control over other's sites.  
Problem: When the user goes to add a web part to the "Home" page of the site via selecting "Site Actions" - "Edit Page" the only web parts that are available to them are the "Lists and Libraries" web parts that consist of "Announcements", "Calendar", "Links", "Shared Documents", "Tasks", and "Team Discussion".
When a Site Collection Administrator does the same thing as above, they see all the web parts listed above as well as "Miscellaneous" web parts consisting of "Content Editor Web Part", "Form Web Part", "Image Web Part", "Page Viewer Web Part", "Relevant Documents", "Site Users", "User Tasks", and "XML Web Part".  Is it possible to make these web parts accessible to the owners of sub-sites with out giving them full control over the entire SharePoint site hierarchy?

 

 

I believe I figured out a solution.  So, if your curious, here is how I fixed it.
Logged into the top level SharePoint site as a site collection administrator.
Select "Site Actions" - "Site Settings"
In the "Galleries" Colum, select "Web Parts"
With the Web Part Gallery open, select the "Settings" menu and select "Gallery Settings"
Now you should be in the Customize Web Part Gallery window.  In the "Permissions and Management" column, select "Permissions for this gallery".
In the "Permissions: Web Part Gallery" Select "Actions" - "Edit Permissions"
Select "OK" to the pop up that is displayed warning you that you are about to create unique permissions, and now you can select "New" and add additional users to the Gallery.
This will allow whomever you add to see all the Web Parts available.

Introduction to customizing pages by using Web Parts

WSS list is so useful for business user, can do something simple system by the simple list and filter :D. i am start thinking should i be business personnel rather then SharePoint engineer? ha ha ha , ok, just keep tract this article from Microsoft , i am sure one day i will consult user to user this :D

 

 

This article is intended for Web page owners and administrators. It provides an overview of Web Parts and Web Part Pages and explains how you can customize a page by adding, changing, or deleting Web Parts.

• Overview of Web Parts and Web Part Pages
• Types of Web Parts
• Ways to use Web Parts and Web Part Pages
• Web browser support for Web Part Pages

Overview of Web Parts and Web Part Pages

A Web Part is a modular unit of information that forms the basic building block of a Web Part Page. You can add Web Parts to Web Part zones in a Web Part Page and then customize the individual Web Parts to create a unique page for your site users.

The following example uses the Image Web Part to describe the basic features of a Web Part.

---

The Web Part title bar contains the heading for the Web Part.

The Web Part menu contains functions that enable you to minimize or close the Web Part, edit the Web Part, or get Help for a specific Web Part. When the page is in edit mode, you can also use this menu to delete the Web Part or connect it to other Web Parts, depending on the type of Web Part that you are using.

The body of the Web Part contains the content that you specified for the type of Web Part that is being used. In this example, the Web Part is an Image Web Part, which displays an image.

---

A Web Part Page is a special type of Web page in which you can use Web Parts to consolidate data, such as lists and charts, and Web content, such as text and images, into a dynamic information portal that is built around a common task or special interest.

Your site home page is one example of a Web Part Page. When you create a new site or workspace site, you are creating a Web Part Page. You can also create a Web Part Page by selecting one of the available site templates, or you can use a Web design program that is compatible with Microsoft Windows SharePoint Services, such as Microsoft Office SharePoint Designer 2007, to create a Web Part Page from scratch.

You can use a Web Part Page to present a variety of structured and unstructured information in an organized, useful, and convenient way. Web Part Pages often contain several Web Parts that are connected so that you can dynamically display data and content to see the results that you want.

For example, you can create a Web Part Page called Customer Orders that you frequently use to display critical information. You get a call from a customer who has a question about an order, does not remember the order ID number, but does remember the date when the order was placed. You can use a Web Part Page to do the following.

---

Look up an order by order ID number or, in this case, the order date.

Display all orders by date.

Select the correct order, based on the customer's name, and look up the order details as well as the customer details.

Select a line item in the order (in this case, the lamp), and display a product picture to confirm the customer's question.

Scan for late-breaking business news that is pertinent to the customer's order.

---

WEB PART PROPERTIES

Each Web Part shares a set of common properties (also called base class properties) that are organized into sections in the tool pane and that control the Web Part's appearance (such as the title, height, and width), layout (such as the Web Part order in the zone and the direction of the content), and advanced characteristics (such as the image icon and description).

Many Web Parts also have custom properties that are unique to the Web Part. These are usually displayed either above or below the common Web Part properties in the tool pane. For example, the Image Web Part has additional custom properties, including the image link, its horizontal and vertical alignment, and background color.

NOTE   Depending on how the Web Part was created, a Web Part custom property may be displayed in a default Miscellaneous section below the common properties in the tool pane.

WEB PART VIEWS

You can customize a Web Part in one of two views:

• Shared view    You can add a Web Part to a Web Part Page and then edit the Web Part Page in a shared view. Shared Web Parts are available to all users of a Web Part Page who have the appropriate permission.
• Personal view    You can add a shared Web Part to your own personal view and then edit your view of the Web Part. The changes that you make to a Web Part while you are in a personal view are available only to you. Other users who did not make changes in a personal view continue to see the shared view of the Web Part.

The view of the Web Part that you are working with can be important because:

• You may have permission to edit only some Web Parts on certain Web Part Pages but not on other Web Part Pages.
• You may be able to connect to certain Web Parts on a Web Part Page but not to other Web Parts on the same Web Part Page.

WEB PARTS AND WEB PART CONNECTIONS

An additional feature of Web Parts is the ability to easily connect them by passing data between them and synchronizing their behavior. By connecting Web Parts, you can manage data in dynamic and interesting ways. In many products and technologies, the task of connecting sets of data from different data sources is not easy and often requires programming skill. But with Web Parts, making data connections is as simple as using menu commands. By connecting Web Parts, you can, for example, present data from two Web Parts in alternate views, perform related calculations between two Web Parts, and filter a Web Part by using values from another Web Part — all on one Web Part Page.

WEB PART ZONES AND THEIR PROPERTIES

Web Part zones are containers of Web Parts that are used to group and organize Web Parts on a Web Part Page. Web Part zones also have a set of properties that serve a dual purpose. You can use one subset of properties to organize the layout and format of Web Parts on the Web Part Page. You can use another subset of properties to provide an additional level of protection from modification (or "lock down") of the Web Parts within the zone.

The Web Part zone properties each have default settings or behaviors. As you add Web Parts to the Web Part Page, some of these property values are automatically set. These property values are not designed to be edited in the browser, but you can edit them by using a Web design program that is compatible with Windows SharePoint Services, such as Office SharePoint Designer 2007.

For more information about Web Part zone properties, see the Windows SharePoint Services 3.0 SDK, which is available from the Windows SharePoint Services Developer Center on MSDN.

TOP OF PAGE

Types of Web Parts

Windows SharePoint Services provides several Web Parts that are ready to use with your site. You can use these built-in Web Parts, customize them to suit your needs, or create new Web Parts and upload them for use throughout your site.

DEFAULT WEB PARTS

The following Web Parts are included by default in any site and can be customized to suit the needs of your team. Many of these Web Parts can also be connected to each other to create a variety of unique solutions:

• Content Editor Web Part    You can use the Content Editor Web Part to add formatted text, tables, hyperlinks, and images to a Web Part Page.
• Form Web Part    You can use the Form Web Part to connect to and filter a column of data in another Web Part. Both Web Parts must run on the same server.
• Image Web Part    You can use the Image Web Part to add a picture or graphic to a Web Part Page. To more easily coordinate the image with other Web Parts on the page, you can control the vertical alignment, horizontal alignment, and background color of the image inside the Image Web Part by editing its custom properties in a shared view.
• List View Web Part    You can use the List View Web Part to display and edit list or library data on your site and to connect to other Web Parts, including other List View Web Parts. Lists are information that you share with team members and often display in tabular format. List views display this information in different ways for different purposes, such as filtering, sorting, or selecting specific columns.

NOTE   There is no Web Part called List View. When you create a list on your site, a List View Web Part is automatically created and named after the list. For example, if you create a list called Boats, a Web Part called Boats will be available in the Site Name gallery. The Web Part automatically displays the data contained in the list that you created.

• Page Viewer Web Part    You can use the Page Viewer Web Part to display a Web page, file, or folder on a Web Part Page. You enter a hyperlink, file path, or folder name to link to the content.
• Site Users Web Part    You can use the Site Users Web Part to display a list of users and groups who have permission to use a site. The Site Users Web Part automatically appears on the home page of a Document Workspace site. You can also add the Site Users Web Part to any Web Part Page.

NOTE   In sites that are running on Microsoft Windows SharePoint Services 2.0 and earlier, the Site Users Web Part was called the Members Web Part.

• XML Web Part    You can use the XML Web Part to display Extensible Markup Language (XML) and apply Extensible Stylesheet Language Transformations (XSLT) to the XML before the content is displayed. For example, you might have an XML file that contains a list of boats, prices, and links to images of the boats. You can use the XSLT to transform the data to display a list of boats and prices and make the boat name a hyperlink to display the image in a separate window.

PRECONFIGURED LIST VIEW WEB PARTS

The following Web Parts are built into the Windows SharePoint Services team site template and are automatically configured and ready to use on a Web Part Page when you create a new team site. Different combinations of these Web Parts are included when you create a team site or workspace site, depending on which site template you select.

NOTE   These Web Parts are derived from the List View Web Part and use preconfigured Web Part templates to create their unique layout and design. To add data to these lists, on the Quick Launch, click View All Site Content, and then clickLists. On the All Site Content page, click the name of the list for which you want to add data.

• Announcements    Use the Announcements Web Part to post news, status, and other short bits of information that you want to share with team members.
• Calendar    Use the Calendar Web Part to display upcoming events or team schedules.
• Links    Use the Links Web Part to post hyperlinks to Web pages that interest your team.
• Shared Documents    Use the Shared Documents Web Part to share files from the default document library with site users.
• Tasks    Use the Tasks Web Part to assign a task to a member of your team, specify its due date and priority, and indicate its status and progress.
• Team Discussion    Use the Team Discussion Web Part to provide a forum for talking about topics that interest your team.

CUSTOM WEB PARTS

By using a programming environment that is compatible with Windows SharePoint Services, such as Microsoft Visual Studio, developers can exploit the full feature set of Microsoft ASP.NET to create custom Web Parts. A Web Part Page is an ASP.NET file (.aspx), and Web Parts are derived from Web Form Controls. To further enhance Web Part Pages, developers can create their own Web Parts that provide new functionality. Developers can also add custom properties to the Web Parts, add custom builders in the tool pane for specialized user interfaces, and connect to other Web Parts by using Web Part connections. For more information about creating and deploying Web Parts, see the Windows SharePoint Services 3.0 SDK, which is available from the Windows SharePoint Services Developer Center on MSDN.

You can also use Web Parts that other people or companies have created. You must have appropriate permissions to add a third-party Web Part to your Web Part Page or site. Some Web Parts may need to be deployed directly to the server. If you are unable to add a third-party Web Part to your Web Part Page or site, contact your administrator for assistance.

TOP OF PAGE

Ways to use Web Parts and Web Part Pages

You can use Web Part Pages in the following ways:

• Consolidate data from different data sources.
• Report and summarize critical data.
• Analyze and aggregate data (for example, sums, totals, or counts).
• Summarize key information that you want to see at the beginning of each day.
• Prioritize and highlight project or customer data to help you make effective decisions.
• Display an up-to-date work schedule and meeting information to quickly plan your day.
• Get quick access to business news, local weather, and your favorite Web sites to focus your Web browsing.

WAYS TO CREATE AND CUSTOMIZE A WEB PART PAGE

There are several ways to create and customize a Web Part Page:

• The New Web Part Page form    The most common way to create a Web Part Page is through the New Web Part Page form. On the Site Actionsmenu , click Create, and then click Web Part Page to open the New Web Part Page form. After using this form to create a page, you can begin designing the page right away in the browser. When you want to browse through the page, just close the tool pane.
• A Web design program    By using a Web design program that is compatible with Windows SharePoint Services, such as Microsoft Office SharePoint Designer 2007, you can make advanced customizations to a Web Part Page, including the following:
  • Customize the theme of a Web Part Page that uses the site theme by default.
  • Edit a Web Part Page template or create a new one.
  • Customize the page layout.
  • Edit zone properties.
  • Add HTML code or Web controls.
  • Change the way Web Parts are ordered inside a zone.
  • Add Web Parts outside a zone, or add a Web Part to a Web page that is not a Web Part Page.
  • Create connections between Web Parts on different Web Part Pages.
  • Publish a Web Part Page to a Web site that is running Windows SharePoint Services.
  • Customize the Form Web Part and use the Data View Web Part.

TOP OF PAGE

Web browser support for Web Part Pages

Web browser support for Web Part Pages can be categorized into two levels:

• Level 1 support is the highest level of visual and functional support. Browsers that provide level 1 support include recent versions of Microsoft Internet Explorer for Microsoft Windows.
• At level 2 support, a few visual and functional features are not available to some browsers, and some other features are compromised and behave differently from the way they behave in level 1 browsers. However, the majority of features are still available to users. Browsers that provide level 2 support include Firefox 1.5 and later for Windows and Netscape Navigator 8.0 and later for Windows. Level 2 browsers are not supported for Windows SharePoint Services 3.0 Central Administration.

NOTE   Level 2 browsers do not support the creation of connections between Web Parts.

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

Site Collection Administrator VS Site Owner

hi hi..one of the question ask by interviewer :D . as i understand is about the recycle bin and quota reach limit contact!!  After that i found the answer here :

 

http://blogs.sharepointhosting.com/Lists/Posts/Post.aspx?ID=61

 

About twice a week someone calls or writes into support trying to figure out why their Site Owners cannot reset passwords, manage the recycle bin, or "see everything" in site settings. The answer is pretty easy – you have to be a Site Collection Administrator to be able to perform those actions. The next question we get from Site Owners is "how do I become a Site Collection Administrator?" The answer is easy enough, find out who your current Site Collection Administrator is and ask that individual to promote you to that role. Here's a short tutorial on how to create new Site Collection Administrator.

Take a look at the graphic below (click for full version). It displays the different views if you will see if you log into the Site Settings portion of your WSS 3.0 site as a member of the site owner's group and then as a Site Collection Administrator. Hint – the Site Collection Administrator's view is the top image. You can easily replicate this for yourself if you want to test it out – just create a test user that is a member of the Site Owners (or similar) SharePoint security group and go to the site settings page.

 

Extra this are at site setting :D [all the time our permission is Farm admin , then will never notice about this!! ha ha] Now you know? :D