Category Archives: Changing-Defaults

Scripts for changing defaults of the SQL Instance

Generate a Random Password

Useful to generate random passwords


    /*
    DESCRIPTION: 
    Generates a (pseudo)random string of chars to be used in a password
     
    Configuration: on top. Length and desired chars
     
    Compatibility list:
    MSSQL2005
    MSSQL2008
    MSSQL2008R2
    MSSQL2012
     
    Not tested:
    MSSQL2000
     
    Update Log:
    */
     
    set nocount on
     
    declare
    	@plength int,
    	@password varchar(50),
    	@string varchar(72), 
    --52 possible letters + 10 possible numbers + up to 20 possible extras
    	@numbers varchar(10),
    	@extra varchar(20),
    	@index tinyint
     
     /* configuration  */
     
    set @plength = 10
     
     
    -- eliminate 0, 1, i, l, o, o to make the password more readable
    set @string = 'abcdefghjklmnpqrstuvwxyzabcdefghijkmnpqrstuvwxyz' -- option @charset = 0
    set @numbers = '23456789'
    set @extra = '>_!@#$%&=?<>' -- special characters
     
     
    /* end configuration  */
     
     
    set @string = @string + @numbers + @extra
     
    set @password = ''
    	
    	while (@plength > 0)
    	begin
    		set @index = (abs(checksum(newid())) % len(@string)) + 1 
    		
    		set @password = @password + substring(@string, @index, 1)
     
    		set @plength -= 1 --set @plength = @plength - 1
    	end
     
     
    /*
    -- This section actually changes the password of the SA account
    declare @cmd nvarchar(100)
    set @cmd = 'alter login [sa] with password=n'''+@password+''' unlock'
     
    execute (@cmd)
    -- */


Enforce Password Policies on SQL Logins

Make sure all SQL Logins have the “enforced password” setting check

/*
DESCRIPTION:
The following code Enables the CHECK POLICY ON on all logins who needs it, No need to run the output, it just does it.
 
CONFIGURATION
None

Author: Miguel Quintana

Compatibility list:
MSSQL2005
MSSQL2008

DOES NOT WORK
MSSQL2000
*/

DECLARE @LoginName sysname
DECLARE @SQL NVARCHAR(1000)

DECLARE DBLOGINS CURSOR FOR
    SELECT name  FROM master.sys.sql_logins
    WHERE is_policy_checked = 0

OPEN DBLOGINS

FETCH NEXT FROM DBLOGINS INTO @LoginName
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQL = 'ALTER LOGIN [' + @LoginName + '] WITH CHECK_EXPIRATION=OFF, CHECK_POLICY=ON;'
-- print @SQL
    EXEC sp_executesql @SQL
    PRINT 'Fixing Login for ['+@LoginName+']'
    FETCH NEXT FROM DBLOGINS INTO @LoginName
END

CLOSE DBLOGINS
DEALLOCATE DBLOGINS
PRINT 'Done'