Lietotāju pielikšanas un tiesību piešķiršanas skripta ģenerācija

Produkcijas un testa datu bāzēs lietotāji un to tiesības parasti atšķiras. Lai aktualizētu datus, testa datu bāzi mēdz laiku pa laikam atjaunot no produkcijas datu bāzes rezerves kopijas.

Šajā rakstā skripts, ar kuru var uzģenerēt skriptu lietotāju pielikšanai un tiesību piešķiršanai datu bāzē.

Doma par procesu:

• Ir testa datu bāze, kurā ir pielikti lietotāji-testētāji un ir produkcijas datu bāze (lietotāju daudzums un to tiesības tajā interesē tikai tik tālu, ka tās nesakrīt ar testa vidi). Vajag aktualizēt ar jaunākajiem datiem testa datu bāzi.
• Pirms datu bāzes atjaunošanas, uzģenerē skriptu lietotāju pielikšanai un tiesību piešķiršanai (tālāk rakstā). Šis skripts nepievieno lietotājus serverim, uz kura tiks atjaunota datu bāze. Ja tie ir SQL lietotāji- tad varētu noderēt links no raksta: Lietotāju pārnešana uz citu serveri.
• Atjauno datu bāzi un izdzēš no tās visus lietotājus (lietotāju un shēmu dzēšanas skripta ģenerācija raksta beigās).
• Izmanto ģenerēto skriptu lietotāju pielikšanai un tiesību piešķiršanai.

Skripts nepretendē uz universālu pielietojumu un tajā apstrādāti tikai tie gadījumi, kas man bija svarīgi. Piemēram, tiek norādīta noklusētā shēma, bet shēma netiek izveidota, ja tas svarīgi, skripts jāpapildina. Savukārt Select/Insert/Update/Delete un Execute tiesības konkrētiem objektiem tiek ģenerētas.

Tātad skripts, kas reāli datu bāzē neko nemaina, tikai izvada ziņojumu logā skriptu, ko tālāk var izmantot lietotāju pielikšanai. Pirms laišanas der apskatīt un novērtēt, vai tas jums maz der.:

print
'/*
    Versija: 1.0
    Izveidots: 2011-05-18
    Autors: Jānis D.
    Komentārs: Paredzēts skripta ģenerācijai, kas
                    1. pievieno datu bāzei lietotājus;
                    2. lietotājus pievieno lomām;
                    3. lietotājiem piešķir tiesības.
    * Skripts ģenerē izveides skriptus tikai tiem lietotājiem, kuriem ir piekļuve serverim.
    * Skripts nemāk piešķirt visas tiesības lietotājiem. Tiek izvadīts ziņojums, ja ir kāds neapstrādāts gadījums.
*/'

declare @user_name nvarchar(100);
declare @com nvarchar(max);
declare @sid varbinary(85);

Set @com = '';
Select @com = dp.name + char(13)
From sys.sysprotects sp
    Inner Join sys.sysobjects so on sp.id = so.id
    Inner Join sys.database_principals dp on user_sid(sp.uid) = dp.sid
    Inner Join sys.server_principals spr ON spr.sid = dp.sid
Where dp.type in ('S', 'U')
    And (sp.protecttype Not In (205,206) OR sp.action Not In (193,195,196,197,224));

IF (@com != '')
Begin
    Print '/*'
    Print 'Uzmanību!!! Šiem lietotājiem nav automātiski ģenerētas visas objektu tiesības:';
    Print @com;
    Print '*/'
End
   
declare cur cursor for
    Select dp.sid, dp.name,
        'CREATE USER [' + dp.name + '] FOR LOGIN [' + sp.name + '] WITH DEFAULT_SCHEMA=[' + default_schema_name + '];' s
    From sys.server_principals sp
        Inner Join sys.database_principals dp ON sp.sid = dp.sid
    Where dp.type in ('S', 'U') -- S=SQL_LOGIN, U=WINDOWS_LOGIN
        And (dp.principal_id between 5 And 16383 Or dp.principal_id between 16400 And 2147483647) -- http://msdn.microsoft.com/en-us/library/ms403629.aspx

open cur                    
fetch next from cur into @sid, @user_name, @com
while @@fetch_status = 0
begin
    print '-- Lietotājs: ' + @user_name;
    print @com;

    Set @com = '';
    Select @com = @com +
        '  EXEC sp_addrolemember N''' + User_Name(groupuid) + ''', N''' + User_Name(memberuid) +''';' + char(13)
    From sys.sysmembers
    Where user_sid(memberuid) = @sid
    print @com;
   
    Set @com = '';
    Select @com = @com +
        CASE sp.protecttype
            WHEN 205 THEN '  GRANT'
            WHEN 206 THEN '  REVOKE'
        END + ' ' +
        CASE sp.action
            WHEN 193 THEN 'SELECT'
            WHEN 195 THEN 'INSERT'
            WHEN 196 THEN 'UPDATE'
            WHEN 197 THEN 'DELETE'
            WHEN 224 THEN 'EXEC'
        END + ' On ' + so.Name + ' To ' + dp.Name + ';' + char(13)
    From sys.sysprotects sp
        Inner Join sys.sysobjects so on sp.id = so.id
        Inner Join sys.database_principals dp on user_sid(sp.uid) = dp.sid
        Inner Join sys.server_principals spr ON spr.sid = dp.sid
    Where dp.type in ('S', 'U')
        And sp.protecttype In (205,206) AND sp.action In (193,195,196,197,224)
        And dp.sid = @sid
    Order By so.Name, dp.Name

    IF(@com != '')
        print @com;
    fetch next from cur into @sid, @user_name, @com
end

close cur;
Deallocate cur;

Lai ar iepriekšējo skriptu uzģenerēto skriptu varētu pielietot, no datu bāzes jāizdzēš visi lietotāji. Arī tam var uzģenerēt skriptu (atkal- skripts nepretendē uz "universālu" pielietojumu):

declare @name nvarchar(100);
declare @principal_id int;
declare @com nvarchar(max);
declare cur cursor for
            Select Name, principal_id From sys.database_principals dp
            Where Type = 'U'
            And (dp.principal_id between 5 And 16383 Or dp.principal_id between 16400 And 2147483647)

open cur                    
fetch next from cur into @name, @principal_id
while @@fetch_status = 0
begin
    Set @com = '
        if (select count(*) from sys.schemas Where principal_id = ' + cast(@principal_id as nvarchar) + ') > 0
        Drop Schema [' + @name + '];
        Drop User [' + @name + ']';
    print '-- dzēsīs: ' + @name;
    Print (@com);   
    fetch next from cur into @name, @principal_id
end

close cur;
Deallocate cur;

Vēl varētu piebilst, ka darbojoties ar visām testēšanas lietām aktīvi izmantoju snapšotus (SnapShot izmantošana testēšanā).