Comments (10)

  1. Neat use of the system function, here is one use I used it for. If a DBA leaves for exmaple and owns any db’s on the giant farm (which they should not own in the first place) databases and job owners become NULL once the id is disabled at the domain level. So I am the resident scripter of my group and kinda clean it all up.

    set nocount on
    use master
    go
    Declare @srvname varchar(55)
    Declare @exec_stmt nvarchar(4000)
    Select @srvname = @@servername
    Print @srvname
    DECLARE @name varchar(255), @return int,@original_Owner varchar(60),@owner_sid varchar(50),
    @sqltxt varchar(255)
    DECLARE c CURSOR
    READ_ONLY FOR

    Select [name],SUSER_SNAME(sid) from master..sysdatabases
    where SUSER_SNAME(sid) is null –and [name] ‘test db’

    OPEN c

    FETCH NEXT FROM c INTO @name,@owner_sid
    WHILE (@@fetch_status -1)
    BEGIN
    –Print @name
    –Print @owner_sid
    /*
    Select @sqltxt = ‘ USE ‘
    Select @sqltxt = @sqltxt + @name + ‘ go’
    Select @sqltxt = @sqltxt + ‘ sp_changedbowner sa’
    print @sqltxt
    execute @sqltxt
    */
    set @exec_stmt = ‘alter authorization on database::’ + quotename(@name) + ‘ to ‘ + quotename(‘sa’ )
    exec (@exec_stmt)

    if @@error = 0
    begin

    Print ‘Owner of Database ‘ + @name + ‘ successfuly altered from owner NULL to sa login’

    end
    else
    begin
    RAISERROR (‘Owner of DB not altered’, 16, 1)
    end

    FETCH NEXT FROM c INTO @name,@owner_sid
    END

    CLOSE c
    DEALLOCATE c
    GO

  2. here is one use to fix a job where owner = guy that left before it is null and fails or just fials due to invalid id as owner

    set nocount on
    use msdb
    go
    Declare @srvname varchar(55)
    Select @srvname = @@servername
    Print @srvname
    DECLARE c CURSOR
    READ_ONLY
    FOR select name,owner_sid from msdb..sysjobs –where owner_sid 0x01
    where SUSER_SNAME(owner_sid) = ‘DomainguyThatLeftId’
    DECLARE @name varchar(255), @return int,@original_Owner varchar(60),@owner_sid varchar(50)
    OPEN c

    FETCH NEXT FROM c INTO @name,@owner_sid
    WHILE (@@fetch_status -1)
    BEGIN
    exec msdb..sp_update_job @job_name = @name , @owner_login_name =’sa’
    –select @original_Owner = name from master..syslogins where sid = @owner_sid

    if @@error = 0
    begin

    Print ‘Owner of Job ‘ + @name + ‘ successfuly altered from orig owner to sa login’

    end
    else
    begin
    RAISERROR (‘Owner of Job not altered’, 16, 1)
    end

    FETCH NEXT FROM c INTO @name,@owner_sid
    END

    CLOSE c
    DEALLOCATE c
    GO

  3. This will find owners and not make any changes:

    Set nocount ON
    Select Convert(varchar(25),@@servername) as ‘Server_Name Checked below:’
    select
    Convert(varchar(55),[name]) as ‘Job_Name’,
    Convert(varchar(25),SUSER_SNAME(owner_sid)) AS Owner_name,
    LEFT([date_created],11)AS Date_Created,
    LEFT([date_modified],11)AS Date_Modified,
    version_number
    from msdb.dbo.sysjobs where SUSER_SNAME(owner_sid) is NULL
    ORDER BY [Owner_name]
    Print ”
    Print ‘##################################################################’
    Print ”

    1. Thanks Ed. I have a script for that too. Do you have a blog that you post these to? If so, what is it?

  4. What tool do you prefer to use to crack open the T-Log?

    1. I don’t crack open the transaction log. I use the built-in functions fn_dblog, fn_dumpdblog, or DBCC Log.

      1. I just kinda write them as I need them.
        I use to lecture and post but got busy with an Autistic daughter for a fews years correcting that. Very nice slick scripts you posted above.
        Ed

        1. I know what you mean. I have an autistic nephew who lived with me until he was 8 years-old.

  5. […] Looking for Buried Treasure in the Transaction Log – How to inspect the transaction log, courtesy of Robert L. Davis(Blog|Twitter), to find out things such as who altered permissions on logins. […]

  6. […] to James Thurber, SQL Soldier (for the buried treasure image), exception-makers,  kind commitment-breakers, and to you, […]

Leave a Reply

Your email address will not be published. Required fields are marked *