SQL Server, PASS, and other data mishaps
Posts tagged SQL Server
Changing the owner of nearly anything in SQL Server
Jan 17th
The other day we were going through some old scripts and had a huge discussion about changing object ownership in SQL. Forever the only answers were to recreate the objects or use the stored procedures: sp_changeobjectowner or sp_changedbowner. Alternatively at times there were those of us who used some other undisclosed hack of system tables but, I’ve never done anything like that…
Since SQL 2005 Alter Authorization has been the right way to change ownership of nearly anything
This is the most common use of alter authorization but there are many more.
sp_changeobjectowner would look like this
sp_changeobjectowner 'Products.Description','AllenK'
While the updated syntax would look like this
ALTER AUTHORIZATION ON OBJECT::[Products].[Description] TO [AllenK]
What really brought this post about was because I was working with certificates recently and at the time I needed to change who the owner and I just exported/imported & recreated them. It was easy and what I knew would work. After looking through the syntax for alter authorization I realized I could & probably should have done it this way. I guess old habits die hard.
ALTER AUTHORIZATION ON CERTIFICATE::[MYCERTIFICATE] TO [CERTOWNER]
Some out there might question why I’m rehashing syntax changes that happened nearly 5 years ago, The reality is that even in 2011 a lot of the installations of SQL Server I look at are still 2005 and many of the people I run into still use old ways of doing things, this is for you (and me)!
ALTER AUTHORIZATION ON OBJECT::[Products].[Description] TO [AllenK]
Something old is something new
Jan 4th
For the new year I have decided to try something different with a series of small regular posts.
Dealing with our large team of developers and our DBA team as well, I often run across interesting discussions about small things that are easily overlooked. Often times these discussions end in a exclamation of “I knew that!!” Of course what really happend is the memory of that specific feature or tidbit has simply been paged out of our memory. I intend to take these (typicaly) small tidbits of SQL goodness and publish them to hopefully jog your memory as well.
It never hurts to have your memory paged back in occasionally and I hope this helps
Im a Winner!
Dec 20th
And no, I dont mean in my mothers eyes, thats a given.
I won the Ipad/SQL Monitor Contest that Redgate was sponsoring over on Grant Fritcheys Blog
Im thrilled to have won!! Ive been eyeing Ipads for quite some time but, the novelty & cost has kept me at arms length.
Im always in the market for DBA tools that can make my teams (or my) job easier, thats where redgate’s Sql Monitor comes in, shortly I expect to be running it through the paces. We already use most of the other redgate tools and love them so I have high expectations for SQLMonitor!
Christmas definetly came early for me this year, Its like Grant, Steve, Brent and most importantly Redgate were my very own elves!
Thanks again to Regdate for their outstanding support of our SQL Community.
PS: I expect to have some aweome pics of remote monitoring SQL Servers shortly!
SQL Server 2008 & IPV6 vs Symantec
Sep 27th
Had a strange error on a SQL 2008 cluster the other day,
The OS was Windows 2008 R2
We kept getting messages that the cluster node was offline because the Quorum was unavailable. This made little sense as both nodes in this cluster were online and the Quorum disk was available. We could ping across the heartbeat, everything looked fine except for these errors.
After a little research we determined that a new version of Symantec Endpoint Security had been pushed to these servers. Even with the new version of endpoint security, we could establish communication across all networks between the 2 nodes so we were a little stumped. Eventually we ran across a policy that was being enforced from the Symantec central management server/policy/whatever its called!
As it turns out, Symantec endpoint security by default blocks all IPV6 traffic. If you’re like me, I didn’t even realize that a windows 2008 cluster would use IPV6 for the heartbeat communication. After disabling the rules that were preventing IPV6 traffic everything returned to normal.
So, the moral of all this is nothing new… NEVER trust anything new getting pushed to your servers..
PASS Summit Community Choice Session results revealed
Aug 16th
This year the Program Committee decided to try something different to increase community involvement with the abstract selection process for the annual summit. We decided to allow any registered PASS members to vote on the final 4 community sessions to be presented at the Summit in Seattle this year. With our historically low turnout in surveys of our membership, I turned to the team at PASSHQ with a simple question about whether or not we could use the same voting tool that is in place for the upcoming Board of Directors election. Considering our surveys have traditionally resulted in 300-500 results , at the time I put a personal goal on the survey response rate of 500, anything greater than that and I would consider the time and energy It took to put it together a win, anything less and well, we wouldn’t be doing any more exploring in the use of public voting for Summit Sessions.
I’m VERY pleased to say that by all indications, the voting process was a great success, sure we will always aim to do better but, for now, having over 1100 people vote on what they want to see at the Summit is a huge win for PASS as an organization. In the App Dev group, the winner was declared by 2 votes. 2 votes out of 600 is pretty outstanding, if you didn’t take the time and vote, you could have been the vote that swayed the result!! Don’t miss your chance next time!! The winning sessions can be found here.
I want to take the time to thank each and every member of the awesome PASS Community who voted. As well as Jeremiah Peschka (Twitter|Blog) & Lori Edwards (Twitter|Blog) for dealing with my crazy ideas on putting this together, and keeping me in check. Andy Warren (Twitter|Blog) for helping me get the voting tool he built for pass working for this use. Id also like to give a huge thanks to Hannes, Wesley and Elena at PASSHQ for helping bring all of this to PASS (pun intended)
