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.