IMPORTANT:
Commands with UPDATE, DELETE or DROP can ruin your data! Please use these queries with caution. We highly recommend that you backup your database before trying out these commands and to load them up first on a test server. Use at your own risk.

Associated SQL Command:
/* ** Drop_Users_Company_MSDE.sql ** ** This script will remove all users from the DYNGRP in the company database ** specified. It will then drop the DYNGRP and readd the DYNGRP to the company. ** It will then add all users back to the DYNGRP based on the SY60100 table. ** NOTE: You will need to replace %Companydb% with the company database ** name. */ declare @cStatement varchar(255) /* Misc exec string */ declare @DynDB varchar(15) /* DB Name exec string */ declare @DYNGRPgid int /* Id of DYNGRP group */ /* ** Loop through all company databases, emptying the DYNGRP group. */ SET QUOTED_IDENTIFIER OFF use %Companydb% select @DYNGRPgid = (select gid from sysusers where name = 'DYNGRP') declare G_cursor CURSOR for select "sp_dropuser [" + name+"]" from sysusers where gid = @DYNGRPgid and name <> 'DYNGRP' set nocount on OPEN G_cursor FETCH NEXT FROM G_cursor INTO @cStatement WHILE (@@FETCH_STATUS <> -1) begin EXEC (@cStatement) FETCH NEXT FROM G_cursor INTO @cStatement end DEALLOCATE G_cursor /* ** Do not delete the group to attempt to preserve the permissions already ** granted to it. */ if exists (select gid from sysusers where name = 'DYNGRP') begin exec sp_dropgroup DYNGRP end /* ** Recreate the DYNGRP group in all company databases. */ use %Companydb% if not exists (select name from sysusers where name = 'DYNGRP') begin exec ("sp_addgroup DYNGRP") end
Viewed: 287541 times

Submitted by: Scotty

View complete list

previous script

next script