Install-DbaFirstResponderKit: add recommended certificates for non-DBAs

[ReeceGoding]

Summarize Functionality

As recorded in the "How to Grant Permissions to Non-DBAs" section here and repeated in the free training class, the First Responder Kit and sp_BlitzFirst in particular are designed for first responders. Specifically, that link I have given shows that sp_BlitzFirst allows for non-DBA usage and gives the following code which allows non-DBAs to run it. We should provide something like this as part of Install-DbaFirstResponderKit.

USE master;
GO
CREATE CERTIFICATE sp_BlitzFirst_cert
ENCRYPTION BY PASSWORD = '5OClockSomewhere'
WITH SUBJECT = 'Certificate for sp_BlitzFirst',
START_DATE = '20130711', EXPIRY_DATE = '21000101';
GO
CREATE LOGIN sp_BlitzFirst_login FROM CERTIFICATE sp_BlitzFirst_cert;
GO
CREATE USER sp_BlitzFirst_login FROM CERTIFICATE sp_BlitzFirst_cert;
GO
GRANT EXECUTE ON dbo.sp_BlitzFirst TO sp_BlitzFirst_login;
GO
GRANT CONTROL SERVER TO sp_BlitzFirst_login;
GO
ADD SIGNATURE TO sp_BlitzFirst BY CERTIFICATE sp_BlitzFirst_cert
WITH PASSWORD = '5OClockSomewhere';
GO
GRANT EXECUTE ON dbo.sp_BlitzFirst TO [public];
GO

Is there a command that is similiar or close to what you are looking for?

Yes

Technical Details

My thinking:

• Copy and pasting the given T-SQL would be a bad idea because of the hard-coded password. This should be easy to solve. We could make the certificate single use with the ALTER CERTIFICATE sp_BlitzFirst_cert REMOVE PRIVATE KEY trick mentioned here. This would easier for us to implement than prompting for passwords.
• I don't know of any good dbatools wrapper for signing stored procedures. I presume that there is some PowerShell way of doing this that is more fun than the T-SQL.
• I imagine that what we want to add to Install-DbaFirstResponderKit is a parameter that takes in a collection of stored procedures names and signs all of them with CONTROL SERVER. It should be a collection because there are other procedures in the FRK that benefit from this, such as the new sp_kill.
• Maybe call the parameter -LetPublicExecute?
• If we can use the same certificate for every procedure, then that would be better for everyone. Again, easier for us to implement and easier for users to manage.

Some of my ideas here may be bad! It has been too many years since I've had to seriously think about certificates. Claude will surely outsmart me here.