Speaking This Weekend in Iowa City!

Come out to SQL Saturday Iowa at the University of Iowa this Saturday 06/23/2018 for a SQL Saturday that never disappoints - Iowa always puts on a fun event with great atmosphere and informative sessions.

I am giving the premiere of my new presentation "Intro to Powershell for the SQL Server DBA"

--

Many DBA's have the same outlook on Powershell - we all know we should use it, but we don't think we have time to learn one more tool that isn't even really part of SQL Server anyway.  I was one of these DBA's for many years, but recently I have come to respect the *power* of Powershell.

Come learn the basics of Powershell, how Powershell and SQL Server interact with each other, and finally how to use Powershell to automate common SQL Server tasks such as maintenance and SQL Server installations.  If you need to interact with Windows or Active Directory, odds are Powershell is the right tool for the job - come learn how to use it!

--

There are also many other amazing speakers and sessions this weekend, including Microsoft Data Platform MVP's David Klee (@kleegeek) and Ed Leighton-Dick (@eleightondick).

Hope to see you there!

Revoke the permission(s) before dropping the server principal

As a remote DBA I often have to log in to client systems for a fixed amount of time and then remove logins and other security after the work is done.

This morning while cleaning up I ended up with a new error for me:

--

Msg 15173, Level 16, State 1, Line 1
Server principal ‘NtiretyMirror’ has granted one or more permission(s). Revoke the permission(s) before dropping the server principal.

--

Interesting.

At first I thought the error was that the login had *been* granted a permission, which seemed bizarre to me - every day I drop logins with all types of permissions and role memberships.

When I Google'd the base portion of the error - "Revoke the permission(s) before dropping the server principal" I found that I was incorrect, as described in an MSDB article here.

The problem wasn't that the login had permissions, but rather than it had granted someone *else* permissions.

https://i.imgflip.com/qygsn.jpg

The next question became how to tell what permissions we are talking about.  Since the permissions weren't on the NtiretyMirror login itself, how could I find out what permissions were involved?

There was a basic query in the MSDB post, but I tweaked it to give a cleaner resultset:

--

DECLARE @GrantorName nvarchar(4000)

SET @GrantorName = 'NtiretyMirror' /* Login in Question */

SELECT b.name as Grantor
, c.name as Grantee
, a.state_desc as PermissionState
, a.class_desc as PermissionClass
, a.type as PermissionType
, a.permission_name as PermissionName
, a.major_id as SecurableID 
FROM sys.server_permissions a
JOIN sys.server_principals b
ON a.grantor_principal_id = b.principal_id
JOIN sys.server_principals c
ON a.grantee_principal_id = c.principal_id
WHERE grantor_principal_id =
(
SELECT principal_id
FROM sys.server_principals
WHERE name = @GrantorName
)

--

Sure enough I found an offending row:

Grantor	Grantee	PermissionState	PermissionClass	PermissionType	PermissionName	SecurableID
NtiretyMirror	public	GRANT	ENDPOINT	CO	CONNECT	6

--

I looked in sys.endpoints and found that ID =6 is the mirroring endpoint.

Surprise, surprise - when I had set up mirroring (as NtiretyMirror - get it?) I had granted CONNECT to public as part of the process, and not the NtiretyMirror login owned that GRANT.

I could try to REVOKE the CONNECT, but did I really want to risk breaking mirroring?

I found the safer thing to do was to change the ownership of the Mirroring endpoint via ALTER AUTHORIZATION:

--

USE [master]
GO
ALTER AUTHORIZATION ON ENDPOINT::mirroring TO sa;
GO

--

Once I changed the ownership of the endpoint, my original query showed no permissions related to NtiretyMirror, and I was able to drop it successfully with the normal DROP LOGIN statement.

https://memegenerator.net/img/instances/66310140.jpg

Hope this helps!

Double Win- Watch DeWitt and Help SQLSoldier's Loved Ones!

Brent Ozar (blog/@BrentO) is sponsoring a special webcast in a couple weeks, with an amazing presenter:

https://www.brentozar.com/archive/2018/06/announcing-a-very-special-sqlfamily-webcast-with-dr-david-dewitt/

The webcast (“SQL Query Optimization. Why is it so hard to get right?”) is free, but with a suggested donation to the  Robert Davis Memorial and Grief Fund:

https://www.gofundme.com/memorial-and-grief-fund-for-chrissy

If you don’t know who these people are, you should…

Dr. David DeWitt is currently with MIT but spent years at Microsoft as a Technical Fellow at the Jim Gray Systems Lab.  He presented keynotes at the PASS Summit for years and gives an awesome talk regardless of the topic.

Robert Davis (SQL Soldier) was a SQL Server MVP and Microsoft Certified Master who was helpful to many over the years online in the forums and in-person at events.  He died suddenly and without warning earlier this years, leaving behind his wife.

Sign up for the webcast if you can. and if you feel so moved please give to the fund as well!

Thanks!

Speaking at PASS Summit 2018!

I found out a few days ago that one of my sessions was selected for Summit this Fall - I will be presenting an updated version of my "Getting Started with Extended Events" session that I have been working at SQL Saturdays over the last couple years.

I have submitted to Summit multiple times over the years, and every time the email comes in late May/early June my stomach knots up - I have been selected to speak once before (2016) but I still have that little bit of self-doubt.  Were my abstracts good enough?  Does anyone care about the subjects I want to talk about?  Who am I kidding anyway?

But when the email comes and one of the sessions says "Accepted"...

I have to admit for a brief moment it feels like this:

https://theworkingcaregiver.files.wordpress.com/2017/03/you-are-the-chosen-one-pl-ffffff.jpg

...but the moment passes pretty quickly, because I know that I am but a little piece of the big puzzle.  There are dozens of  Community Speakers and also dozens of Microsoft and Partner Speakers, not to mention the *HUNDREDS* of volunteers that make PASS and the Summit work.

It is still exciting to be selected, and each time it is a little humbling to hear people who don't happen to get selected in any given year offer up congratulations to those who are picked; we all support each other, and it's likely next year I will be congratulating someone else when they get picked and I don't.

Compared to Mr. T, I think in this case Emmet is more on-target:

At the end of the day we are all #sqlfamily and we all  have our own unique talents and our own unique voice - even if a particular event or venue doesn't select you, keep sharing - speaking/writing/whatever - because everyone has a story to tell.