Horkay Blog
The postings on this site are my own and do not represent my Employer's positions, advice or strategies.
Monday, 22 February 2010

Well, it continues to be a pain in my ass, the inability to grant truncate table permissions on a table to specific users without giving them excessive rights.  SQL 2K5, no solution, SQL 2K8 no solution, well we did get a bunch of other stuff, so we'll continue to suffer.  I originally posted an article on Granting Truncate Table permissions in 2008, here is that link.  Recently the need has arisen to have the ability log failures for the solution.  The code was modified by Holland Humphrey and I'm posting the solution again with his updates.

The solution basically uses a schema, dba, to hold 3 tables for a stored procedure which is used to truncate tables.  The 3 tables, Truncate_List, Truncate_Audit and Truncate_Fail.  A user is granted permissions to a stored procedure in the dbo schema that performs the work and verifies that the table is authorized for truncate, and logs the success to truncate_audit and the failure to truncate_fail.  One could argue that truncate_audit could hold both success and failure, but I'll leave any possible enhancements up to those that desire to do so.

The original inspiration for this actually came from the Oracle group where I work.  Oracle has the same problem as sql "Grant Truncate on {table} to {User}"...so simple....but it's not there in SQL or Oracle.  I basically translated the Oracle solution into SQL Server.

Every upgrade we do from SQL 2000 to 2K5 or 2K8 uses this solution and allows us to reduce our permissions set to the lowest possible necessary.

Here are the scripts for the solution.

1.  Create a Schema Called DBA {if you prefer something else, adjust the tables and proc}.

2.  TruncateSolution_CreateTables.txt (4.67 KB)

3.  TruncateSolution_CreateProc.txt (4.34 KB)

Monday, 22 February 2010 14:30:36 (Central Standard Time, UTC-06:00) | Comments [0] | SQL Server#
Thursday, 18 February 2010

Starting in December I purchased the following books.  Most of these were related to new projects where I work and knew I'd need to sharpen a few skills, Encryption, SQL Express, Merge Replication and security.

  • Beginning SQL Server 2008 Express for Developers: From Novice to Professional - Robin Dewson
  • Enterprise Data Synchronization with Microsoft SQL Server 2008 and SQL Server Compact 3.5
  • Mobile Merge Replication - Rob Tiffany
  • SQL Server MVP Deep Dives
  • Expert SQL Server 2008 Encryption - Michael Coles, Rodney Landrum
  • SQL Server Forensic Analysis - Kevvie Fowler [bought but not reviewed]

Reviews:

Beginning SQL Server 2008 Express for Developers: From Novice to Professional - Robin Dewson

This book was a disappointment, but I knew when purchasing it that it was probably not going to be of much help; but I had to start somewhere.  We're preparing to migrate from MSDE to SQL Express 2K8 in 10,000 locations; I was involved in the intial deployment (and patchings) of MSDE and I really need to figure out how to avoid the gotchas with SQL Express; this book wasn't it.  Over-all it's a good book, but it's not meant for an existing SQL Server DBA, I would expect even a Jr. DBA to already be familiar with the content.  It truly is meant for an access developer who wants to learn SQL Express; I gave it to our local access programmer at work {though it's actually a she and she's not in IT or a programmer, seems that is where most access db's begin life}, maybe she'll develop a new career path, we seriously need more women in IT, I digress.

Enterprise Data Synchronization with Microsoft SQL Server 2008 and SQL Server Compact 3.5 Mobile Merge

This book was great.  We're deploying Merge replication with up to possibly 10,000 differnt locations to SQL Express 2k5 and that is why I purchased this book.  While not an exact guide or road map for what we're doing [we're not using SQL Server compact edition], most of the concepts apply.  It has really been helpful, we're still in the planning and proof of concept stages, so it remains to be seen if we'll continue with this design at work, but I feel this book helped us greatly.

SQL Server MVP Deep Dives

This book was great.  I kind of avoided it at first, as I don't like books that too many people jump on as good, but this one lived up to that promise.  Very few parts of the book were lame ! [there's a glowing recommendation].  I put a sticky next to each new thing I learned and when I was done, i'd say there were at least 15-20 stickeys across the book, that's worth the money I paid for it.  It's also nice as you can read this book in quick "snippets" flipping to what subject interests you and what you have time to read.

Expert SQL Server 2008 Encryption - Michael Coles, Rodney Landrum

This book is good, though a bit dry, I'm still not all the way through it...reading about encryption is not what I'd consider an exciting topic, but it is a skill i'm going to need and it was seriously lacking.  We're preparing to bring in a 3rd party hardware device to provide consistent encryption and key management across the entire enterprise, which is why I needed this book.  So far it's been a great help, as I don't feel like a complete moron as I attend the endless meetings on implementing enterprise encryption where i work.

SQL Server Forensic Analysis - Kevvie Fowler

I haven't got to reading this one yet.  I'm looking forward to it, as security has become a major focus in ways I couldn't even imagine 2 years ago.  I thought the description of this book is interesting, as it approaches security from the aspect of determine what was compromised, from that I hope to learn better practices in security data....I'll update on that later.

Thursday, 18 February 2010 15:55:23 (Central Standard Time, UTC-06:00) | Comments [0] | SQL Server#
Tuesday, 16 February 2010

 

This has to be one of the worst ever toys for kids.  There are some positive reviews out there for this, and I'm convinced those are paid reviews or something.

This toy does not work, it requires heavy objects to weigh it down and it is near impossible to get the car to run on the rope.

A huge disappointment for the kid awaits with this toy.

I don't blog product reviews, but this one sucks so bad i had no choice.

I'd pay money for someone to show me this toy working...

Tuesday, 16 February 2010 07:36:46 (Central Standard Time, UTC-06:00) | Comments [0] | Web_Blog#
Thursday, 04 February 2010

January and February are peak times for my company's business; now I can start to focus on new things, here is what I learned during peak and where 2010 will focus:

  • Capacity management is the 2nd most important thing dba's do.
  • Performance tuning and monitoring is the most important thing (during peak)
  • Locking and Blocking is the biggest scale issue i regularly see.
  • Mirroring on SQL Server standard edition sucks (especially for geographically disperse sites).
  • Mirroring on SQL Server enterprise edition rocks (though the cost factor is prohibitive).
  • Informatica is no better an ETL Tool than SSIS
  • Oracle and SQL Server are a pain in the ass to make work together (Oracle DBAs are'nt much help either)
  • 1 terrabyte of disk space is almost enough !
  • Re-indexing and defragging is analogous to politics and religion and DBA's should be barred from discussing the topic
  • Encryption is a pain in the ass.
  • Surprise - Merge replication actually works and is not near the PIA I thought
  • 6gb of RAM is never enough
  • Polyserve is not worth the effort
  • VM Ware with VMotion is awesome
  • VM Ware is a good alternative to Polyserve
  • Hyper-V is not there yet
  • SQL 2008 R2 licensing sucks
  • SQL 2000 is finally no longer meeting the business requirements and we may finally get funding to upgrade the remaining instances !
Thursday, 04 February 2010 15:12:30 (Central Standard Time, UTC-06:00) | Comments [0] | SQL Server | Web_Blog#
Search
Popular Posts
Unpatched Vulnerabiltiy discovered ...
Spring Fornicator brewed...
SQL Server Security, not where it n...
Calculate Stock Break Even Price
Useable Space
Recent Posts
Archive
August, 2017 (1)
June, 2017 (2)
May, 2017 (2)
April, 2017 (2)
March, 2017 (1)
February, 2017 (1)
December, 2016 (2)
October, 2016 (2)
September, 2016 (1)
August, 2016 (1)
July, 2016 (1)
March, 2016 (2)
February, 2016 (3)
December, 2015 (4)
November, 2015 (6)
September, 2015 (1)
August, 2015 (2)
July, 2015 (1)
March, 2015 (2)
January, 2015 (1)
December, 2014 (3)
November, 2014 (1)
July, 2014 (2)
June, 2014 (2)
May, 2014 (3)
April, 2014 (3)
March, 2014 (1)
December, 2013 (1)
October, 2013 (1)
August, 2013 (1)
July, 2013 (1)
June, 2013 (2)
May, 2013 (1)
March, 2013 (3)
February, 2013 (3)
January, 2013 (1)
December, 2012 (3)
November, 2012 (1)
October, 2012 (1)
September, 2012 (1)
August, 2012 (1)
July, 2012 (4)
June, 2012 (3)
April, 2012 (1)
March, 2012 (3)
February, 2012 (3)
January, 2012 (4)
December, 2011 (3)
October, 2011 (2)
September, 2011 (2)
August, 2011 (8)
July, 2011 (4)
June, 2011 (3)
May, 2011 (3)
April, 2011 (1)
March, 2011 (2)
February, 2011 (3)
January, 2011 (1)
September, 2010 (1)
August, 2010 (2)
May, 2010 (2)
April, 2010 (3)
March, 2010 (1)
February, 2010 (4)
January, 2010 (1)
December, 2009 (3)
November, 2009 (2)
October, 2009 (2)
September, 2009 (5)
August, 2009 (4)
July, 2009 (8)
June, 2009 (2)
May, 2009 (3)
April, 2009 (9)
March, 2009 (6)
February, 2009 (3)
January, 2009 (8)
December, 2008 (8)
November, 2008 (4)
October, 2008 (14)
September, 2008 (10)
August, 2008 (7)
July, 2008 (7)
June, 2008 (11)
May, 2008 (14)
April, 2008 (12)
March, 2008 (17)
February, 2008 (10)
January, 2008 (13)
December, 2007 (7)
November, 2007 (8)
Links
Categories
Admin Login
Sign In
Blogroll