Category: SQL Server

I don’t recall exactly why this script was required, but the below SQL query can be used to get the expiration date for logins in Microsoft SQL Server. Some of the data is available directly from the sys.sql_logins table, but other pieces had to be retrieved using the LOGINPROPERTY function:

SELECT
	['SQL Logins'].name AS 'LoginName'
	,LOGINPROPERTY(['SQL Logins'].name, 'PasswordLastSetTime') AS 'PasswordLastSetTime'
	,LOGINPROPERTY(['SQL Logins'].name, 'DaysUntilExpiration') AS 'DaysUntilExpiration'
	,DATEADD(dd,CONVERT(int, LOGINPROPERTY (['SQL Logins'].name, 'DaysUntilExpiration')),CONVERT(datetime,LOGINPROPERTY(['SQL Logins'].name,'PasswordLastSetTime'))) AS 'PasswordExpiration'
	,['SQL Logins'].is_policy_checked AS 'IsPolicyChecked'
	,LOGINPROPERTY(['SQL Logins'].name, 'IsExpired'') AS 'IsExpired'
	,LOGINPROPERTY(['SQL Logins'].name, 'IsMustChange'') AS 'IsMustChange'
	,LOGINPROPERTY(['SQL Logins'].name, 'IsLocked'') AS 'IsLocked'
	,LOGINPROPERTY(['SQL Logins'].name, 'LockoutTime'') AS 'LockoutTime'
	,LOGINPROPERTY(['SQL Logins'].name, 'BadPasswordCount'') AS 'BadPasswordCount'
	,LOGINPROPERTY(['SQL Logins'].name, 'BadPasswordTime'') AS 'BadPasswordTime'
	,LOGINPROPERTY(['SQL Logins'].name, 'HistoryLength'') AS 'HistoryLength'
FROM
	sys.sql_logins AS ['SQL Logins']
WHERE
	is_expiration_checked = 1 
ORDER BY
	['SQL Logins'].name

I needed to find custom triggers or views which referenced a certain table and although I thought I had a script which would find text in a SQL object like a trigger, view or stored procedure, I couldn’t find one when I searched my site the other day.

It only took me a few minutes to write one; the first highlighted text is the text to search for and the second a limitation on the name of the SQL objects to check.

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
DECLARE @Search varchar(255)
SET @Search='PM00200'

SELECT DISTINCT
	['SQL Objects'].name AS Object_Name
	,['SQL Objects'].type_desc
FROM
	sys.sql_modules AS ['SQL Modules']
INNER JOIN
	sys.objects AS ['SQL Objects']
		ON
			['SQL Objects'].object_id=['SQL Modules'].object_id
WHERE
	['SQL Objects'].name LIKE 'u%_AZRCRV_%'
AND
	['SQL Modules'].definition LIKE '%'+@Search+'%'
ORDER BY
	['SQL Objects'].name
	,['SQL Objects'].type_desc

I did some work for a client recently which involved some custom SQL objects created by a consultant at their previous VAR. It appeared that there was a consistent prefix on the created objects which meant we could use a script I’ve previously posted to identify them all.

However, we quickly realised there were other objects which did not adhere to the naming convention we’d identified. Fortunately, I remembered writing a script to update database names in SQL views after copying live to test which identified the views to update by using the sys.sql_modules object.

Using that oncept, I came up with the below script which will search for the text specified in the variable at the top in all objects:

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
DECLARE @SearchString VARCHAR(100) = 'string to find'

SELECT DISTINCT
	['sys objects'].name AS 'Object Name'
	,['sys objects'].type_desc AS 'Object Type Description'
FROM
	sys.sql_modules AS ['sys modules']
INNER JOIN
	sys.objects AS ['sys objects']
		ON
			['sys objects'].object_id = ['sys modules'].object_id
WHERE
	['sys modules'].definition like '%' + @SearchString + '%'

I work on a variety of test and demo systems and recently stumbled across an oddity with SQL Server Management Studio (SSMS); when I tried to launch it using the shortcut, the splash screen would display, stay on screen for a few seconds before disappearing with SSMS never starting, but no error message appearing or showing in the logs.

If I tried to start it by typing ssms in a command prompt I received the following error:

I did some exploring and found this article on Stackoverflow which resolved my problem.

Find the Ssms.exe.config file and erase the line <NgenBind_OptimizeNonGac enabled="1" />:

While much of the work I do is directly with Microsoft Dynamics GP, I also do work for clients which isn’t directly related. I’ve created code to generate numbers a few times in the past and figured I might as well post the base code I use for this to make it easier to find in future.

I’ve created it in such a way that several unique numbers can be stored and incremented.

The first part of the code creates a table to hold the number type and next number:

-- drop table if it exists
IF OBJECT_ID (N'ut_AZRCRV_NextNumber', N'U') IS NOT NULL
	DROP TABLE ut_AZRCRV_NextNumber
GO
-- create table
CREATE TABLE ut_AZRCRV_NextNumber(
	NMBRTYPE VARCHAR(50)
	,NEXTNMBR INT
)
GO

Next, I create a stored procedure which will increment and return the next number:

-- drop stored proc if it exists
IF OBJECT_ID (N'usp_AZRCRV_GetNextNumber', N'P') IS NOT NULL
    DROP PROCEDURE usp_AZRCRV_GetNextNumber
GO
-- create stored proc
CREATE PROCEDURE [dbo].[usp_AZRCRV_GetNextNumber]
	(
	@NMBRTYPE VARCHAR(50)
	,@NEXTNMBR INT OUTPUT
	)
AS
/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
	SET NOCOUNT ON

	BEGIN TRAN
		-- if this is the first value generated for this table, start with one
		IF NOT EXISTS (SELECT * FROM ut_AZRCRV_NextNumber WHERE NMBRTYPE = @NMBRTYPE)
			INSERT INTO ut_AZRCRV_NextNumber
				(NMBRTYPE,NEXTNMBR)
			VALUES
				(@NMBRTYPE,1)
		
		-- select next number from table into variable
		SELECT @NEXTNMBR = NEXTNMBR FROM ut_AZRCRV_NextNumber WHERE NMBRTYPE = @NMBRTYPE
			
		-- increment number by 1
		UPDATE
			ut_AZRCRV_NextNumber
		SET
			NEXTNMBR = NEXTNMBR + 1
		WHERE
			NMBRTYPE = @NMBRTYPE
	COMMIT TRAN
	
	-- return variable containing next number
	RETURN @NEXTNMBR
GO

Then, I grant execute permissions to the relevant database role:

-- grant execute permission on stored proc to ur_AZRCRV_InvoiceUser
GRANT EXECUTE ON usp_AZRCRV_GetNextNumber TO ur_AZRCRV_InvoiceUser
GO

And finally, I have the SQL code which will generate the next number:

-- code to get next number
DECLARE @NMBRTYPE VARCHAR(50) = 'Sales Invoice'
DECLARE @NEXTNMBR INT

EXEC [usp_AZRCRV_GetNextNumber] @NMBRTYPE, @NEXTNMBR OUTPUT

SELECT @NEXTNMBR
GO