SQLServerPedia

PASS liked our “Mouth Wide Shut” interviewing session from last year so much that they’ve decided to have us back for part two: Mouth Wide Shut: Interviews from the Other Side of the Table.

This is our brand-spanking-new session for the interviewERs…those who question, vet, and rant about it afterward. Check out all the details on the PASS Session Details page. And while you’re there, feel free to take a look around at the other community sessions.

So, to review:

• PASS Summit 2013 is in Charlotte, NC
• On October 15-18
• With pre-conference sessions October 14-15
• Discount (good ill June 30) is $800 off the final rate , and you can get another discount from your local PASS Chapter (see here).

There, I think we’ve covered it all.

Happy days,
Jen McCown
http://www.MidnightDBA.com/Jen

Seems I managed to make it through all of April without an update to the index analysis stored procedure.  It wasn’t for lack of using it with clients, but a lack of time to get the changes out the door.

A few months back, I added the option to look at just duplicate index or overlapping index results.  Have you taken the opportunity to use those features?  If so, I’d like to hear from you to find out if the output is sufficient to make judgments on dropping the or modifying your indexes.  Please reach out or leave a comment so I can make those sections better.

INDEX ANALYSIS UPDATES

There are a couple tweaks that I forgot about documenting in this update.  Though, the main updates to the script in this release are the following:

• Fixed overlapping index logic that was made wonky because of duplicate index changes (see That’s Actually A Duplicate Index for more about duplicate indexes)
• Adjusted missing foreign key logic to remove duplicate responses

INDEX ANALYSIS DOWNLOADS

Click on the following links to download the index analysis script:

• SQL Server 2005 - Index Analysis v9
• SQL Server 2008 - Index Analysis v10
• SQL Server 2012 - Index Analysis v11

Comments and MORE INFORMATION

Let me know if you run into any issues with these scripts, my e-mail address is in the header of all of the procedures.  If you are looking for a few hundred pages of indexing information, you can check out Expert Performance Indexing for SQL Server 2012.  Along with that, for indexing resources from my blog, check out the index resource page.

Lastly, want to see this script do anything special with indexes?  Leave a comment and I’ll see what I can do.

I am beyond excited to announce that I have been accepted to speak at the annual SQL PASS Summit 2013 in Charlotte, NC.  This will be my first time to speak at this event and I can’t wait!  The session I will be presenting is titled “How Active Directory Affects SQL Server”.

I have been working with SQL Server since the 2000 release and I have been working with Active Directory since it hit the shelf.  Prior to becoming a full time production DBA for Verizon, I was an AD guy and actually helped design and architect their worldwide Active Directory infrastructure.  I couldn’t be happier that this was the session that got picked from my submissions.  It’s a perfect melding of my two favorite technologies and I have a unique and rare perspective of both how they can work together and how they can work against each other.  For instance, have you enabled Instant File Initialization for your SQL Server instance?  Good!  Did you go back and make sure the setting was still there 90 minutes later?  If you haven’t then you better go double check it because Active Directory has a mechanism to not only change it, but persist that change.  If Active Directory was SQL Server’s kid then it just pulled a Ferris Bueller.

If you are planning to attend the conference make sure to add my session to your schedule.  I will explain the mechanism I alluded to in the above example along with all the other things good and bad that it can do, plus much more.  Here is the abstract:

If you have ever had a Kerberos or SSPI context error, then you won’t want to miss this session. SQL Server has a large surface area and Active Directory can influence a big part of it. I will discuss AD DNS configuration, Group Policy Objects, Kerberos (of course), and how all of them affect your SQL Server. By the end of the session you’ll have a check list of things to discuss with your domain administrator when you return to work.

Well it is that time of year again where the results of the PASS Summit 2013 session selections become public. This year was of course a very difficult one to get selected since (I believe) a record number of submissions were received -but thankfully I still managed to knock one session (out of four) into the back of the net. I would also like to take this opportunity to congratulate all the other selected Speakers (especially if this is your first time at the Summit) and commiserations to all those who were not successful on this occasion. I know the usual inquests and finger pointing will naturally ensue by a small handful of people who feel personally aggrieved that they weren’t successful, but believe me when I say that the process is as fair as I could expect, but not without room for improvement (more on this very soon in a follow up post).

Essentially you should take heart in the fact that whilst the selected schedule *might* have been better by your inclusion (yes we know you are awesome and have written a gazillion books and are a respected MVP/ MCM and Certified Guru), but at least someone else will get the opportunity to shine (and hopefully be awesome too). Ultimately (in my opinion) the Summit is about a team effort so we should be nothing but pleased for them and take our failures in good grace.

The Summit 2013 marks my third year at the biggest SQL Server Conference in the World as a speaker in any capacity, my second appearance giving a regular session and is the third regular session in total that I will have presented there (I gave two last year). It will also be my fourth as an attendee. I have been very grateful to have presented two lightning talks in total at the last two Conferences and I am always very humbled to get chosen in any capacity and grateful to the people who make this happen. I would also like to give a very quick shout out to my good friend Niko Neugebauer who I was hoping to co-present a Personal Development session with. Unfortunately this session was not chosen which is a big loss for the Summit in my humble opinion since (for me at least) Niko oozes #awesomesauce and I think our session would have ROCKED! Still perhaps another time and I keep my fingers eternally crossed that Niko (blog|twitter) is successful at next years Summit if not sooner. If anyone deserves payback for all his HARD community efforts and major recognition for his abilities, he is your man!

I shall be presenting on one of my favorite subjects -SQL Server Concurrency. Those of you who follow me a little closer than is healthy will already be aware, last year I presented a session titled “READPAST & Furious: Transactions, Locking, and Isolation (DBA-309)” which focussed on some of SQL Server’s crazy behaviors and concurrency basics. My session this year is called “Lock, Block and Two Smoking Barrels – SQL Server Concurrency” and will follow along similar lines looking at even more SQL Server crazy behaviors but focus in a little bit more depth on transactional processing theory and contrast SQL Server’s Pessimistic and Optimistic isolation levels and how they may help/ hinder/ make no difference to the concurrency of your environment. The “Two Smoking Barrels” referred to in the title is of course a direct reference to SNAPSHOT Isolation Level and READ COMMITTED Snapshot Isolation -the optimistic implementation of READ COMMITTED Isolation Level. Yes we are sure to focus more on these two optimistic bad boys. I hope you can manage to come to my session, I think we will have a blast!

If you are interested, the following is the session abstract:-

SQL Server is a high performance relational engine and provides a highly scalable database platform but due to its complexity and bad programming practices can be prone to serious concurrency problems, unexpected behaviors, lost updates and much more!
In SQL Server 2005, two optimistic concurrency mechanisms were introduced and touted as the solution to all our problems. Now in SQL Server 2012 (and beyond) even more have followed, but many challenges and problems still remain.

Let’s take a long look into the world of SQL Server concurrency and investigate Pessimistic and Optimistic isolation understanding how they work, when you should use them, and more importantly when they can go very wrong.

Don’t be staring down the wrong end of SQL Server’s two Smoking Barrels and join me for this revealing and thought provoking presentation.

It’s official… I will be presenting two sessions at PASS Summit 2013 in Charlotte, North Carolina.

I’m honored to have been selected and I can’t wait to see my #SQLFamily members who will be in attendance this year.

I don’t want this to sound like a cheesy acceptance speech at some awards show but I do want to thank all the people who I was able to bounce ideas and abstracts off of to get their suggestions and feedback.  Your comments and critiques were invaluable.

See you all in Charlotte!!!

Enjoy!! (Follow me on Twitter: @ColinStasiuk)

As I mentioned in the introductory post, in the Introduction to SQL Server Security session for Pragmatic Work’s Training on the T’s, I received a large number of questions that there wasn’t time to answer.  Instead of just a re-cap of all of the questions, instead I’ve opted to put together a post per topic.  Hopefully, this will help, not just those that attended the session, but also anyone searching for the same questions later on.

StoreD Procedure question

The second question in the list is:

Do we have easy way to grant all stored procedures execution in a single shot?

When I was a DBA working on SQL Server 7 and SQL Server 2000, this was a near constant pain.  I wanted to give users the ability to only execute stored procedures and wanted the least amount of hassle while doing it.  Back then, that meant a script that would run nightly and verify that all stored procedures were included in the database role for executing stored procedures.  Needless to say, while automated, the delay between deploying the procedure and granting access often got the better of me and we really needed something different.

In steps SQL Server 2005.  At this point, you might be thinking, “are you going to tell me that I’ve been able to do this since SQL Server 2005?”  Yes, since SQL Server 2005, there has been a permission that allows execution for all stored procedures to be assigned to any principal.

Exec All Stored Procedure Demo

Granting the permission to execute all stored procedures is rather simple.  It just requires knowing the user (or role) that needs the permissions and a grant statement, shown in listing 1.

--Listing 1. Grant exec to all stored procedures in a database

GRANT EXEC TO [User]
GO

Of course, the question that immediately follows the permissions to grant all stored procedures is one where the users need access to all of them, except these other ones.  There are a few ways this can be approached.  One useful manner to control the permissions is to scope the permissions to a specific schema.  The GRANT option, shown in listing 2, can allow the user to only execute stored procedures in the schema.  Or the user could be granted permissions for all schemas, as was done previously, and then the schema that shouldn’t be accessed has DENY permissions set.

--Listing 2. Permissions based on executing stored procedures in a schema

GRANT EXEC ON SCHEMA::SomeSchema TO [User]

DENY EXEC ON SCHEMA::AnotherSchema TO [User]
GO

There are numerous ways to achieve this, but I’ve always found it useful to know that you can scope permissions, such as EXEC, to the schema.

Summary

If you weren’t aware of the flexibility in assigning broad permissions to stored procedures, this post has hopefully enlightened you to some new security options.  Are there any permission combinations that you’ve tried to accomplish that haven’t worked out right?  Or, have you come up with an interesting permission solution that you think others might find useful?  Leave a comment and let us in on your solutions or problems.

I’m happy to officially announce that I’ll be presenting at this year’s PASS Summit in Charlotte, NC! I’ll be presenting my talk “What is a BI DBA?” as a Spotlight Session (90 minutes). This is the second time I’ll have presented at the Summit and I’m honored and beyond excited to have a Spotlight Session so we can cover more material!

This year the conference runs from October 15-18. I hope to see you at the Summit this year! http://www.sqlpass.org/summit/2013/

I’m happy to officially announce that I’ll be presenting at this year’s PASS Summit in Charlotte, NC! I’ll be presenting my talk “What is a BI DBA?” as a Spotlight Session (90 minutes). This is the second time I’ll have presented at the Summit and I’m honored and beyond excited to have a Spotlight Session so we can cover more material!

This year the conference runs from October 15-18. I hope to see you at the Summit this year! http://www.sqlpass.org/summit/2013/

Recently I saw the following error while working with Report Builder in SharePoint 2013:

“An error occurred while connecting to the data source.  Only the text-based query designer will be available.  The selected data extension DAX is not installed or cannot be loaded.  Verify that the selected data extension is installed on the client for local reports and on the report server for published reports.”

Huh?  I had tested the connection when I set it up, and knew it worked.  But no dice when using it for Report Builder.  Interestingly, my report executed but had empty results.

It turns out I had a connection with the wrong data source type.  Below is info re: setting up a connection in SharePoint for use with Report Builder.

Creating a Data Source for Report Builder Reports in SharePoint 2013

The first thing we need is a Report Data Source (RSDS) created so we can point to it (as a shared data source) when we create the report in Report Builder. 

The data connection can reside in a Data Connections library (if you prefer to centralize), or within the same library as the reports are stored (if you prefer to keep reports & data connections together).  For simplicity, the library shown below contains both reports and a data connection which is sales-specific.

Under Files on the ribbon, click the down arrow for New Document and choose Report Data Source.

Sidenote:  If you don’t see Report Data Source under the New Document menu, you’ll need to add the content type to the library first.  That’s a two-step process within the Library Settings…first, within the Advanced Settings, set “Allow Management of Content Types” to Yes…then back under General Settings choose “Add from existing content types” and select the various options that are related to BI data connections and/or reports (depending on what you need this library).

Set the various Data Source Properties.  Make sure to use the “Microsoft SQL Server Analysis Services” data source type, as shown here:

In the above screen shot, note the structure of the connection string.  For a bit more information about creating connections, see my blog entry titled “Comparison of Direct URL, BISM, and RSDS Data Connections for a Power View Report.”

The problem related to my original error was that within this RSDS connection, I had a data source configured with a type of “Microsoft BI Semantic Model for Power View” which – as the name implies – will work for Power View but not for Report Builder.  Problem solved when the type was changed to SSAS.

Here’s what the Report Data Source looks like when it’s been added to the document library:

Binding the RSDS Connection to a Report Builder report in SharePoint 2013

To associate the Report Data Connection it to a Report Builder report, click the ellipses next to an existing report name, then the ellipses one more time, then select Manage Data Sources.

Click the link on the name of your data source.

Ensure the “Shared data source” radio button is selected, and paste in the URL to the Report Data Source created previously.  Note this will have an RSDS file extension.

As I mentioned in the introductory post, in the Introduction to SQL Server Security session for Pragmatic Work’s Training on the T’s, I received a large number of questions that there wasn’t time to answer.  Instead of just a re-cap of all of the questions, instead I’ve opted to put together a post per topic.  Hopefully, this will help, not just those that attended the session, but also anyone searching for the same questions later on.

Temporary table question

The first question in the list is:

What permissions are required to create temporary tables?

I’ll be honest, this was a new one for me.  Not because it’s a bad question, but because I’ve never had problems trying to create temporary tables.  And also, I’ve never had complaints from users about not being able to create temporary tables.

That fact of the matter is, that as long as a user can connect to a SQL Server instance, the user is able to create temporary tables.  There are no special permissions.  A login with the most basic permissions has full rights to create and use temporary tables.  As it also turns out, there are no permissions for blocking or limiting temporary table creation.  This last bit would actually be useful, given the ability in other users I’ve seen before to fill tempdb with their temporary data sets.

Temporary Table Demo

Just to demonstrate that any login with access can create temporary tables, we’ll run through a quick demo.  Using the script in listing 1, create a new login on a SQL Server instance.

--Listing 1. Create new login

USE [master]
GO
CREATE LOGIN [TempTableUser] WITH PASSWORD=N'pass@word1'
, DEFAULT_DATABASE=[master]
, CHECK_EXPIRATION=ON
, CHECK_POLICY=ON
GO

Then connect to the SQL Server instance and run the create table script provided in listing 2.  You’ll see that without any issues, the user can create a temporary table.

--Listing 2.

CREATE TABLE #temp (Column1 INT)

Summary

Now this might not have been the deepest content on this blog ever, but it is a quick answer and proof that, as DBA’s we don’t have to do anything to allow temporary table access for users.  This question, though, makes me wonder, have you ever had issues with creating temporary tables?  Maybe someone out there has worked in a place where someone figured out how to block temporary tables create permissions.