November Snuck Up on Me

Tags: ,
Comments: 1 Comment
Published on: November 1, 2010

I should have gotten this post out last week with another follow-up this week.  Well, looks like I will have to get a couple out this week to try to remind people.  This month SSSOLV has moved up 1 week due to Connections being in town this month and PASS being next week.  With Connections being in town this week, I was able to convince Steve Jones (Blog | Twitter) to come and speak to us.  It works well if we move our regularly scheduled meeting from the second Thursday to the first Thursday of the month.

Steve is going to talk to us this month about the modern resume.  And for those that don’t know who Steve is, Steve is a co-founder of SQLServerCentral and is currently the Editor of SQLServerCentral.

We will be hosting this presentation via LiveMeeting as well.  Here are the details for joining the meeting virtually.

Attendee URL: https://www.livemeeting.com/cc/usergroups/join?id=PPBZ4S&role=attend

Meeting ID: PPBZ4S

The meeting will be from 6:30 – 9 PDT at the Learning Center ( 777 N. Rainbow Ste. 250 Las Vegas 89107).  I am still working on the evite at this time (the contacts list is unavailable currently).

We hope to see you there.

Bucket List

Tags:
Comments: 3 Comments
Published on: October 26, 2010

I have no intentions of kicking the bucket – ever.  I have no intentions of getting old either.  We’ll see how all of that works out.

Bob Pusateri (BlogTwitter) posted his bucket list this morning and it seemed like an interesting topic for an early day such as this.  You can see his Bucket List here.

Pilot an aircraft – preferably something very fast.  I would even settle for a ride in a cockpit of an F-18 (Blue Angels) or F16 (Thunderbirds).  I know – the F-16 is a single pilot cockpit.  The point is – I want to ride up front in something really fast.

Skydiving – at least once.

Start my own Business.  It would be nice someday to be a consultant.

Write A Book.  Someday it would be nice to at least contribute to a publication, preferably about SQL Server or something career minded.

European Vacation.  I would like to take my family on a vacation to Europe.  I have been and lived there.  It would be good for the family to see different parts of the world.

Speak at the Summit.  I think I will practice a lot before that happens.  I will start submitting sessions for Summit, beginning with 2011.

Be Teleported.  I don’t want to experience the same things as we see in Willy Wonka.  I would rather it be more like Star Trek teleportation.

See Bears.  I would like to attend a game at Soldier Field and watch the Bears play.  Even better if I could take a couple of my sons with me.

My SQLSat54 Experience

Comments: 1 Comment
Published on: October 25, 2010

In case you hadn’t heard SQLSaturday #54 was Oct 23 in Salt Lake City.  It was a dreary opening to the Deer hunt, and a good morning to be inside to learn about SQL Server.  I was there to make my first presentation at a larger scale event (than a UG setting).

I got to meet some cool people and to learn a few things while I was there.  I enjoyed the time I had to chat with Denny Cherry (Twitter | Blog), Nic Cain (Twitter | new twitter | Blog), Bill Pearson (Twitter), TJay Belt (Twitter | Blog), Pat Wright (Twitter | Blog), Meredith Ryan-Smith (Twitter), and Randy Knight (Twitter) as well as some others.

I picked up a few pointers for when we do a SQLSat in Las Vegas (like order overkill on the pizza).  It is also encouraging after having participated in a SQLSat to just get out and do it.  Nevermind the concerns about this or that, just do it.  So we will be formally working toward a SQLSat in LV very shortly.  I will be presenting that to the UG in LV first though.  We had tinkered with the idea – but now it is time to do something about it.

If you have never participated in a SQLSat, I highly recommend that you do.  I got some good networking in, some knowledge transfer, and I got to give my presentation (which needs a little more tuning).  That brings me to another thing that I learned.  It seems to be a trend that it is difficult to get audience participation no matter where you go (first hand experience as well as from what I have heard).  As presenters, many thrive on participation.  For my presentations – participation is crucial.

I hope to see you at future SQL Saturdays.

Oh,  and many thanks to Tjay Belt and Pat Wright for organizing this event.  Great Job.

October 2010 TSQL Tuesday Reminder

Tags:
Comments: No Comments
Published on: October 6, 2010

Holy Cow, another month has flown by without much of  a hint.   We now have upon us another TSQL Tuesday.  If you hadn’t heard or haven’t seen the twitter announcement, you can find it to be hosted by Sankar Reddy (Blog).  The theme this month is all about myths (this should be a fun one).  There are plenty of myths to go around, so get your myth and debunk it this month as a part of the Blog Party.

Oh and by the way, the Party is Oct 12th.

It’s Implied

Tags:
Comments: No Comments
Published on: October 5, 2010

This has been on my radar now for a little over a week.  I ran across a  request in the forums to assist with permissions and decided to test some things.  The gist of the request was to disallow a user from inserting into a table and still have execute permissions on a stored procedure that may need to insert data into the same table.

The Setup

To work through this, I decided to create a user, grant that user specific permissions, and create a stored procedure to which I would grant that user I created execute permissions.  All of this is pretty straight forward stuff just to demonstrate the principle.  I will also be using a database previously setup just for testing purposes.

So let’s just start by creating this user that we will be using to test our permissions.

[codesyntax lang=”tsql”]

[/codesyntax]

We can see that the user creation is pretty straight forward and we are immediately adding the user to the denydatawriter role for the database.  Now for the next couple of items that we will be using in this little experiment, a table and a stored procedure.

[codesyntax lang=”tsql”]

[/codesyntax]

[codesyntax lang=”tsql”]

[/codesyntax]

And the last little bit of the setup is to grant our TestRW user execute permissions on this stored procedure.

[codesyntax lang=”tsql”]

[/codesyntax]

That is it for the initial setup.  I think this is a pretty straight forward setup to demonstrate these permissions.  The next item of business is to actually create these objects and to test.

Testing – 1,2,3

The testing I have setup for this is also pretty straight forward.  I will execute the proc and then select all records from that table that was created.  If successful, then there will be no records.  If (per the requirements), we see records then the setup has failed and we need to explore other options or find a reasonable conclusion that is preventing us from achieving success.

[codesyntax lang=”tsql”]

[/codesyntax]

Upon executing that, you should see the following results at this point.

Well, would you look at that.  Having denydatawriter on the table, and granting execute permissions on the proc has allowed us to insert data into the table.  Let’s take it one step further now – what if we explicitly deny update, delete, and insert permissions on the table to this user?  We can do that with the next script.

[codesyntax lang=”tsql”]

[/codesyntax]

With the new permissions added (or subtracted depending on how you look at it), we can test once again – using the same script as in the first round.

Well, now we can see that explicitly denying update, insert and delete on the table is no more effective than adding the user to the denydatawriter role.  Are the deny permissions even working at this point?  Well, this verify that.

[codesyntax lang=”tsql”]

[/codesyntax]

Now, I had forgotten to mention this but it is very critical to the proper testing of this routine.  The objects created were created by a different user than the TestRW account we created.  Once the objects are created, then we MUST login to the Database Server with the new login and we must run the queries from that user account.  To recap – grant permissions and object creation are done from an account other than TestRW.  Running the test queries is done through the TestRW account.  (For ease of testing, you may also want to grant access to the db_datareader role on the TestRW user.

The results of running that simple Insert statement should yield the following result.

So we know that the permissions are working but that the stored procedure is circumventing those explicit permissions.

Conclusion

Well, this is not actually a failure.  This is by design.  This is called implicit permissions.  If I am going through the trouble of granting execute permissions on a stored procedure to a specific user, then the system interprets that as the stored proc permissions override any explicitly set permissions.  Is there a way around this?  You could setup triggers on a table to prevent this from happening or you could remove the execute permissions for that user on each proc.  I also think this helps to underscore the importance of not just granting execute to all stored procedures in a database to a user.  This simply over-permissions that user and they may be able to do more than you wish for them to do.  Another option is to add code to each stored procedure that would check for group memberships and error out if the user is a member of the denydatawriter role (for instance).  This would be very similar to the way that MS prevents users from executing certain system stored procs from being executed by users who do not have adequate permissions.

SQLSat 54

Comments: No Comments
Published on: October 1, 2010

Wow, I have really been kinda slow in the blog department for a couple of weeks now.  I have a few things in queue that i will be letting out within the next week.

I got notice today that I have a presentation on the slate for SQL Saturday #54 – woohoo.  I was honestly unsure of whether I would be able to present.  I was hopeful but this is a new venture for me.  I hope to get to meet some of you at SQL Saturday 54.

You can find more info on it here.  You should be able to read my little blurb for the presentation as well as find a bit of a bio about me there.

BTW, I am a bit stoked to be able to do this in the community.

TSQL Tuesday Indexes and Blobs

Comments: 1 Comment
Published on: September 14, 2010

Woohoo – chugga chugga chugga chugga – woo woo.  The train is pulling into town once again for another installment in the TSQLTuesday Blog party.  Michael Swart is hosting and has asked us to post something ado about indexes.

What to do What to do

I thought long and hard about the topic this month.  I really wanted to discuss something that would be of use.  I also wanted to talk about something that may not have been covered by somebody else.  The topic of indexes and combination of blog entries this month should be comprehensive and covering in many aspects of Indexes.  We all know that there are Clustered Indexes and Non-clustered indexes, and then the other subtypes of indexes that I am sure have been covered by others in this party.

Then it hit me.  One day while helping in the forums I ran across a post requesting information about how to find what columns were LOB columns in the database.  I knew I had some past blog posts about various aspects of LOBs, but I had never broken it down to find the columns in the LOB.  Even better was that I wanted to know what columns were in what index that were also a BLOB.  Oooohh, spine tingling idea – I could cover Blob Index Columns.

Restore from Backup

Not really, but every once in a while we have to dig back in the backups to recover data from the past.  I had to pull up an article from the past in order to get to the script that would benefit me in this venture.

Before I go to lengths to post that script, let’s start with the script that was provided as a response to that initial question.  This is a straight-forward script that provides the answer to the question posed.

And here is the script that I thought could prove useful for this situation:

That script can be found in my post here.

With that script in hand I figured a few short customizations could prove useful to help find the additional column info.  Now, I must admit, this is still a work in progress for which I want to tune it and tidy up the script substantially – where possible.  My point for now is the concept of using it to retrieve the indexed columns that are blobs and to see that extra detail about it.

I am going to make a few modifications that include adding a CTE, as well as adding the following snippet to both sides of the union, as well as a few additional columns.

This is what I came up with:

This script will not return as many rows as the first script shared.  That should be an indicator that not all LOB columns are in an index.  Here we are looking for those that may be causing an extra performance impact due to the inclusion in an index.  It is always good to know what is going on in a database.  Something like this is very helpful for the newly hired DBA when trying to get to understand the databases which need to be supported.  This is also helpful when the need is to document a database.  This script may also spit out multiple rows for the same column in the same index.  This is due to that column being split into two different allocation unit types.  I feel this is good to know as well.

Here we can see just a small sample of the output from this script.

This little snippet is from a CRM database, and you can see that the clustered index in this image has three columns in it that are BLOBS and they are all three being stored as an LOB_Data allocation unit.

Conclusion

Even though this was a quick and dirty entry on the topic, there is much to be gained from the little insight this script can provide.  I would recommend that people find out what indexes are holding that LOB data.  It’s better to know than to not know.

And I go by Indexes – not indices.  Indices for me represent more of a financial term than a logical lookup term.

September TSQL Tuesday

Tags:
Comments: 1 Comment
Published on: September 8, 2010

It is hard to believe that time is flying like it is.  Already it has been a month since I hosted TSQL Tuesday.  This month the party is being hosted by the Database Whisperer, Michael Swart (Blog).  We have been given the challenge to write about indexes err indices err indexes – well, you get the point.

Don’t forget to follow the hashtag (#TSQL2sday) on twitter.

I hope this helps somebody remember that the blog party is less than a week away at this point.

September S3OLV

Tags: ,
Comments: No Comments
Published on: September 8, 2010

Here is another short reminder about this months UG meeting in Las Vegas.

We will be meeting Thursday at the same DBA Place, Same DBA Time.

You can find more details here.  That is my blog post from last week on the topic.  We will be listening to Stacia Misner talk to us on Power Pivot.

The meeting will be broadcast over livemeeting.  Hope you will be able to attend either virtually or in person.

September S3OLV Meeting

Tags: ,
Comments: 1 Comment
Published on: September 3, 2010

Another month has crept up upon us.  It is time once again for the Las Vegas User Group /PASS Chapter to meet.  This month we will have Stacia Misner (Blog | Twitter) present to us: “PowerPivot – Why Should You Care?”

The meeting place is the usual location at the Learning Center

Learning Center of Las Vegas
777 N Rainbow, Ste 250.

We are also making this group meeting available via Livemeeting once again.
All guests can attend at:
https://www.livemeeting.com/cc/UserGroups/join?id=C7N873&role=attend

Meeting ID is C7N873 (just in case you need it).

We hope to see you virtually or in person.

Also, S3OLV is now on twitter.  Follow @S3OLV

«page 3 of 6»

Calendar
August 2019
M T W T F S S
« Jul    
 1234
567891011
12131415161718
19202122232425
262728293031  

Welcome , today is Saturday, August 24, 2019