SQLServerPedia

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.

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.

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.

Earlier this month, I presented an Introduction to SQL Server Security session for Pragmatic Work’s Training on the T’s.  A video of the session is available at the Pragmatic Works website.  As a part of that session, I received a couple dozen questions about security that we didn’t have a chance to go over during the Q&A portion of the webcast.

Rather than write a short, possibly insufficient, answer for each question, I decided instead to put each question into a blog post.  That way, they’ll be a bit easier to track down, read, and get the information you want out of them.  These questions run from simple to complex.

Security Questions Asked

For the questions, I’ve made a few edits here and there for clarity.  Overall, though, these are all of the questions that I received.  As I answer the questions, I’ll add links to the posts.

1. What permissions are required to create temporary tables?
2. Do we have easy way to grant all stored procedures execution in a single shot? (live on 5-22-2013 10 AM CST)
3. Can you please expound on the difference between “Grant” and “With Grant?”
4. What is the difference between sysadmin and CONTROL SERVER Permission?
5. Whats the best role(s) to assign a junior DBA if you want them to see jobs, error logs, activity monitor, and run profiler, but not be able to manage jobs or kill processes?
6. Do the different ways of accessing SQL Server (Windows Athentication, SQL Server authentication, certificate or key) have differing authentication and authorization performance? If so, can you order the list?
7. Does 2012 provide TRUNCATE TABLE permissions?
8. How do you access the list of Server Securables?
9. How can i be sure a user is no longer used, so it can be deleted? Or when was the last time the logon was used?
10. How can you migrate users and passwords from one server to another server?
11. How would handle permissions for people that need full SQL Agent permissions (including being able to edit other peoples jobs) without giving sysadmin rights?
12. I work in a bank and federal inspectors are always looking at how secure are my databases. How would you prioritize security for the SQL Server?
13. What is the relationship between logins, credentials, and proxies? And why they were introduced?
14. What are some tips regarding roles other than sysadmin?
15. If an associate leaves, what is the best way to remove them from not only the logins but also all the databases?
16. What is the difference between db_datawriter and db_ddladmin?
17. How do I resolve the error: Drop Failed for User – The database principal owns a schema in the database, and cannot be dropped?
18. In 2008, a user at the DB level remains when you remove them at the Server level. Is this by design or perhaps ‘fixed’ in 2012 so that if a user is deleted at the server level she is also deleted from all database security?
19. Is the chart or dashboard in the Policy Based Management slide a standard one in SQL Server?
20. Is the grantor important? When, I as sysadmin grant rigths to a user I believe I come up as sa as grantor.
21. Is there a way to grant user to create alerts?
22. Is there a way to hide column/restrict from users?
23. Is there way to limit permission with date and time? For example, I want to give read access to a table within certain time period. For example, during business hours only.
24. Can you grant access through a job that runs at a specified time and remove this access through another job with the end period?
25. On a clustered server, can I grant access to a user to run SQL Trace (Alter SQL Trace permission) on specific databases but not all of them?
26. I have a login that does not have an entry in view sys.server_permissions. Shouldn’t every login has at least one entry, for “Connect SQL” as value for permission_name?
27. Temp tables: as a DBA, can i delete temp tables that are much too big?
28. Anyways to identify host name if an app is used through web, as well as mobile, to access SQL database? This question is just for tracking DB SELECT activity and the device/app usage from mobile?
29. What is the name of the recommended book again?

Last Words

Some of these post may inspire additional questions.  Please leave those questions in the comments and I’ll either write an additional post to cover them or re-direct you to a post or other resource that can answer the question.

For those that follow me on twitter (@StrateSQL), you’ll know that throughout the day I tweet out some links of things I find interesting.  These tweets include “Reading” in the message and are items I’ve read over the past few days, usually after hours when sharing would be less than useful, and spaced out to avoid spamming.  The content of the links usually pertain to SQL Server, technology, and career topics; which I think others would find useful.

Most Popular Link

The most popular link that I sent out this week doesn’t have the most safe-for-work content.  It’s basically a couple with a restaurant in Arizona that decided to take on the internet after a poor reception on some reality show they were on.  It was something like, “your restaurant sucks, let me show everybody what morons you are” – that’s not the name of the show, but it sums it up nicely.  Anyways, they take on the internet, and low-and-behold, the internet wins.

“This Is The Most Epic Brand Meltdown On Facebook Ever” bit.ly/15LrL4X <– just wow

— Jason Strate (@StrateSQL) May 15, 2013

Last week’s top 11! “Reading” Links

Along with the top link, here are the top twenty items relating to SQL Server, technology and careers that were sent out last week.  I had planned for a top 10, but number 11 is a review of my indexing book – so I’ve included it and recommend you read that… then read my indexing book.

1. Geek City: What gets logged for index rebuild operations? [52 clicks]
2. How a Single Tweet Can Change Your Career [41 clicks]
3. 5 SQL Server White Papers Every DBA Should Read [36 clicks]
4. Know the Difference Between Your Data and Your Metrics [30 clicks]
5. Consultants – Why pay more? [24 clicks]
6. SQL Server Customer Advisory Team – SQL Server Best Practices [19 clicks]
7. Why CONTROL SERVER Doesn’t Cut It [15 clicks]
8. Introducing our latest SQL Server 2008 Microsoft Certified Master – Wayne Sheffield [15 clicks]
9. Doing Your Job [14 clicks]
10. Distinct Aggregation Considered Harmful [14 clicks]
11. Book review: Expert Performance Indexing for SQL Server 2012 [13 clicks]

Other Stuff Shared

Of course, no week would be complete without a few off-topic links.  These have nothing to do with technology or your career, but they sure are entertaining and interesting (and a couple are pretty gross).

1. This Is The Most Epic Brand Meltdown On Facebook Ever [112 clicks]
2. 9 Incredible Objects That Prove 3D Printers Are Totally Worth it [27 clicks]
3. 6 Steps To Healing Yourself [16 clicks]
4. DDOS on the VideoLAN downloads infrastructure [14 clicks]
5. Thor 2 : The Dark World Trailer (2013) [12 clicks]
6. The worst break-up stories ever [11 clicks]
7. Timelapse: Changed Over A Few Decades via Google Earth Engine [9 clicks]
8. Michael Bay Is Why Transformers Got So Complicated [10 clicks]
9. Google Expected to Start a Competitor to Spotify [0 clicks]

Got something you think I should read and share, leave a comment below.  Also, if you want to see all of the links that were tweeted out last week?  Then follow the links on my tumblr blog or subscribe to it’s RSS feed.

Ever jump on a machine to connect to a SQL Server and find out… whoops! No tools.  For some reason, the client machine doesn’t have SQL Server Management Studio (SSMS) installed.  Now I’m not talking about not having the SSMS installed on the server, but on the local workstation that you need to connect to the server with.

No tools has happened to me many times and, in fact, happened to me this past week.  In the past, this would mean running around trying to get find the client’s SQL Server installation media.  Maybe downloading a 4+ GB ISO of the entire SQL Server installation from TechNet.  Or, hopefully, making sure the client had USB ports open on their machines and that I had my installation media backup drive with me.  A lot of things that were more work than one would like.

With the release of SQL Server 2012 Service Pack 1, there is a new option on the table.  Instead of downloading everything, you can download just the management tools through the Microsoft® SQL Server® 2012 Service Pack 1 (SP1) Express download page.  Just select either SQLManagementStudio_x64_ENU.exe or SQLManagementStudio_x86_ENU.exe from the download options and you’ll have the full version of SSMS at your fingertips; in about a quarter the size of the full installation download.

For those that follow me on twitter (@StrateSQL), you’ll know that throughout the day I tweet out some links of things I find interesting.  These tweets include “Reading” in the message and are items I’ve read over the past few days, usually after hours when sharing would be less than useful, and spaced out to avoid spamming.  The content of the links usually pertain to SQL Server, technology, and career topics; which I think others would find useful.

Most Popular Link

I wasn’t too surprised by this week’s most popular link.  Negotiating salaries always tends to lack fun and usually were across the desk from someone who’s done this more often that we have.

Reading: “How To Negotiate Your Salary” onforb.es/VkIWU6 #career

— Jason Strate (@StrateSQL) May 8, 2013

Last week’s top 20 “Reading” Links

Along with the top link, here are the top twenty items relating to SQL Server, technology and careers that were sent out last week. Usually, I do a top ten, but I really like the items that were in the top twenty.

1. How To Negotiate Your Salary [51 clicks]
2. Microsoft to fold in-memory database technology into SQL Server Next [44 clicks]
3. The Saddest Thing I Ever Heard a Manager Say [40 clicks]
4. The secrets of body language: why you should never cross your arms again [34 clicks]
5. LinkedIn Labs | InMaps|Visualize your LinkedIn network [30 clicks]
6. SQL Server 2012 partitioned table statistics update behavior change when rebuilding index [26 clicks]
7. SQL Server Alerts with Text Messaging from SQL Server Database Mail [25 clicks]
8. The Microsoft Certified Solutions Master: Data Platform Knowledge Exam is available today! [23 clicks]
9. Microsoft Goes After 3 Big Data Myths [21 clicks]
10. Microsoft Azure overtakes Amazon’s cloud in performance test [20 clicks]
11. How to Overcome Perfectionism [20 clicks]
12. Why You Should Write Daily [19 clicks]
13. Analyzing Storage Performance [17 clicks]
14. Career Advice: How to Prepare for a Conference [14 clicks]
15. Using Filtered Indexes to Improve Performance [13 clicks]
16. Understanding the SQL Server 2012 Licensing Model [13 clicks]
17. Drunk falling into a worm [13 clicks]
18. The Ten Commandments of SQL Server Monitoring [13 clicks]
19. 5 Killer Ways to Improve your Writing Right Now [12 clicks]
20. SQL Server 2012 PDW extends big data capability [11 clicks]

Other Stuff Shared

Of course, no week would be complete without a few off-topic links.  These have nothing to do with technology or your career, but they sure are entertaining and interesting (and a couple are pretty gross).

1. How to use a semicolon – The Oatmeal [23 clicks]
2. Funny yearbook photos [17 clicks]
3. 10 Mouthwatering Pick Up Lines To Help You Flirt With Food [16 clicks]
4. LEGO® CUUSOO | Avengers: Iron Man Mark VII Armor [15 clicks]
5. This Lego Mashup of Volkswagen and Star Wars Works Surprisingly Well [15 clicks]
6. I Want These Daft Punk Lego Minifigs More Than Their New Album [15 clicks]
7. Old Spock battles New Spock in the greatest car commercial ever [15 clicks]
8. What to watch on Netflix – Miramax [9 clicks]
9. Roses are red, bacon is red [8 clicks]
10. #Bacon Houses: So Much Uglier Than You’d Imagine [8 clicks]
11. Bacon Cheeseburger Fatty – smoked in a Bradley beef, cheddar [8 clicks]
12. Mark Hamill Wants Less CGI In Star Wars VII | The Mary Sue [7 clicks]

Got something you think I should read and share, leave a comment below.  Also, if you want to see all of the links that were tweeted out last week?  Then follow the links on my tumblr blog or subscribe to it’s RSS feed.

Welcome to this Friday’s reblog summary post.  The aim of these posts is to bring some old posts that newer readers may not have seen back to the forefront.  As with many technology blogs, just because a post is old, doesn’t mean it doesn’t still have value.

Some posts from years gone by that you may have missed are:

• Understanding Query Filters and Clauses (5 May, 2006)
• Room with a View (6 May, 2006)
• How to Sort SSMS Project (8 May, 2006)
• This blog entry brought to you by the procedure “sp_MSForEachDB” (9 May, 2006)
• Sorting Project Files in SSMS (4 May, 2009)
• Snapshots of Index Contention (4 May, 2009)
• Querying for Parallelism (5 May, 2009)
• Finding Objects In Schema (6 May, 2009)
• I Didn’t Receive My Subscription (7 May, 2009)
• More Parallelism (8 May, 2009)
• Searching for Plans (3 May, 2010)
• Can You Dig It? – Determining Index Plan Usage (4 May, 2012)
• “Thou Shall Not Shrink the Database” #TSQL2sDay (8 May, 2012)

Do you have something from years gone by that was posted during this week?  If so, leave a comment below and we’ll give some new life to good knowledge.

I’m a little late getting the information from my Fargo SQL Saturday session posted, titled ”Discovering the Plan Cache“.  But it’s better a little late that never.

Even though it was the end of the day, the session had great turnout and lots of good questions.  Reviewing my session evals, I saw quite a few high marks from people.

Below I’ve included the slide deck that you can flip through in this post, or you can download the deck with the demos included (Discovering the Plan Cache (SQLSat175)).

Additional information: http://www.jasonstrate.com/resources/plan-cache/

Last Thursday, I delivered a presentation for Pragmatic Works Training on the T’s. The session for last week was Introduction to SQL Server Security, a part of last month’s series of introductory presentations.

The abstract for the sessions is:

One of the most often overlooked feature sets of SQL Server is it’s security features. The focus is often on what type of data, how to store the data, and how to get the data out of SQL Server. As DBAs, we also need to be consider how to lock down the data and implement security. In this session, we’ll investigate the basic components of security within SQL Server. Also, we’ll review some practices for that you may want to adopt to secure your environment.

Session Materials

For the presentation materials, you can download them (Introduction to SQL Server Security) or if you just want to flip through the slides, you can do that below.

Additional information: http://www.jasonstrate.com/security-resources/

Twitter Comments

The response from the session was pretty good. Here are a few of the comments that were received after the session.

Thanks to Jason Strate @stratesql for an excellent webinar on security @pragmaticworks@pwfreetraining

— Thomas Sykes (@sqltomato) May 2, 2013

@stratesql was in your SQL SECURITY presentation today.Great job!Took good notes. I think I’m a little smarter!

— CAGrant-Willis (@DBADiva) May 2, 2013

Great presentation on security by @stratesql,@pragmaticworks.Good advice: use Control Server instead of sysadmin. #sqlserver

— Erika Bricker (@DancesWChickens) May 2, 2013

Thanks a lot to everyone that listened in. It’s always a pleasure to present to people and it’s great when people get something from the presentation.  If you have questions on this session, please leave a comment below.