Sql-server – sp_Blitz script output

sp-blitzsql server

I'm a very green DBA at my company, and ran the sp_Blitz script (on one of our test databases that is an exact copy of our production database), and I only get the message "Command(s) completed successfully.".

Is there a reason why I don't get a more detailed output? Or is the output file written somewhere else?

Best Answer

As Denis Rubashkin said in a comment:

If you had a look at the script text you would see that the script just creates a procedure sp_Blitz. You have to execute that proc (sample execution call with the most common parameters is in the bottom of the sp_Blitz script) to get an output you are waiting for.

You just created the procedure, but you still have to execute it. Here's some extra information if you have more questions:

There's a bunch of good resources you can check to get more information about any of the blitz procedures.

If you like to read: This is the official documentation on the procedure, you can probably find everything you need here: sp_blitz documentation

If you rather watch videos: Brent has made some great YouTube videos where he goes through the usage of the of the Blitz procedures.

This one is for the SP_Blitz, but there are also some vids for SP_BlitzIndex / sp_Blitzfirst. (All very useful procedures) Brent Ozars youtube

Related Solutions

Sql-server – How to inspect the security settings of a SQL Server Endpoint

After spending a portion of the day dealing with moving mirrored databases from server to server in our DEV environment in preparation for our move to SQL Server 2012, I came across this MSDN document that explains that ENDPOINT security is determined solely based on the type of account SQL Server is running under. If SQL Server is using a domain account, then that account automatically has access to the endpoint. If SQL Server is using a built-in account such as [Network Service] or [Local System] etc, then the endpoint must be configured with a security certificate, and only holders of the certificate can access the endpoint.

The relevant section from the document:

Determining the Authentication Type for a Database Mirroring Endpoint

It is important to understand that the SQL Server service accounts of your server instances determine what type of authentication you can use for your database mirroring endpoints, as follows:

If every server instance is running under a domain service account, you can use Windows Authentication for your database mirroring endpoints. If all the server instances run as the same domain user account, the correct user logins exist automatically in both master databases. This simplifies the security configuration for the availability databases and is recommended.

If any server instances that are hosting the availability replicas for an availability group run as different accounts, the login each account must be created in master on the other server instance. Then, that login must be granted CONNECT permissions to connect to the database mirroring endpoint of that server instance.

If your server instances use Windows Authentication, you can create database mirroring endpoints by using Transact-SQL, PowerShell, or the New Availability Group Wizard.

Note:

If a server instance that is to host an availability replica lacks a database mirroring endpoint, the New Availability Group Wizard can automatically create a database mirroring endpoint that uses Windows Authentication.

If any server instance is running under a built-in account, such as Local System, Local Service, or Network Service, or a nondomain account, you must use certificates for endpoint authentication. If you are using certificates for your database mirroring endpoints, your system administrator must configure each server instance to use certificates on both outbound and inbound connections.

There is no automated method for configuring database mirroring security using certificates. You will need to use either CREATE ENDPOINT Transact-SQL statement or the New-SqlHadrEndpoint PowerShell cmdlet. For more information, see CREATE ENDPOINT (Transact-SQL).

Remus Rusanu (in his answer) correctly identified how to determine the security in place on a mirroring endpoint; as in:

SELECT ep.name, sp2.name AS Grantee, sp.name AS Grantor, p.permission_name, ep.state_desc
FROM sys.server_permissions p 
    INNER JOIN sys.endpoints ep ON p.major_id = ep.endpoint_id
    INNER JOIN sys.server_principals sp ON p.grantor_principal_id = sp.principal_id
    INNER JOIN sys.server_principals sp2 ON p.grantee_principal_id = sp2.principal_id
WHERE p.class_desc = 'ENDPOINT' AND ep.type_desc = 'DATABASE_MIRRORING'

However in my case on SQL Server 2005 the original mirroring endpoints do not show up in the results of this query - I assume this is because they default security allows CONNECT access to the account SQL Server is running under (as explained above).

So the canonical answer to the question, how do you know who has CONNECT access to an ENDPOINT, is the sum of the query above, and the account SQL Server is using, along with any possible certificates you've configured with access to the endpoint.

Sql-server – SQL Server Agent: QUOTED_IDENTIFIER

I discovered that the SQL Agent job sets the database options based on the compatibility level of the database and this can override the database options that have been set.

i.e. Database has database options of SET QUOTED_IDENTIFIER ON however the compatibiliy level of the database is set to SQL 2000 so that the SQL Job fails (i.e. when attempting to UPDATE rows in table that has filtered index on it).

Switch the compatibility level to SQL 2008 and then the SQL job will succeed.

Best Answer

Related Solutions

Sql-server – How to inspect the security settings of a SQL Server Endpoint

Sql-server – SQL Server Agent: QUOTED_IDENTIFIER

Related Question