Delete user in asp.net membership provider

I have a SharePoint farm where I use the aspnet membership provider. Now and again I need to remove users due to separations, change of job, etc. so they can’t access SharePoint.

Like all of you I have to research this thing anew every time I have to do this. But no more! My future self will thank me.

Using the aspnet_Users_DeleteUser stored proc we can remove users via SSMS.

USE [database name]

EXEC [dbo].[aspnet_Users_DeleteUser]
@ApplicationName = '[Application Name]',
@UserName = '[Username]',
@TablesToDeleteFrom = 15,
@NumTablesDeletedFrom = 0

GO

@TablesToDeleteFrom can be a bit confusing when you open the stored proc up. It’s actually a bit mask. I typically have to remove users entirely so I use 15, but this blog post details out some additional options for that parameter: http://vsproblemssolved.blogspot.com/2007/01/using-sqlmembershipprovider.html

@NumTablesDeletedFrom is an input/output variable, meaning anything you put in there will get replaced by 0 in the query. You can use that parameter to inspect the output but I’m not that ambitious.

SharePoint on Azure: several lessons learned

We got back from Convergence on Friday. I had a good time overall; good food, good times, crazy things to see. Never did make it to Acme Oyster House (sorry dad!).

Meanwhile, we’ve migrated a public SharePoint site to Azure. I should say we’ve migrated a SharePoint 2010 internet site to SharePoint 2013 running in the 14-hive. Many of my colleagues throughout the SharePoint community I’ve spoke to have this to say about that:

In the last few weeks we’ve learned some valuable lessons. Here they are (in no particular order):

  • Search scopes – I chose to not migrate the Search databases because migrating the content db was hard enough. Plus, when we complete the upgrade to 2013 I would just have 1 DB to focus on. In doing so, I don’t have scopes anymore since they’ve been deprecated in SharePoint 2013. You can’t even use PowerShell to add them. The fix was to revert back to the All Sites scope. It isn’t the end of the world though because the Search Service App is smart enough to see the variation you’re searching from and serve up that site’s content. For example, if you search for bikes on the German site, you’ll get German site content back to you, rather than UK or US content.
  • SharePoint Designer – Someone wanted a quick change to a page layout. Good news here is that only 1 page in the overall site uses that page layout. SharePoint Designer 2010 works but I was unable to add another web part zone the page layout. As a workaround I added the HTML directly to the page layout. Again, not the end of the world here, but it definitely isn’t what I would want to do. Adding a web part zone would have allowed me to drop additional content in the future or replace it altogether from the Edit Page rather than editing the Page layout.
  • compat.Browser config – We never bothered with mobile sites in SharePoint 2010 with this site. All we did was turn off the mobile browsers in the compat.browser config. I did the same for SharePoint 2013 (set all ismobiledevice to false) then reset IIS. However, this did not result in success. Got hit with a vague SharePoint error. I started looking on the interwebs for help and ran across this: LINK. Followed Option 2 and hit pay dirt. It does seem out there to have to drop a statement in to the overall web.config but I had to get things working. Once we migrate to SharePoint 2013 we’ll remove the statement and make use of device channels but since we’re still running in the 14-hive I don’t get that functionality quite yet.
  • Azure IaaS growing pains – IaaS is still in preview, and thus you’re subject to wonkiness and issues beyond your control. A few days ago we experienced an outage on the site. SharePoint couldn’t talk to SQL for some reason. Logged on the SQL box and couldn’t even connect to the SQL instance. The service was running but still no dice. Well, time for a restart and Yahtzee! everything was better. I went through the logs as best I could but had no idea what I was looking for. Come to find out Azure pushed down restarts. In doing so, SQL came back before the AD instance did so nothing was authenticating properly. HUGE lesson learned there. Best way to overcome this is to start using Availability Sets: link to documentation.
  • Calculated Column issues – had a user come to me with this one. The user noticed that a calculated column was throwing a string of characters into the column. I went and checked the column settings and miraculously the issue was gone. I edited another item and got the exact same string again. So it would appear that it wasn’t the formula but rather something was going on in the DB. Luckily I was at Convergence and there were a handful of SharePoint support folks in the Expo hall. Ran by their booth and showed them the issue. Thankfully this is a known issue and installing the March PU will fix everything: link to PU. One other note, according to the KB, you MUST install this PU if you ever hope to install a future CU.

That’s about it for now.

Create a standalone report from the SharePoint 2010 Web Analytics Service App

I did it – albeit with a lot of help from a variety of folks. I successfully created an SSRS report that queries the SharePoint Web Analytics database. If you don’t have SSRS, then there’s always PowerPivot. The queries will work with both. Here’s how I did it.

First, you’re going to need the Site Aggregation ID. Why Microsoft uses this is beyond me. I ran across a blog post where the writer wrote a console app to extract this (blog post HERE). Tried the app with no luck. I then posted THIS on TechNet. Thanks to Guru Karnik for getting me on the right path.

The only catch is Guru’s query uses CURSORs. According to Tim Laqua this is very bad, and I’m inclined to listen to him (you’ll notice our blogs look eerily similar – yes I used the same layout). So what to do? Well we first need the Aggregation ID. I grabbed this portion of Guru’s query and fired away:

SELECT DISTINCT DimensionName as SiteCollectionID,AggregationId,SM.[Path]

FROM WASiteInventorySnapshot WASIS WITH (NOLOCK)

INNER JOIN [SPConfigDB].[dbo].[SiteMap] SM

ON WASIS.DimensionName = SM.Id

WHERE WASIS.DimensionType=0

Order by Path

Take a look at your results and you now have the Site Collection GUID, the AggregationID and the URL path. Filter on the site collection you’re after and grab the Aggregation ID. If you’re not sure which site collection you want to grab (as was the case for me), you can grab the site collection id and use PowerShell to show you the way like this:

image

image

OK, AggregationID in hand, we place it into the following query:

USE [WebAnalytics_ReportingDB]

   --Declare everything

DECLARE @SiteId UniqueIdentifier

DECLARE @AggregationId UniqueIdentifier

DECLARE @SitePath NVarchar(255)

DECLARE @StartDate Date

DECLARE @EndDate Date

DECLARE @DateDiff Int

  --Set Variables; @datediff is the amount of days you're after

SET @DateDiff = 30

SET @StartDate = DATEADD(d,-1*@DateDiff,GETDATE())

SET @EndDate = GETDATE()

SET @AggregationID = '[paste aggregationid here]'

SET @SiteID = '[paste siteid here]'

SET @SitePath = '[paste URL here]'

  --Make Magic

SELECT @SiteId AS 'SiteId',@AggregationId AS 'AggregationId',@SitePath AS 'SitePath',@StartDate 'StartDate',@EndDate 'EndDate',

* FROM [WebAnalytics_ReportingDB].[dbo].[fn_WA_GetSummary] (Cast(CONVERT(varchar(8),@EndDate,112)as int),Cast(CONVERT(varchar(8),@StartDate,112)as int),@DateDiff,@AggregationId,1)

You’ll notice I took Guru’s query and further tweaked it for my use. Couple of notes here:

  • The GetSummary function the query is calling is a little jacked. You need to set a Start Date, End Date, and a Date Difference (i.e. the difference between the 2 dates). Why? Because that’s the variables the function wants. Like I said…jacked. Get’s worse below.
  • For me, I wanted the SSRS report to always display the last 30-days starting with Today’s date. Later I can always go back and add some sort of parameter/expression magic to make the dates customizable by the user, but for now this works.
  • Notice in the Set Variables section of the query that I’ve included the SiteID and SitePath. I did this for posterity only. It has no other bearing on the query other than making it available for me when I create the report.
  • I first declare the date fields as Date so that I can do some magic with them in terms of calculating the dates programmatically, but later on I have to convert them to Int so the function will work properly. Very jacked, but hey, it works.

But what about Top users? Got that too:

USE [WebAnalytics_ReportingDB]

   --Declare everything

DECLARE @SiteId UniqueIdentifier

DECLARE @AggregationId UniqueIdentifier

DECLARE @SitePath NVarchar(255)

DECLARE @StartDate Date

DECLARE @EndDate Date

DECLARE @DateDiff Int

  --Set Variables; @datediff is the amount of days you're after

SET @DateDiff = -30

SET @StartDate = DATEADD(d,@DateDiff,GETDATE())

SET @EndDate = GETDATE()

SET @AggregationID = '[paste aggregationid here]'

SET @SiteID = '[paste siteid here]'

SET @SitePath = '[paste URL here]'

  --Make Magic

SELECT @SiteId AS 'SiteId',@AggregationId AS 'AggregationId',@SitePath AS 'SitePath',@StartDate 'StartDate',@EndDate 'EndDate',

* FROM [WebAnalytics_ReportingDB].[dbo].[fn_WA_GetTopVisitors] (Cast(CONVERT(varchar(8),@StartDate,112)as int),Cast(CONVERT(varchar(8),@EndDate,112)as int),@AggregationId,1)

Similar to the query above, only this time I call a different function: GetTopVisitors. Again, had to do some magic with the Start and End Dates to calculate programmatically.

This is only the beginning. Lot’s of possibilities here. The downfall to this approach is that it’s site specific. There is a function that will show top pages, but you can’t get down to the specific web (i.e. sub-site).

Next iteration will allow users to input custom dates and we’ll get creative with the graphs and charts in SSRS (think: dancing kittens and rainbows style). Thanks to everyone that gave me a hand in this.

SQL Query Basics

Needless to say, I’m a SQL n00b. For the longest time I drank the Microsoft Kool-aide and believed “you shouldn’t touch the SQL side of SharePoint.” And when I say “shouldn’t touch” I mean absolutely no touching or querying of any kind. Well, I’ve wised up. It’s not that you can’t touch anything, just be mindful of what you can and can’t do. Querying – as long as you do it with no lock – will not adversely impact anything. And above all else, you must have a healthy respect for what you’re doing lest you decide to anger the SharePoint Gods. This post is for all my peers out there that do a little SP Admin work on the side, but – for a variety of reasons – don’t get into the SQL side of things much.

SQL Management Studio is the best (and only?) tool used to query SQL. It’s a pretty straightforward, easy-to-use product. Open it up, connect to a database, and click the “New Query” button:

image

Pretty simple. From here you can query SQL (i.e. “ask” SQL things):

USE [database name]

select * from [database table]

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

The Use statement dictates which database you want to query. There is a dropdown near the “New Query” button, but the Use statement is so much easier to use. Plus, I’ve – on many occasions – forgot to select the correct database from the dropdown. I just use the Use statement from now on. “Select *” means you want to return all the columns in that table. This can be helpful to use when you just want to poke around and learn about SharePoint’s innards.

Use [database name]

select * from [database table] with(nolock)

 

“with(nolock)” is important because it doesn’t lock up the table while you’re querying it. I again learned this one the hard way. Locking up tables is a bad thing, meaning no one else can use said table until the query is done. Don’t do what I did and lock up the content database. Does not make for good job security. Furthermore, it’s generally a good idea to run just about any query (at least in the case of SharePoint) with no lock on it.

One last query nugget of wisdom. Replace the * in the select statement with column names (comma separated). So for example:

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

Use [database name]

select LastName,FirstName,Address from [database table] with(nolock)

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

Your query will return the results with just the Last Name, First Name, and Address columns. Doing so will help clear the query results up and remove any extraneous data.

These 3 basic queries – while n00b’ish – are very valuable to know and will hopefully help you when working with Management Studio.