System Table Security in SQL Server 2005

I ran into an interesting issue today while working with SQL Server 2005 Beta 2 CTP December 2004. It would appear that Microsoft has greatly restricted permissions, even SELECTing from master system tables. Here's the situation we had.

TableA in user DatabaseZ had an insert/update trigger to insert into TableB in DatabaseY, but only if the context_info from master..sysprocesses was not = 1, which was set to 1 if the application set the context_info when it logged in. (Yeah, I know this should really be added to http://thedailywtf.com/). When the trigger tried to SELECT from sysprocesses the user calling did not have permission to read the table, thus causing an error.

To correct this we (thanks to Bruce Nation and Peter Watson) changed the trigger to use the new context_info() function instead of selecting from sysprocesses.

We still have not determined if this is just a broken permissions chain or if MSFT just won't allow this. We were not able to GRANT to the system tables in master, and we weren't able to wrap them in views, SPs, or functions either. It just appears that MSFT doesn't want us using the system tables for ANYTHING anymore.

If anyone has additional insight into this problem, posts would be appreciated.

Error Renaming Server with SQL Server 2005 CTP December 2004

I have just recovered from an interesting error with SQL Server 2005 CTP December 2004. Part of the project at a customer is to "Smoke Test" the in-house applications against SQL Server 2005 as-is. To do this, we built a SQL Server 2005 server to be identical to an existing non-production SQL2000 server, swapped servers(rename), and re-pointed the applications for testing.

While renaming servers I ran into a problem where SQL Server 2005 would start, would not allow user connections of any kind, and quickly stopped. The only way I found to recover from it was to move the databases out of the install path and reinstall.

The error reported by SQL Server was TDSSNICLIENT INITIALIZATION FAILURE. Error: 17182, Severity: 16, State: 1. There were subsequent errors, but this was the first in the chain.

I could find no documentation on the error on the net or the documentation with the Beta. To recover, I reinstalled and moved the databases (master and all) into the install path and restarted SQL Server. I then performed the usual sp_dropserver/sp_addserver steps of renaming a server.

All is well, but I thought I would post this just in case anyone else runs across it.


Jon Baker

Why did I start a blog?

It is probably bad etiquette to start a blog with a rant, but here goes

<rant>

Why are there so few blogs about SQL Server?!?!? Other than Microsoft employees and a few other well-connected people, I could find very little blogging about SQL Server on the big wide internet.

I actually went looking for a new source of SQL Server knowledge in the form of blogs, a good RSS reader, and the informal community of SQL Server developers/DBAs. I would never have been so pretentious as to think, "Gee, I should start a blog on SQL Server!" But with so little published work out there, I was compelled to create some work in this space.

If I have missed something and/or I AM being pretentious, someone please tell me!
</rant>

Future posts will include work that I'm doing at customer sites, research into SQL Server 2005, my presentations from the local SQL Server SIG, and anything else I think might be interesting to the SQL Server community. Please feel free to give me feedback on the things I write. I am a firm believer that debate is healthy, and I love to learn from others.