Drop User Failed - User has granted one or more permission(s).

Today I was tasked with cleaning up our security logins, and one of the users came up with the following error:

I wasn't quite sure what to do. After a little google-fu, I found the following script:

select * from sys.server_permissions

where grantor_principal_id =
(select principal_id from sys.server_principals where name = 'domain\user')

And got two results:

This told me the user owned an endpoint, but which one? The previous query only gave me the major_id which was 65536.

Luckily, I found the following: http://technet.microsoft.com/en-us/library/ms180076.aspx

So I ran SELECT * FROM sys.endpoints and got the following results:

I saw endpoint_ID 65536 and found it was a mirroring endpoint. A mirroring remnant of mirroring that was once set up, but was now gone. So all I had to do was drop that endpoint with:

DROP ENDPOINT Mirroring

And I was good to drop the user.

Fixing A Broken SQL Config Manager

I've had to deal with a broken SQL Configuration Manager four times in the past four months. The first time was a bit nerve-wracking as I didn't quite know what to do.
You'll know that it's broken when you get the following prompt after trying to open "SQL Configuration Manger":

Turns out all I had to do was run the following in a command prompt to get the Config Manager up and running:
mofcomp "C:\Program Files (x86)\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof"

And voila it's fixed.

Check: http://support.microsoft.com/kb/956013 for full technical details.

P.S. If Avamar can't view databases on a server, this will fix it. Thanks to John Habu for figuring that out with me.

On the road to Execptional DBA

My very first blog, dated January 23rd, 2012. I've probably missed the blog bandwagon by many years, but that's fine by me, I finally have something I want to write about; My road to becoming an "Exceptional DBA." For those that don't understand what this is, please Google "How to Become an Exceptional DBA" by Brad M. McGehee.

March 2011 was the month I started working with SQL Server. It's been close to a year now, and I'm much more comfortable in my role today than I was then. It's definitely been an interesting road, some highs and some lows. But through it all, I've continued on my path. I know there's a vast array of SQL Server knowledge, but I'm young and willing to learn. I'm also lucky to have a great mentor, Ivan Bermudez (http://hollywoodsql.blogspot.com/).

My current goal is to get my first SQL certification, the MCTS "Microsoft SQL Server 2008 - Implementation and Maintenance" certification. SQL Server 2012 will be coming out later this year, so I'll upgrade my certs when that date arrives.

Wish me luck!!!