Clearing locked Dynamics GP batches / transactions

More than once we've run into an issue where a batch is stuck, a reconcile cannot be run, a transaction cannot be edited, among other things. Normally you would have to get everyone out of the system and wipe out a few SQL tables to clear the blockage. Sometimes you have a lot of users to notify to cease and desist all work in GP, log off, wait a few minutes, notify everyone they can log back in, and, finally, they log back in to continue their jobs. For a medium to large business this can be a serious resource downtime (ie: 15 minutes x 20 users = 5 hours lost). Plus, users may have stepped away from their desk or are simply not available to log off.

The error messages you will get that could use a script like this are "Sorry, another user is editing this document" (KB865003), "You cannot complete this process while transactions are being edited" (KB864434), "Item is in use" (KB855665), "Another user is doing this operation" (KB873851), "You cannot complete this process while invoices are being posted" (KB865739), "Cannot complete reconcile while someone is posting" (KB855277), "Another user is printing or calculating 1099s" (KB859915).

If you get "This asset is being retired by another user. Please try later." (KB860373) or "Batch is marked for posting by another user" (KB850289), you can run this script, but there are more scripts you will need to run afterwards. See the KB article on PartnerSource/CustomerSource for more information.

As usual, use these at your own risk. And don't forget to make backups first!

/*
    This script will clear out entries in the following tables,
        in order that you don't have to log everyone out to clear
        out orphaned records and transaction/batch locks:
            DYNAMICS..Activity
            tempdb..DEX_SESSION
            tempdb..DEX_LOCK
            DYNAMICS..SY00800
            DYNAMICS..SY00801
        The Activity will be cleared out first for orphaned records
        Then the DEX_SESSION will be cleared out for orphaned records
        Then the DEX_LOCK will be cleared out for orphaned records
*/
--    Remove orphaned entries in Activity table
--        by matching Activity, DEX_SESSION, and SysProcesses tables
--        to find Activity not associated with current SQL sessions/processes
--    Make sure to run this DELETE script before the others

    DELETE FROM DYNAMICS..Activity
    WHERE SQLSESID NOT IN (
        SELECT SQLSESID
        FROM DYNAMICS..Activity A
        INNER JOIN tempdb..DEX_SESSION S
        ON A.SQLSESID = S.session_id
        INNER JOIN master..sysprocesses P
        on S.sqlsvr_spid = P.spid
        AND A.USERID = P.loginame
    )

--    Remove orphaned entries in DEX_LOCK and DEX_SESSION
--        by deleting any records not found in Activity

    DELETE FROM tempdb..DEX_LOCK
    WHERE session_id NOT IN (
        SELECT SQLSESID
        FROM DYNAMICS..ACTIVITY
    )

    DELETE FROM tempdb..DEX_SESSION
    WHERE session_id NOT IN (
        SELECT SQLSESID
        FROM DYNAMICS..ACTIVITY
    )

--    Remove orphaned entries in DYNAMICS..SY00800 (Batch Activity)

    DELETE FROM DYNAMICS..SY00800
    WHERE DEX_ROW_ID NOT IN (
        SELECT B.DEX_ROW_ID
        FROM DYNAMICS..SY00800 B
        INNER JOIN DYNAMICS..ACTIVITY A
            ON B.USERID = A.USERID
            AND B.CMPNYNAM = A.CMPNYNAM
    )

--    Remove orphaned entries in DYNAMICS..SY00801 (Resource Activity)

    DELETE FROM DYNAMICS..SY00801
    WHERE DEX_ROW_ID NOT IN (
        SELECT R.DEX_ROW_ID
        FROM DYNAMICS..SY00801 R
        INNER JOIN (
            DYNAMICS..ACTIVITY A
            INNER JOIN DYNAMICS..SY01500 C
                ON A.CMPNYNAM = C.CMPNYNAM)
            ON R.USERID = A.USERID
            AND R.CMPANYID = C.CMPANYID
    )

--    Look for DEX_LOCKs not associated with currently logged in users

    SELECT
        DL.table_path_name,
        A.USERID,
        A.CMPNYNAM
    FROM tempdb..DEX_LOCK DL
        LEFT JOIN DYNAMICS..ACTIVITY A
            ON DL.session_id = A.SQLSESID

--    Values in the USERID and CMPNYNAM columns show which user is
--        tied to that table; have the user log out
--        then run the above SQL statement again
--        to make sure the DEX_LOCK is cleared

/*
--    *******************************************************
--    Select code from the lines below if you want to view what
--        you will be removing in the code above
--    *******************************************************

--    Match Activity, DEX_SESSION, and SysProcesses tables
--        to find Activity not associated with SQL sessions/processes

    SELECT *
    FROM DYNAMICS..Activity
    WHERE SQLSESID NOT IN (
        SELECT SQLSESID
        FROM DYNAMICS..Activity A
        INNER JOIN tempdb..DEX_SESSION S
            ON A.SQLSESID = S.session_id
        INNER JOIN master..sysprocesses P
            ON S.sqlsvr_spid = P.spid
            AND A.USERID = P.loginame
    )

--    View orphaned entries in DEX_LOCK and DEX_SESSION

    SELECT *
    FROM tempdb..DEX_LOCK
    WHERE session_id NOT IN (
        SELECT SQLSESID
        FROM DYNAMICS..ACTIVITY
    )

    SELECT *
    FROM tempdb..DEX_SESSION
    WHERE session_id NOT IN (
        SELECT SQLSESID
        FROM DYNAMICS..ACTIVITY
    )

--    View orphaned entries in DYNAMICS..SY00800 (Batch Activity)

    SELECT * FROM DYNAMICS..SY00800
    WHERE DEX_ROW_ID NOT IN (
        SELECT B.DEX_ROW_ID
        FROM DYNAMICS..SY00800 B
        INNER JOIN DYNAMICS..ACTIVITY A
            ON B.USERID = A.USERID
            AND B.CMPNYNAM = A.CMPNYNAM
    )

--    View orphaned entries in DYNAMICS..SY00801 (Resource Activity)

    SELECT * FROM DYNAMICS..SY00801
    WHERE DEX_ROW_ID NOT IN (
        SELECT R.DEX_ROW_ID
        FROM DYNAMICS..SY00801 R
        INNER JOIN (
            DYNAMICS..ACTIVITY A
            INNER JOIN DYNAMICS..SY01500 C
                ON A.CMPNYNAM = C.CMPNYNAM)
            ON R.USERID = A.USERID
            AND R.CMPANYID = C.CMPANYID
    )

-- ***************************************************
-- ***************************************************

--    If all else fails, get everyone out and run the usual process:

--    MAKE SURE EVERYONE IS OUT OF THE SYSTEM before you run this:

    DELETE FROM DYNAMICS..SY00800
    DELETE FROM DYNAMICS..SY00801
    DELETE FROM DYNAMICS..ACTIVITY
    DELETE FROM tempdb..DEX_LOCK
    DELETE FROM tempdb..DEX_SESSION

---    View the contents of the above tables

    SELECT * FROM DYNAMICS..Activity
    SELECT * FROM tempdb..DEX_SESSION
    SELECT * FROM tempdb..DEX_LOCK
    SELECT * FROM DYNAMICS..SY00800
    SELECT * FROM DYNAMICS..SY00801

--    The currently connected SQL sessions
    SELECT * FROM master..sysprocesses


*/

SharePoint Permissions and Business Portal

I don't know if anyone else has the same headache when setting up Business Portal permissions, but I invariably miss something between the SharePoint permissions and the Business Portal permissions. Or the customer needs to change some access around from time to time.

Either it's not there or I've just missed it; I'm no expert in SharePoint by any means, so it could be either. With Active Directory, you just go to a user's "Member Of" tab and you see a list of the groups in which they are a member. In SharePoint, you can see a list of users in a group, but not not a list of groups for a user.

Another problem I fall into is forgetting to assign a user to the "BP Internal User" group.

Here are two simple and related scripts to help with both those problems. The first is to see which groups users are in. The second is to make sure all users are in the "BP Internal User" group.

(connecting to the SharePoint content database)

SELECT 
      tp_Title [User],
      title [Group]
FROM
      UserInfo U
LEFT JOIN GroupMembership GM
      ON U.tp_ID = GM.memberid
LEFT JOIN Groups G
      ON GM.groupid = G.id
 
-- WHERE tp_Title = 'some user'
ORDER BY
      [User],
      [Group]

SELECT 
      tp_Title [User], 
      title [Group]
FROM
      UserInfo U
LEFT JOIN (GroupMembership GM
      INNER JOIN Groups G
            ON GM.groupid = G.id
            AND G.title = 'BP Internal User'
      ) ON U.tp_ID = GM.memberid
ORDER BY
      [User]

DBCC for All Databases

Another bit of code I've come up with to make maintenance life easier at my work: running DBCC CHECKDB and DBCC UPDATEUSAGE for all Great Plains companies. I know the Maintenance Plan can do this, but sometimes you just need to run it manually. If you have a lot of companies, this can be a chore.

declare @DatabaseId varchar(255)

declare DatabaseLoop cursor for 
    select name from master..sysdatabases 
--        where name in (select INTERID from DYNAMICS..SY01500 union select 'DYNAMICS')
--        Uncomment previous line for only GP databases

open DatabaseLoop
fetch next from DatabaseLoop into @DatabaseId
while (@@fetch_status <> -1)
    begin

    /*Inner Loop for each Database*/

        RAISERROR (@DatabaseId, 0, 1) WITH NOWAIT;
        WAITFOR DELAY '000:00:05'
        DBCC UPDATEUSAGE(@DatabaseId)
        DBCC CHECKDB (@DatabaseId) WITH NO_INFOMSGS
        print CHAR(13)+CHAR(10)+'----------------'+CHAR(13)+CHAR(10)

    fetch next from DatabaseLoop into @DatabaseId 
    end

close DatabaseLoop
deallocate DatabaseLoop

Copying Great Plains Databases for Server Transfer or Test Environment

If you've ever done a server transfer for Microsoft Dynamics GP (Great Plains) you'll know how much effort it is just to move the databases to the new server and run the SQL scripts against them as described in the KB article (878449): https://mbs.microsoft.com/knowledgebase/kbdisplay.aspx?wtntzsmnwukntmmyqlytnsukzpxkmunvxxrmsqnmtquptkvo

We have a company with 80 company databases that we needed to copy into a test environment at our office to set up a complex report. There was no way I was going to do all those steps manually. Thus, the creation of a couple of handy SQL scripts to do the job for me.

The backup script - nothing spectacular here - it just sticks on our common file suffix.

/*
This script backs up all GP databases to a local or network location
and appends a specific file suffix.

If you get an error when running this script and there
are no typeos in the path, make sure your SQL Server
service logon user has appropriate rights to the path.
It does not use your login context to create the backups.

Change the @ variables to fit your needs

***************************
JP Davey
2009
***************************
*/

declare @DatabaseId varchar(100)
declare @SQL varchar(400)
declare @BackupFolder varchar(256)
declare @FileSuffix varchar(256)
declare @GPDatabase tinyint
declare @BackUpGPOnly char(1)

-- Only Back Up Dynamics GP databases: Y = Yes, N = No
-- This script does not restore the system databases (master, msdb, tempdb, model)
-- regardless of which option you select
set @BackUpGPOnly = 'Y'

-- The network (UNC) path where the databases will be backed up TO
set @BackupFolder = 'G:\Transfer\' -- must end with a \

-- The suffix after the database name in the backup filename
-- For example: a FileSuffix of '-ServerTransfer' would give a filename of
-- DYNAMICS-ServerTransfer.bak
set @FileSuffix = '-ServerTransfer'


declare DatabaseLoop cursor for
   SELECT
      RTRIM(LTRIM(name)) as name,
      (CASE WHEN name IN (select INTERID from DYNAMICS..SY01500 union select 'DYNAMICS')
         THEN 1
         ELSE 0 END) as GPDB
   FROM master..sysdatabases
   WHERE name not in ('master','model','msdb','tempdb')
open DatabaseLoop
fetch next from DatabaseLoop into @DatabaseId, @GPDatabase
while (@@fetch_status <> -1)
   begin

   /*Inner Loop for each Database*/
   IF @GPDatabase = 1 OR @BackUpGPOnly = 'N'
      BEGIN
         RAISERROR (@DatabaseId, 0, 1) WITH NOWAIT;
         SET @SQL = 'BACKUP DATABASE [' + @DatabaseId + '] TO DISK = N''' + @BackupFolder
-- ** if you want to create subfolders for each company, you will need to uncomment the following line
-- ** keep in mind that you will have to manually create the folders if they do not already exist
--          + @DatabaseId + '\'
            + @DatabaseId + @FileSuffix
            + '.bak'' WITH NOFORMAT, INIT, NAME = N'''
            + @DatabaseId + '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10'
         print @SQL
         EXEC(@SQL)
         print CHAR(13)+CHAR(10)+'----------------'+CHAR(13)+CHAR(10)
      END

   fetch next from DatabaseLoop into @DatabaseId, @GPDatabase
   end

close DatabaseLoop
deallocate DatabaseLoop

The restore script for restoring the databases and running all the GP scripts to the distination server

/*
This script is compatible with SQL 2005 and 2008

This script creates the script you should open and run on the
new/test server to restore and prepare the databases.
The output from this script is a script
to perform the following tasks:

Restore the databases from the @BackupFolder location
Run the Dex_Req.sql script on the master database
Run the Grant.sql script on all GP databases
Run the sp_changedbowner 'DYNSA' on all GP databases
Run the MassDropUserSchemas.sql script on all GP databases

After running this script on the existing SQL server,
copy/paste the output to the new SQL server and run it there.
Don't include the first line "(xx row(s) affected)".

If you are referencing the backup files from a network share
(UNC path) make sure your SQL Server service logon user has
appropriate rights to the network path.

If it fails on a couple lines, correct the output script on the new
server, commenting out the lines you don't want, and Execute the
script again. Because there are variables declared, you might not be
able to just select a few lines and Execute.
If it fails miserably, you probably don't have your files in
the correct path. Fix the path in this script, Execute, and copy
the script to the new server again.

Change the @ variables to fit your needs

***************************
JP Davey -- Using modified GP scripts
2009
***************************
*/

declare
@BackupFolder varchar(256),
@RestoreDataFileFolder varchar(256),
@RestoreLogFileFolder varchar(256),
@FileSuffix varchar(256),
@DatabaseId varchar(100),
@MDFName varchar(256),
@LDFName varchar(256),
@InDynamics tinyint,
@RestoreGPOnly char(1)

-- The local or network (UNC) path where the databases will be restored FROM
set @BackupFolder = 'D:\Transfer\' -- must end with a \

-- The local path where the Data (.mdf) files will be restored TO
set @RestoreDataFileFolder = 'C:\SQLData\Data\' -- must end with a \

-- The local path where the Log (.ldf) files will be restored TO
-- This may or may not be the same as the @RestoreDataFileFolder
set @RestoreLogFileFolder = 'C:\SQLData\Logs\' -- must end with a \

-- The suffix after the database name in the backup filename
-- For example: a FileSuffix of '-ServerTransfer' would give a filename of
-- DYNAMICS-ServerTransfer.bak
set @FileSuffix = '-ServerTransfer'

-- Restore Dynamics GP databases only: Y = Yes, N = No
-- This script does not restore the system databases (master, msdb, tempdb, model)
-- regardless of which option you select
set @RestoreGPOnly = 'Y'

-------------------------------------------------------------------------------
---------- Get a list of database files ---------------------------------------
-------------------------------------------------------------------------------

CREATE TABLE #GenScript_DBInfo
   ( DatabaseName VARCHAR(100),
   LogicalFileName varchar(100),
   LFType smallint)

DECLARE @command VARCHAR(5000)

SELECT @command = 'Use [' + '?' + '] SELECT
' + '''' + '?' + '''' + ' AS DatabaseName,
rtrim(sysfiles.name) AS LogicalFileName,
sysfiles.groupid as LFType
FROM dbo.sysfiles'
INSERT INTO #GenScript_DBInfo
   (DatabaseName,
   LogicalFileName,
   LFType)
EXEC sp_MSForEachDB @command

-------------------------------------------------------------------------------
---------- Restore the databases ----------------------------------------------
-------------------------------------------------------------------------------
print ''
print '-- Restore Databases --'
print ''

declare DatabaseLoop cursor for
   (SELECT
      DB.DatabaseName as DB,
      MFile.LogicalFileName as MDFName,
      LFile.LogicalFileName as LDFName,
      (CASE WHEN DB.DatabaseName IN (select INTERID from DYNAMICS..SY01500 union select 'DYNAMICS')
         THEN 1
         ELSE 0
       END) as GPDB
   FROM
      #GenScript_DBInfo DB
      LEFT JOIN
      #GenScript_DBInfo MFile
      ON DB.DatabaseName = MFile.DatabaseName
      AND MFile.LFType = 1
      LEFT JOIN
      #GenScript_DBInfo LFile
      ON DB.DatabaseName = LFile.DatabaseName
      AND LFile.LFType = 0
   WHERE
      DB.DatabaseName NOT IN ('master','model','tempdb','msdb')
      AND (@RestoreGPOnly = 'N'
         OR (@RestoreGPOnly = 'Y' AND DB.DatabaseName IN (select INTERID from DYNAMICS..SY01500 union select 'DYNAMICS')))
   GROUP BY
      DB.DatabaseName,
      MFile.LogicalFileName,
      LFile.LogicalFileName
   )

declare
@Prefix varchar(3),
@SuffixDat varchar(3),
@SuffixLog varchar(3)

open DatabaseLoop
fetch next from DatabaseLoop into @DatabaseId, @MDFName, @LDFName, @InDynamics
while (@@fetch_status <> -1)
   begin
      if @InDynamics = 1
         begin
            set @Prefix = 'GPS'
            set @SuffixDat = 'Dat'
            set @SuffixLog = 'Log'
         end
      else
         begin
            set @Prefix = ''
            set @SuffixDat = ''
            set @SuffixLog = ''
         end
      print 'RESTORE DATABASE [' + @DatabaseId + '] FROM DISK = N''' + @BackupFolder
-- ** if you created subfolders for each company, you will need to uncomment the following line
--       + @DatabaseId + '\'
         + @DatabaseId + @FileSuffix
         + '.bak'' WITH FILE = 1, MOVE N'''
         + @MDFName + ''' TO N''' + @RestoreDataFileFolder
         + @Prefix + @DatabaseId + @SuffixDat + '.mdf'', MOVE N'''
         + @LDFName + ''' TO N''' + @RestoreLogFileFolder
         + @Prefix + @DatabaseId + @SuffixLog + '.ldf'', KEEP_REPLICATION, NOUNLOAD, REPLACE, STATS = 10'
      fetch next from DatabaseLoop into @DatabaseId, @MDFName, @LDFName, @InDynamics
   end
close DatabaseLoop
deallocate DatabaseLoop

DROP TABLE #GenScript_DBInfo

-------------------------------------------------------------------------------
---------- Grab the logins and passwords --------------------------------------
-------------------------------------------------------------------------------

PRINT ''
PRINT '-- Add Users --'
PRINT ''

USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
   DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
   @binvalue varbinary(256),
   @hexvalue varchar(256) OUTPUT
AS
DECLARE @charvalue varchar(256)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)  BEGIN        DECLARE @tempint int    DECLARE @firstint int        DECLARE @secondint int        SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))        SELECT @firstint = FLOOR(@tempint/16)     SELECT @secondint = @tempint - (@firstint*16)     SELECT @charvalue = @charvalue +        SUBSTRING(@hexstring, @firstint+1, 1) +        SUBSTRING(@hexstring, @secondint+1, 1)     SELECT @i = @i + 1  END  SELECT @hexvalue = @charvalue  GO  IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL     DROP PROCEDURE sp_help_revlogin  GO  CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL  AS  DECLARE @name sysname  DECLARE @xstatus int  DECLARE @binpwd varbinary (256)  DECLARE @txtpwd sysname  DECLARE @tmpstr varchar (256)  DECLARE @SID_varbinary varbinary(85)  DECLARE @SID_string varchar(256)  IF (@login_name IS NULL)        DECLARE login_curs CURSOR FOR              SELECT sid, name, xstatus, password FROM master..sysxlogins              WHERE srvid IS NULL AND name <> 'sa'
ELSE
   DECLARE login_curs CURSOR FOR
      SELECT sid, name, xstatus, password FROM master..sysxlogins
      WHERE srvid IS NULL AND name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
IF (@@fetch_status = -1)
BEGIN
   PRINT 'No login(s) found.'
   CLOSE login_curs
   DEALLOCATE login_curs
   RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated '
   + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
PRINT 'DECLARE @pwd sysname'
WHILE (@@fetch_status <> -1)
BEGIN
   IF (@@fetch_status <> -2)
   BEGIN
      PRINT ''
      SET @tmpstr = '-- Login: ' + @name
      PRINT @tmpstr
      IF (@xstatus & 4) = 4
         BEGIN -- NT authenticated account/group
            IF (@xstatus & 1) = 1
               BEGIN -- NT login is denied access
                  SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''
                  PRINT @tmpstr
               END
            ELSE BEGIN -- NT login has access
               SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''
               PRINT @tmpstr
            END
         END
      ELSE BEGIN -- SQL Server authentication
         IF (@binpwd IS NOT NULL)
            BEGIN -- Non-null password
               EXEC sp_hexadecimal @binpwd, @txtpwd OUT
               IF (@xstatus & 2048) = 2048
                  SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'
               ELSE
                  SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'
               PRINT @tmpstr
               EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
               SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name + ''
                  + ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '
            END
         ELSE BEGIN
            -- Null password
            EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
            SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name + ''
               + ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '
         END
         IF (@xstatus & 2048) = 2048
            -- login upgraded from 6.5
            SET @tmpstr = @tmpstr + '''skip_encryption_old'''
         ELSE
            SET @tmpstr = @tmpstr + '''skip_encryption'''
            PRINT @tmpstr
         END
      END
      FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
   END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
IF OBJECT_ID ('seeMigrateSQLLogins') IS NOT NULL
   DROP PROCEDURE seeMigrateSQLLogins
GO
create procedure seeMigrateSQLLogins @login_name sysname = NULL
as
declare
   @name char(50),
   @binpwd varbinary (256),
   @txtpwd sysname,
   @tmpstr varchar (256),
   @SID_varbinary varbinary(85),
   @SID_string varchar(256),
   @Is_Policy bit,
   @Is_Exp bit,
   @type char(1),
   @Pol char(3),
   @Exp char(3)
set nocount on
create table #logins (
   [name] nvarchar(128) NOT NULL,
   [sid] varbinary(85) NOT NULL,
   [type] char(1) NOT NULL,
   [is_policy_checked] bit default 0,
   [is_expiration_checked] bit default 0,
   [password_hash] varbinary(256) )
insert #logins (name, sid, type)
select name, sid, type from sys.server_principals where
   (type_desc = 'SQL_LOGIN' or type_desc = 'WINDOWS_LOGIN') and name <> 'sa' and name <> 'NT AUTHORITY\SYSTEM'
update a set a.is_policy_checked = b.is_policy_checked, a.is_expiration_checked = b.is_expiration_checked, a.password_hash = b.password_hash
   from #logins a, sys.sql_Logins b
   where a.sid = b.sid
set nocount off
IF (@login_name IS NULL) --Not a single user, get the list
   DECLARE seelogin_curs CURSOR FOR
      SELECT name, sid, password_hash, type, is_policy_checked, is_expiration_checked FROM #logins
      WHERE name <> 'sa'
ELSE
   DECLARE seelogin_curs CURSOR FOR
      SELECT name, sid, password_hash, type, is_policy_checked, is_expiration_checked FROM #logins
      WHERE name = @login_name
OPEN seelogin_curs
FETCH NEXT FROM seelogin_curs INTO @name, @SID_varbinary, @binpwd, @type, @Is_Policy, @Is_Exp
IF (@@fetch_status = -1)
BEGIN
   PRINT 'No login(s) found.'
   CLOSE seelogin_curs
   DEALLOCATE seelogin_curs
END
SET @tmpstr = '/* seeMigrateSQLLogins - For SQL Server 2005 Only '
PRINT @tmpstr
SET @tmpstr = '** Generated '
   + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
   IF @type = 'S'
      BEGIN
         PRINT '/* SQL Login ******************/'
         EXEC sp_hexadecimal @binpwd, @txtpwd OUT
         EXEC sp_hexadecimal @SID_varbinary, @SID_string OUT
         IF @Is_Policy = 1 Begin set @Pol = 'ON' End ELSE Begin set @Pol = 'OFF' END
         IF @Is_Exp = 1 Begin set @Exp = 'ON' End ELSE Begin set @Exp = 'OFF' END
         SET @tmpstr = 'Create Login [' + rtrim(@name) + '] WITH PASSWORD = ' + @txtpwd + ' hashed, sid = ' + @SID_string + ', CHECK_POLICY = ' + @Pol + ', CHECK_EXPIRATION = ' + @Exp
         PRINT @tmpstr
         PRINT ''
      END
   Else
      BEGIN
         PRINT '/* SQL Login ******************/'
         EXEC sp_hexadecimal @SID_varbinary, @SID_string OUT
         SET @tmpstr = 'Create Login [' + rtrim(@name) + '] FROM WINDOWS; '
         PRINT @tmpstr
         PRINT ''
      END
      FETCH NEXT FROM seelogin_curs INTO @name, @SID_varbinary, @binpwd, @type, @Is_Policy, @Is_Exp
   END
CLOSE seelogin_curs
DEALLOCATE seelogin_curs
drop table #logins
GO

declare @version2005 char(5)
declare @version2008 char(5)

--Get the current version of SQL Server running
select @version2005 = substring(@@version,29,4)
select @version2008 = substring(@@version,35,4)

if @version2005 = '9.00'
   Begin
      exec seeMigrateSQLLogins
   End
Else if @version2008 = '10.0'
   Begin
      exec seeMigrateSQLLogins
   End
Else
   begin
      exec sp_help_revlogin
   End

IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
   DROP PROCEDURE sp_hexadecimal
GO
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
   DROP PROCEDURE sp_help_revlogin
GO
IF OBJECT_ID ('seeMigrateSQLLogins') IS NOT NULL
   DROP PROCEDURE seeMigrateSQLLogins
GO

-------------------------------------------------------------------------------
---------- Dex_Req.sql --------------------------------------------------------
-------------------------------------------------------------------------------
PRINT ''
PRINT '-- Dex_Req.sql --'
PRINT ''
print 'use tempdb'
print 'go'
print 'if exists (select * from dbo.sysobjects where id = Object_id(''.DEX_LOCK'') and type = ''U'')'
print 'begin'
print ' drop table DEX_LOCK'
print 'end'
print 'go'
print 'if exists (select * from dbo.sysobjects where id = Object_id(''.DEX_SESSION'') and type = ''U'')'
print 'begin'
print ' drop table DEX_SESSION'
print 'end'
print 'go'
print 'use master'
print 'go'
print 'if exists (select * from dbo.sysobjects where id = Object_id(''.smDEX_Build_Locks'') and type = ''P'')'
print 'begin'
print ' drop procedure smDEX_Build_Locks'
print 'end'
print 'go'
print 'create procedure smDEX_Build_Locks'
print 'as'
print 'exec (''create table tempdb..DEX_LOCK (session_id int, row_id int, table_path_name char(100))'')'
print 'exec (''create unique index PK_DEX_LOCK on tempdb..DEX_LOCK(row_id,table_path_name)'')'
print 'exec (''create table tempdb..DEX_SESSION (session_id int identity, sqlsvr_spid smallint)'')'
print 'exec (''create unique index PK_DEX_SESSION on tempdb..DEX_SESSION(session_id)'')'
print 'exec (''use tempdb grant insert,update,select,delete on DEX_LOCK to public'')'
print 'exec (''use tempdb grant insert,update,select,delete on DEX_SESSION to public'')'
print 'return'
print 'go'
print 'sp_procoption ''smDEX_Build_Locks'',''startup'',''true'''
print 'go'
print 'smDEX_Build_Locks'
print 'go'

-------------------------------------------------------------------------------
---------- Grant.sql and sp_changedbowner 'DYNSA' -----------------------------
-------------------------------------------------------------------------------

PRINT ''
PRINT '-- Grant.sql, sp_changedbowner, and remove user schemas --'
PRINT ''

declare @DatabaseId varchar(8)

declare DatabaseLoop cursor for
   select name from master..sysdatabases where name in
      (select INTERID from DYNAMICS..SY01500 union select 'DYNAMICS')

open DatabaseLoop
fetch next from DatabaseLoop into @DatabaseId

print 'declare @cStatement varchar(255)'
print 'declare @UserId sysname'

while (@@fetch_status <> -1)
   begin
      print ''
      print '-----------------------------------------'
      print 'print ''Security for ' + @DatabaseId + ''''
      print 'USE [' + @DatabaseId + ']'
      print ''
      print 'declare G_cursor CURSOR for select ''grant select,update,insert,delete on ['' + convert(varchar(64),name) + ''] to DYNGRP'' from sysobjects'
      print ' where (type = ''U'' or type = ''V'') and uid = 1'
      print 'set nocount on'
      print 'OPEN G_cursor'
      print 'FETCH NEXT FROM G_cursor INTO @cStatement'
      print 'WHILE (@@FETCH_STATUS <> -1)'
      print 'begin'
      print ' EXEC (@cStatement)'
      print ' FETCH NEXT FROM G_cursor INTO @cStatement'
      print 'end'
      print 'DEALLOCATE G_cursor'
      print 'declare G_cursor CURSOR for select ''grant execute on ['' + convert(varchar(64),name) + ''] to DYNGRP'' from sysobjects'
      print ' where type = ''P'''
      print 'set nocount on'
      print 'OPEN G_cursor'
      print 'FETCH NEXT FROM G_cursor INTO @cStatement'
      print 'WHILE (@@FETCH_STATUS <> -1)'
      print 'begin'
      print ' EXEC (@cStatement)'
      print ' FETCH NEXT FROM G_cursor INTO @cStatement'
      print 'end'
      print 'DEALLOCATE G_cursor'
      -- Change Owner
      print ''
      print 'exec sp_changedbowner ''DYNSA'''
      -- Remove user schemas
      print ''
      print 'declare UserIDLoop cursor for select USERID from DYNAMICS..SY01400'
      print 'open UserIDLoop'
      print 'fetch next from UserIDLoop into @UserId'
      print 'while (@@fetch_status <> -1)'
      print ' begin'
      print ' IF EXISTS (SELECT * FROM sys.schemas WHERE name = @UserId)'
      print ' exec(''DROP SCHEMA '' + @UserId)'
      print ' fetch next from UserIDLoop into @UserId'
      print ' end'
      print 'close UserIDLoop'
      print 'deallocate UserIDLoop'

   fetch next from DatabaseLoop into @DatabaseId
   end
close DatabaseLoop
deallocate DatabaseLoop

Needless to say, this is a "use at your own risk" sort of thing.

Hello World!

Welcome to the first blog of many. I'll be publishing all sorts of things ranging from technical issues I have run into, programming code (VB.NET mostly), technical articles, photography articles, and anything else deemed bloggable. I've been in the IT industry since the late 90's, but I've been heavily tinkering - mostly self-taught - with computers since the mid 80's (my father was a manager at a TV station, so that helped). I have an MCSE, BMA Specialist (Microsoft Great Plains), and a brand new MCST (SQL 2005) certification under my belt. I enjoy graphic design and photography as a hobby, when I have time. Hope you enjoy the blog!