SQLLawman

We are in the process of upgrading a Microsoft product that just hasn’t gotten around to integrating with AD and uses SQL accounts.  It also has a really bad habit of locking SQL accounts if your AD policy for failed login attempts is set low.  So I got tired of searching through SSMS to unlock accounts, so I wrote a quick script to list the locked accounts and the command to unlock it.  I didn’t go as far as to auto-unlock the locked accounts but that would be a simple edit.  Here’s the script.

Declare @UserID varchar(25), @status nvarchar(5), @Command varchar(255), @command1 varchar(255)
Declare Account_cursor Cursor
for select name from sys.syslogins where name not like ‘<DOMAIN>\%’
open Account_cursor
FETCH NEXT FROM Account_cursor
INTO @UserID
WHILE @@FETCH_STATUS = 0
BEGIN
select @status= convert(nvarchar,(loginproperty(@userID,N’IsLocked’)))
if @status = ’1′
BEGIN
print ‘– ‘ + @userID + ‘ is locked’
select @command = ‘ALTER LOGIN [' + @userID + '] WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF, NO CREDENTIAL’
select @command1 = ‘ALTER LOGIN [' + @userID + '] WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON, NO CREDENTIAL’
print @command
print @command1
– exec (@command)
– exec (@command1)
END
fetch next from Account_Cursor into @userID
END
Close Account_cursor
Deallocate Account_cursor

Thank you @unclebiguns for pointing me toward the function that makes this all possible.

I am guessing that this is not the easiest or most efficient way to do this, so if anyone would like to educate me on a better way, I am very open to learning your techniques.

I was asked to find all tables in a database that had a DML trigger so a developer could ensure that all the old functionality was available in the new database.  So once I built my query to give me the information, I thought I’d save it here for future use.

select a.name as ‘Trigger’, b.name as ‘Table’
from sysobjects a
join sysobjects b
on a.parent_obj=b.id
where a.xtype = ‘TR’

This gave me a nice little result set of each trigger and what table it was associated.  Maybe I won’t have to re-invent the wheel the next time they ask.

We have a database that holds information that is fairly personal, so much that the application team that supports it may only have read access to the database.  There came a need to occasionally lock all the administrators out of the system for updates.  These updates will occur every few months.  So I created a SQL user and gave that user select and update rights to the necessary table.  Then I created a couple of stored procs, one to disable the logins and one to enable the logins.

CREATE PROCEDURE [dbo].[LockOutAdministrators]
WITH EXECUTE AS ‘EXECUTIONER’
AS
BEGIN
BLAH, BLAH, BLAH
END

I then granted execute to the stored procedures to the application group.  So the application team can only update the table via the stored procedure that does only what it needs to do.  They are happy they have an easy way to do this without me becoming the bottle neck.  If this was a more sensitive system, I’d probably add some auditing, but it is not necessary at this time.

When I became our companies MSSQL DBA I inherited an smooth running machine.  My predecessors were extremely talented and their scripts did everything we needed done.  But I can’t just live off their hard work, I need to leave my mark on the environment.  So I am tweaking some of their scripts to get rid of what annoys me.  The current task is old backup files.  The scripts they wrote do this awesome thing that archives a backup if the archive bit has not been cleared.  This is so cool.  If NetBackup has a problem, the old backup file that hasn’t gone to tape/disk is moved to an archive folder and then the new backup file is created.  But what happens when after the file is backed up and the archive bit is cleared.  It just sits on the disk and is backed up again with every full backup that occurs.  That makes the backups bigger and take longer.  So I added a job that deletes all *.bak files that have their archive bit cleared.  That was so much easier than I expected it to be.

exec xp_cmdshell ‘del d:\mssql\backup\*.bak /A:-A /S /F’

I run this command 15 minutes before I do the nightly backup.  It frees up space so I get less alerts of full volumes.  It decreases the NetBackUp full backup time and more backups can happen within their windows.  Unfortunately the DBAs before me decided that transaction log backups and database backups should have an extension of .bak.  Since I want to treat transaction logs differently I have to go back and change the backup extension.  So I’m slowly working through the databases to make that change.  So once that is done, I can start cleaning up my backup volumes.