Nezinu, cik bieži (vai reti) gadās sastapt nepieciešamību izdzēst vairākus datubāzes lietotājus. Tā kā man šorīt darbā šāda vajadzība radās un ātrumā nevarēju iedomāties pareizos atslēgas vārdus, lai atrastu skriptu, kas veic šo darbību, tad nācās patērēt nedaudz laika, lai tādu uzrakstītu.
Tātad, ideja ir vienkārša. Ņemam lietotājus no sistēmas skata sys.sysusers, atfiltrējam pēc kādiem kritērijiem dzēsīsim. Sasaistam rezultātu ar sys.schemas skatu, lai pārbaudītu, vai lietotājs nav saimnieks kādai shēmai. Šāda pārbaude ir nepieciešama tādēļ, ka MS SQL serveris neļauj dzēst lietotājus, kuriem ir kādas shēmas saimnieki. Tālāk jau paliek "ģelo tehņiki".
Rezultāta skripts:
/********************************************************************
* SP for droping users from database *
* (c) 2008 Ivars "PiRX" Āriņš *
* e-mail: ivars_a@inbox.lv *
* !!!NB:Use it on your own risk!!! *
*==================================================================*
* Usage: *
* EXEC dbo.CleanUsers *
* @WhereClause = '' *
* @ExecDrop = '' ; *
* Prameters: *
* @WhereClause - WHERE clause for user filtering from sys.sysusers *
* @ExecDrop - Indicates whether DROP actualy must be executed *
* (Y - exec DROP, any other value - just simulate) *
********************************************************************/
CREATE PROCEDURE dbo.CleanUsers
WhereClause nvarchar(300),
@ExecDrop nchar(1)
AS
BEGIN
SET NOCOUNT ON
IF NOT @ExecDrop = 'Y'
PRINT 'Running in simulation mode';
CREATE TABLE #FilteredUsers (id int);
DECLARE @UsersSql nvarchar(400);
SET @UsersSql = 'INSERT INTO #FilteredUsers SELECT uid FROM sys.sysusers ';
IF LEN(@WhereClause) > 0
SET @WhereClause = 'WHERE ' + @WhereClause + ';';
SET @UsersSql = @UsersSql + @WhereClause;
PRINT 'Filtering users using query:';
PRINT ' SELECT uid FROM sys.sysusers ' + @WhereClause
EXEC(@UsersSql);
DECLARE curs_users CURSOR LOCAL FORWARD_ONLY
FOR SELECT DISTINCT
u_id = u.uid, --id
u_name = u.name,
s_id = s.schema_id,
s_name = s.name,
s_objects = (SELECT COUNT(*) FROM sys.all_objects o WHERE o.schema_id = s.schema_id)
FROM #FilteredUsers f
INNER JOIN sys.sysusers u ON f.id = u.uid
LEFT JOIN sys.schemas s ON s.principal_id = u.uid;
OPEN curs_users;
DECLARE @u_id int,
@u_name nvarchar(255),
@s_id int,
@s_name nvarchar(255),
@s_objects int;
FETCH NEXT FROM curs_users INTO @u_id, @u_name, @s_id, @s_name, @s_objects;
WHILE @@FETCH_STATUS = 0
BEGIN
--DROP schema if exists and containts no objects
IF @s_name IS NOT NULL
IF (@s_objects = 0)
BEGIN
PRINT 'Droping schema ' + @s_name;
IF @ExecDrop = 'Y'
EXEC ('DROP SCHEMA [' + @s_name + '];');
SET @s_objects = 0;
END
--DROP user if doesn't own any objects
IF (@s_objects > 0)
PRINT 'Not droping user ' + @u_name + ' because owned schema ' + @s_name + ' contains '
+ CAST(@s_objects AS nvarchar(10)) + ' object(s)';
ELSE
BEGIN
PRINT 'Droping user ' + @u_name;
IF @ExecDrop = 'Y'
EXEC ('DROP USER [' + @u_name + '];');
END
FETCH NEXT FROM curs_users INTO @u_id, @u_name, @s_id, @s_name, @s_objects;
END
CLOSE curs_users;
DEALLOCATE curs_users;
DROP TABLE #FilteredUsers
END
GO
Procedūru izsaucot, tai nodod divus parametrus, @WhereClause, kurā norāda derīgu SQL WHERE nosacījumu (bez atslēgvārda WHERE) un @ExecDrop, ar kuru norāda, vai tiešām izpildīt lietotāju un shēmu dzēšanu, vai tika simulēt skripta darbību, neizpildot DROP operāciju.
Piemēram, lai izdzēstu visus lietotājus ar Windows autentifikāciju, kuru lietotājvārdi nestaur Ivars, skripts ir jāpalaiž šādi:
--Simulācija
EXEC dbo.CleanUsers
@WhereClause = N'isntuser = 1 AND name NOT LIKE ''%Ivars%''',
@ExecDrop = 'N';
GO
--Izpilde
EXEC dbo.CleanUsers
@WhereClause = N'isntuser = 1 AND name NOT LIKE ''%Ivars%''',
@ExecDrop = 'Y';
GO
P.S. Nepretendēju uz T-SQL guru statusu, tāpēc pieļauju, ka šo visu varētu uzrakstīt labāk. Manām vajadzībām derēja arī šādi.
Laikam jau zemrindas piezīmes man sāk kļūt par ieradumu. Nekas, būs konsistents veids, kā informēt par dažādām bloga saimnieciskajām lietām. Joprojām gaidu jebkādus komentārus un atsauksmes par rakstīto.
Šodienas ziņa ir tāda, ka esmu pieslēdzis FeedBurner statistiku un tāpēc ar pirmdienu atslēgšu tiešo RSS plūsmu. Tāpēc, lūdzu, pārslēdziet savus lasītājus uz http://feeds.feedburner.com/IvaraBlogs