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:

• SQL Load Generator (19 May, 2009)
• BPA Installs But Doesn’t Work (20 May, 2009)
• Are You Down With The BPA? (21 May, 2009)

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.

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.

THE GRANT question

The third question in the list is:

Can you please expound on the difference between “Grant” and “With Grant?”

When you GRANT something within SQL Server, that provides the permissions required to access the object, or securable, that is included in the statement.  For instance, a GRANT of read permissions on a tables allows the person to read from the table.

The WITH GRANT option, requires that Grant Fritchey (Blog | @GFritchey) show up for any of your permissions to work.  No, that’s not right.

In all seriousness, the WITH GRANT option allows the person who has been granted access to an object to GRANT that same access to other principals.  It is basically a method for delegating permissions when you have someone you can trust to manage those permissions.

Generally,  I’m not a big fan of this option.  The main reason is that, as a DBA, I’ve tended to be more concerned with who has permissions within a database compared to others.  The frequency in which I’ve seen others over grant permissions has been disheatening, especially when I was an FTE DBA and working on trying to constrain all of the run-away permissions.  That said, as a team member, we can’t always hold all of the cards and granting a junior DBA or team lead in another department the option to grant permissions through delegation can be especially handy.

Demo

To demonstrate how WITH GRANT works, let’s run a couple GRANT statements in AdventureWorks2012.  We’ll start by creating two new logins, WithGrantLogin1 and WithGrantLogin2, using the script in Listing 1.  The last part of the script includes a GRANT statement for WithGrantLogin1 provide access to Sales.SalesOrderHeader.  At the end of the GRANT, notice that “WITH GRANT OPTION” is included.  That is the clause that allows WithGrantLogin1 to have the permissions required to assign that GRANT to other users.

--Listing 1. Create new login
USE [master]
GO
CREATE LOGIN [WithGrantLogin1] WITH PASSWORD=N'pass@word1'
, DEFAULT_DATABASE=[master]
, CHECK_EXPIRATION=ON
, CHECK_POLICY=ON
GO

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

USE [AdventureWorks2012]
GO

CREATE USER [WithGrantLogin1] FOR LOGIN [WithGrantLogin1]

CREATE USER [WithGrantLogin2] FOR LOGIN [WithGrantLogin2]

GRANT SELECT ON Sales.SalesOrderHeader TO [WithGrantLogin1] WITH GRANT OPTION

To test this out, open two query windows and login to each of them with one of the two new users.  After doing so, run the script in Listing 2 in each of the two query windows, once for each of the users.  As you might expect, the query will succeed for WithGrantLogin1, but fails for WithGrantLogin2.

--Listing 2. Query Sales.SalesOrderHeader
USE AdventureWorks2012
GO

SELECT * FROM Sales.SalesOrderHeader

In the query window for WithGrantLogin1, run the script in Listing 3.  This script; which does not include the WITH GRANT OPTION, will GRANT read permissions to WithGrantLogin2 on Sales.SalesOrderHeader.  You should now be able to successfully run the query in Listing 2 for WithGrantLogin2.

--Listing 3. Create new login
GRANT SELECT ON Sales.SalesOrderHeader TO [WithGrantLogin2]

For the DBAs reading this, one of your first concerns with using the WITH GRANT clause for the GRANT statement is auditing how permissions have been assigned.  Fortunately, through SQL Server’s catalog views, this information is easy to discern.  With the catalog view sys.database_permissions, there are columns that identify both the grantee and the grantor for permissions.  Joining the view to sys.database_principals provides the names associated with the principal_ids and the names associated with accepting and granting permissions.  Using the query in Listing 4 and output in Figure 1, you can see that there is an audit trail of both who has and who granted permissions.

--Listing 4. Review permissions and the grantor
SELECT dp.class
,dp.class_desc
,dp.permission_name
,dp.state_desc
,OBJECT_NAME(dp.major_id) AS object_name
,dp2.name AS grantee_principal
,dp1.name AS grantor_principal
FROM sys.database_permissions dp
left JOIN sys.database_principals dp1 ON dp.grantor_principal_id = dp1.principal_id
left JOIN sys.database_principals dp2 ON dp.grantee_principal_id = dp2.principal_id

Figure 1. Sys.permissions output

Summary

When it comes to granting permissions, using the WITH GRANT OPTION might not be my favorite security command.  But it is a very useful option for delegating permissions, and potentially freeing DBAs from mindless tasks to more fun tasks, like performance tuning.  Properly leveraging this option is something that definitely should be a consideration.  What do you think?  Do you use the WITH GRANT OPTION in your environment?

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? 
   
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.