Comments for Cleveland DBA

Comment on Pitching Pebbles – Using Certificates to Grant Privileges by Colleen M. Morrow

Colleen M. Morrow — Fri, 17 May 2013 22:38:50 +0000

Greg - I think the thing to do would be to create the certificate in the utility database, back it up to a file, use that file to create the certificate in the master database. Then create a login from the master certificate and create an associated user in the utility database, and grant execute on the stored proc to the user. Jonathan Kehayias goes through a demo here. His example deals with multiple user databases, but it should work for what you're trying to do.

Comment on Toolbox by Kevin Zakoski

Kevin Zakoski — Tue, 14 May 2013 01:28:15 +0000

Great tool, even as a Powershell newbie like myself I was able to convert it to Trusted Autentication and it’s now collecting data from approx. 300 SQL boxes in our environment.

However, the one problem I’m having and haven’t found a solution for is the CommandTimeout. The default of 30 secs causes an exception error every now and them, but I can’t find any way to set the value higher. There have been some posts around the subject but everything I try errors out with an invalid argument.

Do you know of a way to set the value higher and can you provide an example, if possible?

Thanks,

Kevin Z.

Comment on Composite indexes – Does column order matter? by Erik Grob

Erik Grob — Mon, 06 May 2013 19:57:14 +0000

Hi Colleen,

One thing that I’d suggest would be to perform the test on a Table and not a Heap – (1) We’ll get more predictable and repeatable results testing things out in a “lab environment” when we have a clustered index and (2) it’s good to enforce best practices in the postings we create, so that others don’t run into issues resulting in [the specific case] the use of HEAPS.

Heaps are great for loading large amounts of data into an ETL staging table, but when performing due diligence or doing lab work, tables with a clustered index is something I would recommend.

Lastly, I agree with Bruce. In fact, I just answered an interview question with – ‘It depends’, regarding column ordering and index usage, and I referenced the same reasoning behind my answer. SQL Server is constantly making decisions on the fastest route to take, and complexity and page allocation of branches in a b-tree index is something taken into consideration.

Comment on Pitching Pebbles – Using Certificates to Grant Privileges by Greg Faulk

Greg Faulk — Fri, 03 May 2013 17:01:47 +0000

Could you extend the example to demonstrate how to sign a stored procedure that is not stored in the master database, but is instead stored in a DBA utility database? I ask because the ‘CREATE LOGIN … FROM CERTIFICATE’ statement requires the certificate to reside in the master database. However, ‘ADD SIGNATURE …’ does not allow the certificate to reside in another database. I tried to work around the problem by creating the certificate a second time in the utility database but that resulted in a different certificate thumbprint so the permissions weren’t granted. Is the solution to export the certificate from master and then import it to the utility database so as to preserve the original certificate thumbprint?

Comment on PAGE_VERIFY – Checksum vs. Torn Page Detection by Writing Checksums by Reindexing | FradenSQL

Writing Checksums by Reindexing | FradenSQL — Tue, 30 Apr 2013 13:26:37 +0000

[...] a checksum on the page. To do that I used the method Colleen Morrow used when she discussed switching from torn page to checksum recovery models. As she shows, the checksum is stored in the m_tornBits field of the page header instead of a [...]

Comment on Event Notifications 101 – Intro to Event Notifications by Colleen M. Morrow

Colleen M. Morrow — Thu, 18 Apr 2013 01:20:34 +0000

I hope so, too, Mike. And I’m definitely heading over to check out your posts!

Comment on Building a SQL Server Inventory – Part 2 by Colleen M. Morrow

Colleen M. Morrow — Thu, 18 Apr 2013 01:07:01 +0000

Shannon – Yep, the end of that line went AWOL. It’s been updated, thanks for pointing it out!

Comment on Composite indexes – Does column order matter? by Javier Villegas

Javier Villegas — Wed, 17 Apr 2013 16:58:07 +0000

Hi, I think you should force not to use Cache on your test. I have similar conclusion until I forced NO_SQL_CACHE.

Comment on Building a SQL Server Inventory – Part 2 by Shannon

Shannon — Wed, 17 Apr 2013 16:57:43 +0000

Is there something missing within the inner query around line 405? The where statement reads “AND backupset_inner.backup_start_date” then a bunch of white space before picking backup up again with “AND backupset_inner.is_copy_only = 0 )”.

Comment on Event Notifications 101 – Intro to Event Notifications by Mbourgon

Mbourgon — Tue, 16 Apr 2013 03:11:49 +0000

I really do think and hope 2012 is the year for EN! Looking forward to seeing what you’ve done with it.

Some posts of mine, culminating with a multi-server setup. http://thebakingdba.blogspot.com/search?q=Event+notifications