none
2016->2017 Migration. Manuell geht es, im Job/Trigger nicht. RRS feed

  • Frage

  • Hallo Zusammen,

    wir haben hier intern einen Job/Trigger, welche einen bestimmten Domainenuser automatisch jeder neuen oder wiederhergestellten DB auf einem MSSQL 2017 Server mit einer Reader-Rolle hinzufügen. Auf dem alten MSSQL Server 2016 funktionierte das immer ohne Probleme. Auf dem neuen funktioniert es nur in sofern der User der neuen/wiedergergestellten DB hinzugefügt wird, aber nur als inaktiv. Wenn man manuell im Nachhinein "GRANT CONNECT TO [domain\user];" ausführt wird er direkt aktiv und hat die richtige Rolle. Wenn man den gleichen Befehl in dem Skript->Job/Trigger ausführt, dann bringt das leider nichts. Der User ist da, aber immer inaktiv. Bis man wieder manuell den Grant anstößt.

    Ist das ein Sicherheitsfeature? Hat Jemand eine Idee woran das liegen kann?

    Hier mal das Skript, was diesen Job/Trigger einrichtet:

    USE model

    GO

     

    EXEC sp_addrolemember 'db_datareader',[domain\user]

    GO

     

    USE [msdb]

    GO

     

    /****** Object:  Job [setdb_reader]    Script Date: 11.12.2018 14:58:22 ******/

    BEGIN TRANSACTION

    DECLARE @ReturnCode INT

    SELECT @ReturnCode = 0

    /****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 11.12.2018 14:58:22 ******/

    IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)

    BEGIN

    EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

     

    END

     

    DECLARE @jobId BINARY(16)

    EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'setdb_reader',

                                   @enabled=1,

                                   @notify_level_eventlog=0,

                                   @notify_level_email=0,

                                   @notify_level_netsend=0,

                                   @notify_level_page=0,

                                   @delete_level=0,

                                   @category_name=N'[Uncategorized (Local)]',

                                   @owner_login_name=N'sa', @job_id = @jobId OUTPUT

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    /****** Object:  Step [step 1]    Script Date: 11.12.2018 14:58:22 ******/

    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'step 1',

                                   @step_id=1,

                                   @cmdexec_success_code=0,

                                   @on_success_action=1,

                                   @on_success_step_id=0,

                                   @on_fail_action=2,

                                   @on_fail_step_id=0,

                                   @retry_attempts=0,

                                   @retry_interval=0,

                                   @os_run_priority=0, @subsystem=N'TSQL',

                                   @command=N' declare @sql as nvarchar(max)

     

     

                    declare @databasename as nvarchar(max)

     

                    select top 1 @databasename = destination_database_name from msdb.dbo.restorehistory order by restore_history_id desc

     

                    set @sql =N''Use [@databasename] drop user if exists [domain\user]    

    create user [domain\user] for login [domain\user]

    alter role db_datareader add member [domain\user]''

     

                    set @sql=replace (@sql,N''@databasename'',@databasename)

     

                    exec  (@sql)

    ',

                                   @database_name=N'msdb',

                                   @flags=0

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    COMMIT TRANSACTION

    GOTO EndSave

    QuitWithRollback:

        IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

    EndSave:

    GO

     

    USE [msdb]

    GO

    DECLARE @jobIdBK uniqueidentifier

    SELECT @jobIdBK = job_id FROM msdb.dbo.sysjobs

    WHERE name = 'setdb_reader'

     

    EXEC msdb.dbo.sp_add_alert @name=N'setuser',

                 @message_id=18267,

                 @severity=0,

                 @enabled=1,

                 @delay_between_responses=2,   

                 @include_event_description_in=0,

                 @category_name=N'[Uncategorized]',

                 @job_id=@jobIdBK

     

    GO


    Dienstag, 13. August 2019 09:35

Antworten

  • Wir haben es gefunden. Der Fehler saß leider vor dem Bildschirm...

    Der Alert reagiert natürlich nur bei einem Restore!! einer Datenbank, nicht bei einem Create.

    Bei Create kam der User aus der Model Datenbank, und da fehlte dann das Grant Connect. Einmal gesetzt und es ging.

    VG&DANKE

    • Als Antwort markiert bjoern.k Mittwoch, 14. August 2019 10:20
    Mittwoch, 14. August 2019 10:20

Alle Antworten

  • Du musst in der Prozedur einen Admin mit Berechtigung aufrufen:
    https://docs.microsoft.com/de-de/sql/t-sql/statements/setuser-transact-sql?view=sql-server-2017

    Dann kannst du ggf. auch den Grant direkt selber machen.
    Keine Angst: SETUSER bleibt auf die SP beschränkt.

    Dienstag, 13. August 2019 10:50
  • Du musst in der Prozedur einen Admin mit Berechtigung aufrufen:
    https://docs.microsoft.com/de-de/sql/t-sql/statements/setuser-transact-sql?view=sql-server-2017

    Dann kannst du ggf. auch den Grant direkt selber machen.
    Keine Angst: SETUSER bleibt auf die SP beschränkt.

    Welche Prozedur?

    Das Skript legt einen Agent Job an und dazu einen Alert, der bei einem Restore einer DB den Job triggert.

    BTW: SETUSER ist abgekündigt.  EXECUTE AS wäre der zu verwendende Befehl.


    May you never suffer the sentiment of spending a day without any purpose

    Dienstag, 13. August 2019 13:55
  • Hi Bjoern,

    interessanterweise kann ich das von Dir geschilderte Verhalten nicht nachstellen. Ich hab das Ganze gerade mal durch gespielt auf einem 2017er und bei mir ist der DB Benutzer NICHT als inaktiv markiert.

    Der Job gehört sa, das sieht man ja im Skript.

    In der Job Historie wird ja drin stehen Job executed as ....   Ich gehe davon aus, dass hier das DiensteKonto des SQL Server Agent drin steht. Wenn hier an den Berechtigungen nichts gedreht wurde, dann müsste das ausführende Konto intern ja auch der Rolle sysadmin zugewiesen sein.

    Ich vermute, dass dennoch irgendwo bei den Berechtigungen "der Hund begraben" liegt.

    Sprich: innerhalb der DB kann zwar der User angelegt werden, aber das mapping erfolgt nicht sauber oder so. Aber im Moment hab ich noch nicht die Idee, wie ich das nachstellen kann für eine Erklärung.


    May you never suffer the sentiment of spending a day without any purpose

    Dienstag, 13. August 2019 13:58
  • Hallo Dirk,

    schon mal vielen Dank für's Nachstellen. Ich hab hier leider gerade nur einen 2017er. Es ist echt komisch. Alles andere auf dem Server passt / läuft gut, und bei allen alten Servern tut es das auch.

    Was wohl bei mir passiert, dass "hasdbaccess" bei dem Trigger/Job auf dem neuen Server nicht gesetzt wird. Sobald ich aber "grant CONNECT TO [SWK\scom-manager]" danach manuell ausführe klappt es direkt. Owner vom Job is der "sa". Schaue morgen noch mal alle Berechtigungen/Rechte/Rollen usw durch, und habe den Link zu dem Thread meinem Head DB Entwickler mal gegeben.

    Dienstag, 13. August 2019 14:17
  • Wir haben es gefunden. Der Fehler saß leider vor dem Bildschirm...

    Der Alert reagiert natürlich nur bei einem Restore!! einer Datenbank, nicht bei einem Create.

    Bei Create kam der User aus der Model Datenbank, und da fehlte dann das Grant Connect. Einmal gesetzt und es ging.

    VG&DANKE

    • Als Antwort markiert bjoern.k Mittwoch, 14. August 2019 10:20
    Mittwoch, 14. August 2019 10:20
  • Hi Bjoern,

    und ich hatte echt schon angefangen an mir zu zweifeln. Mir ist partout kein Weg eingefallen, um das geschilderte Problem nachzustellen.

    An Layer 8 hab ich an der Stelle gar nicht gedacht.  ;-)

    Gruß Dirk


    May you never suffer the sentiment of spending a day without any purpose

    Donnerstag, 15. August 2019 12:20