How do I - Allow someone the ability to TRUNCATE TABLE in SQL Server without granting them DBO rights?

There comes a time where a developer produces a stored procedure (sproc) that tries to clear a table quickly. In the case of permanent tables, this is usually done using the TRUNCATE TABLE statement in TSQL. Of course in a production setting, you will have limited rights on your SQL Server and databases. As such, we generally grant users DB_DATAREADER and DB_DATAWRITER rights. Additionally, we'll grant users EXECUTE rights on sprocs. The problem is that in order to do a TRUNCATE TABLE, SQL actually drops that table in order to clear it so quickly. That won't be possible when a users is trying to execute the sproc in production. Since they have EXECUTE rights, they'd be able to execute it, but it will fail when the system tries to drop the table in question. For that, the executor needs to have DBO rights. Of course we're not going to grant people DBO rights in production! So the question becomes, how do you solve the problem?

I'm assuming that well defined change management and process control is in place already. If it is not, you need to start there first. Once you have a proper method for reviewing changes to sprocs going into production, you can apply this quick fix:

TSQL BEFORE

ALTER PROCEDURE [dbo].[BlaBlaBla]
@TableName varchar(60)
AS    
DECLARE @QUERY NVARCHAR(200);
...

TSQL AFTER

ALTER PROCEDURE [dbo].[BlaBlaBla]
@TableName varchar(60)
WITH EXECUTE AS OWNER
AS
DECLARE @QUERY NVARCHAR(200);
...

By simply adding the WITH EXECUTE AS OWNER line to the sproc, it will execute as owner level permissions, thus allowing the TRUNCATE statement to succeed.  Now it's important to understand that this creates a security risk that YOU must manage.  That's why it's important to review any and all changes that are made to the sprocs, especially if they're going to have this statement in them.  It may be better to just create a generic sproc that you can feed a table name to, which elevates like this and truncates the table passed to it.

Enjoy
C

How do I - Determine what type of site template was used when a SharePoint site was created?

If you work in SharePoint regularly, you may develop the ability to look at a SharePoint site and correctly guess which template was used in the creation of said site.  Of course, given how SharePoint works, you'd probably only have a 50/50 shot at best of actually being correct.  Given how most everything is connected to Features and the fact that site templates in most cases just have a given sub set of Features that are turned on during the creation process, it's really anybody's guess.
Powershell to the rescue!
Of course, if you're an administrator with shell access to your farm, you can always resort to Powershell to get that answer.  All you have to do is get the WebTemplate property of the SPWeb object for the site thus:

Write-Host (Get-SPWeb http://sharepoint.crayveon.com/sites/site123 | Select WebTemplate);

Alternatively, you could use the Get-SPSite cmdlet at the beginning of the pipe to get all the sites and sub sites and their templates thus:

Write-Host (Get-SPSite http://sharepoint.crayveon.com/sites/site123 | Get-SPWeb | Select URL, WebTemplate);

Enjoy

C

My "Save site as template" option is gone from Site Settings in SharePoint 2013... where did it go?

A common practice in the world of SharePoint is to create sub site templates.  A site collection administrator will customize a template sub site and then via the Site Settings use the "Save site as template" option to create a new template that can be used for new sub sites.  The problem is that this option isn't always available.  So why not?
The answer lies in the activation of Publishing Features on the site collection.  If at any point in time during the entire life cycle of the site, you had turned Publishing on for the site collection, this option is removed permanently.
See https://support.microsoft.com/en-us/kb/2492356 for more detail.

Later
C

How to convert text to upper or lower case in InfoPath

If you're developing SharePoint forms with extended functionality, then you've most likely already been exposed to InfoPath.  You know, that thing that Microsoft said was dead years ago? :-)
Unfortunately, InfoPath doesn't have the maturity of something like Visual Studio and C# when it comes to text handling.  It would have been great if we could have had access to a .ToUpper() or .ToLower() method when dealing with text, but alas, we do not.
To that end, we have to use a workaround leveraging the translate() method.  In order to generate the equivalent of .ToUpper() which converts all text to uppercase, you'll need to use this:

translate(., "abcdefghijklmnopqrstuvwxyz", "ABCDEFGHIJKLMNOPQRSTUVWXYZ")

Vice versa, to get a .ToLower() equivalent which converts all the text to lowercase, you'll need:

translate(., "ABCDEFGHIJKLMNOPQRSTUVWXYZ", "abcdefghijklmnopqrstuvwxyz")

Enjoy

C

How do I - Fix the problem where SharePoint Designer 2013 crashes upon opening a site?

This one is REALLY annoying!

I never understood why software vendors always assume that users are on the latest and greatest version of their software.  Furthermore, they assume you're only using the current version by itself.  The REAL WORLD REALITY is much further removed from the utopian vision most vendors have of their products.

In my case, this was a problem back in the previous version cycle, when I upgraded from SharePoint 2007 to 2010.  My clients were migrating from 2007 to 2010 thus having content in both formats.
Of course, this problem didn't go away or get fixed, but instead it persists to this day with the version upgrade from SharePoint 2010 to 2013 and I'll be you dollars to donuts we'll see this problem going from 2013 to 2016 as well.

Synopsis

A large company with multiple terabytes of content decides to upgrade from SharePoint 2010 to 2013.  Given the large amount of data, it's a phased migration over time.  As a result, some sites are in 2010 and some are in 2013.  End user support staff will need to be able to support both 2010 and 2013 sites.  When these users install SharePoint Designer 2013 and then try and open a SharePoint 2013 site with SPD 2013, without uninstalling SPD 2010 i.e. running 2010 and 2013 parallel, then SPD could crash consistently.  Oh, and forget about going back to 2010 also.  It will also now be crashing upon opening a site.  I consider this to be a product bug.
I really wish concurrent version testing would have higher priority with software vendors.

Solution

To solve this little conundrum, we're going to have to hack the system... time to fire up good old REGEDIT.  Follow these steps to clear up the problem.

1. Click the Windows start button.
2. Type "regedit" and press enter.
3. In the registry editor, locate the following key:
   HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Common\Open Find\Microsoft SharePoint Designer\Settings\Open Site
4. Delete the "ClientGUID" key located here.
5. Locate the next key:
   HKEY_CURRENT_USER\Software\Microsoft\Office\15.0\Common\Open Find\Microsoft SharePoint Designer\Settings\Open Site
6. Delete the "ClientGUID" key here.

Now restart SharePoint Designer and your sites should open as expected.

NOTE:  Your most recently used list will now show both 2010 and 2013 sites.  It's recommended to keep using SPD 2010 with SharePoint 2010 sites and 2013 with 2013 sites.

Hopefully this post saves someone some time and frustration.

Later
C

Powershell Quick Tip - How do I get the Powershell Version?

I often get asked how to determine the version of Powershell running in an environment.  I wish it was a simple as just typing "ver", but alas, it's slightly more complex than that.  You'll need this statement:

$PSVersionTable.PSVersion

This will output the version table looking like this:

PS C:\> $PSVersionTable.PSVersion

Major  Minor  Build  Revision
-----  -----  -----  --------
4      0      -1     -1

Enjoy
C

How do I - Select data from a table using TSQL while not returning any duplicates?

When working with large lists of data that are not guaranteed to be unique, you often have to filter out duplicate values.  To do this in SQL Server using TSQL, you can leverage the GROUP BY/HAVING combination of statements thus:

Enjoy

C

SharePoint Audit Log reporting

SharePoint has some built in Audit Log reporting that can be leveraged for rudimentary auditing purposes.  In order to these reports to become available, you will need to configure the Audit Settings on a per site collection basis from the /_layouts/15/settings.aspx page.

1. From your site collection root, click the Settings gear in the top left of the page.
2. On the dropdown menu, click "Site Settings".
3. 
4. Under the "Site Collection Administration" section, click the "Site collection audit settings" (/_layouts/15/AuditSettings.aspx) link.
5. 
6. On the "Configure Audit Settings" page, you can configure a multitude of options such as audit log trimming and the different events to audit.
7. 
8. Once you've configured your desired settings, click the "OK" button to close the page and enforce the settings.

You will have to wait for the audit processing timer jobs to run before reports will become available.  The delay time will depend on your system's configuration, but by default these jobs run daily.
To view the reports, simply return to the site settings page and click "Audit log reports" (/_layouts/15/Reporting.aspx?Category=Auditing) under the Site Collection Administration section.
From this page, you will have several report options.



Play around with these reports and see what's useful to you.
Happy SharePointing!

Later
C