Mini Post: Detecting Orphaned Users; sp_change_users_logins

Good day all! We are all the way in October 2018 and we are due for another SQL Something! Man is the year flying by!

Today, we are looking at detecting orphaned users. In short, these are usually users that do not have an associated SQL login.

You can detect these 'orphans' using the sp_change_users_login stored procedure with the @Action='Report' option.

Mini Post: Using sp_executesql

Hey everyone and welcome back to SQL Something!

Today I'm going to slip in a quick post before the month ends. Today we are going to look at sp_executesql.

This system stored procedure allows users to run batches of T-SQL code. Its use of parameters allowed for a bit more security as opposed to using something like EXEC by itself. Below we can see a simple example of the format needed to use sp_executesql.

--------------------------------------------------------------------------------------

DECLARE @SQLString NVARCHAR(500); 
SET @SQLString = N'SELECT @MessageOut = f.message
                                     FROM Test.dbo.Files f 
                                     WHERE File_ID = @level';

DECLARE @ParmDefinition NVARCHAR(500);
SET @ParmDefinition = N'@level tinyint,
                                            @MessageOut varchar(30) OUTPUT';

DECLARE @Result VARCHAR(30);

EXEC sp_executesql
@SQLString,
@ParmDefinition,
@level = 2,
@MessageOut = @Result OUTPUT;

SELECT @Result;

-------------------------------------------------------------------------------------- 

To use sp_executesql we can to do the following:

• Declare a SQLString variable to hold the T-SQL query/batch we would like to run.
• Declare a Parameter Definition string to hold a string of parameters that we would like to use. We can also declare a OUTPUT parameter here in order to pass information back to the caller.
• If you are returning information you may want to declare a variable to store the returned information (@Result).
• Use the sp_executesql system stored procedure and give it the SQL String variable as well as the parameter definition and the values for each parameter defined. If you have an OUTPUT parameter you can save the result to your @Result variable.
• Finally, select your @Result if you have one.

And there you go. You can help prevent SQL Injection in your applications this way. :-)

EDIT: Also, and I may go into this in more detail later, but I saw a very good demonstration illustrating that sp_executesql caches execution plans even if you use different parameter values. This can help speed up queries a good bit. :-)


DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)

Mini Post: List All Stored Procedures/Functions In A Database (Information_Schema.Routines)

Hey all! Welcome to, or welcome back, to SQL Something!

Today we're taking a quick look at how to list all Stored Procedures (and Functions) in a database. As per usual, I needed to do this at some point for my job and so I'm sharing what I found. :-)

In order to get the information we need we will be querying information_schema.routines as follows:

SELECT    ROUTINE_NAME as 'Stored Procedure',
        ROUTINE_CATALOG as 'Database Name',
        ROUTINE_SCHEMA as 'Database Schema',
        ROUTINE_TYPE as 'Object Type (PROCEDURE/FUNCTION)',
        ROUTINE_DEFINITION as 'Stored Procedure Definition',
        CREATED as 'Date Created',
        LAST_ALTERED as 'Date Last Altered'
FROM information_schema.routines with (nolock)
WHERE ROUTINE_TYPE = 'PROCEDURE'
ORDER BY ROUTINE_NAME

The above will list all Store Procedures for the database that you run the query on. Very helpful! :-)


Now you'll notice the WHERE clause specifies the PROCEDURE type only. If we wanted info on Functions as well as Stored Procedures, we can remove this clause.


If we wanted info on Functions only and wanted some additional info like what value the Function returns, we tweak the query as follows:

SELECT    ROUTINE_NAME as 'Function Name',
        ROUTINE_CATALOG as 'Database Name',
        ROUTINE_SCHEMA as 'Database Schema',
        ROUTINE_TYPE as 'Object Type (PROCEDURE/FUNCTION)',
        ROUTINE_DEFINITION as 'Function Definition',
        DATA_TYPE as 'Return Value Type',
        CREATED as 'Date Created',
        LAST_ALTERED as 'Date Last Altered'
FROM information_schema.routines with (nolock)
WHERE ROUTINE_TYPE = 'FUNCTION'
ORDER BY ROUTINE_NAME

DATA_TYPE returns the data type of the Function's return value or it returns TABLE if it's a table valued function.


DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)