Check owners, collations and backups

  • paul_richardson

    SSCarpal Tunnel

    Points: 4594

    Comments posted to this topic are about the item Check owners, collations and backups

  • dr.kusnadi

    Say Hey Kid

    Points: 696

    I can't see the script. is this only me?

  • paul_richardson

    SSCarpal Tunnel

    Points: 4594

    hi,

    the script seems to have disappeared. I've re-added it, but it will be a few days until it's approved, So check back then.

    Thanks

    Paul

  • dr.kusnadi

    Say Hey Kid

    Points: 696

    Thanks Paul. 🙂

  • Gennadi Gretchkosiy-440661

    SSC Rookie

    Points: 38

    Hi

    The script

    -- check whether database owners are valid

    select name into #check_database_owners

    from master.dbo.sysdatabases

    where isnull(suser_sname(sid),'no_owner') = 'no_owner'

    doesn't look like a valid.

    This one works better:

    IF SUBSTRING(@@version,23,4)='2000'

    select SD.name as DBname,

    suser_sname(SD.sid) as DBowner,

    SL.name as USERname,

    'Notfor2000' as LOGINname,

    DD.name as DEFAULTdatabase

    from master.dbo.sysdatabases SD left join master.dbo.sysxlogins SL

    on SD.sid = SL.sid

    left join master.dbo.sysdatabases DD on SL.dbid = DD.dbid

    WHERE SL.name is null

    ELSE

    select SD.name as DBname,

    suser_sname(SD.owner_sid) as DBowner,

    SL.name as USERname,

    SL.loginname as LOGINname,

    SL.dbname DEFAULTdatabase

    from master.sys.databases SD left join master.sys.syslogins SL

    on SD.owner_sid = SL.sid

    WHERE SL.name is null

  • Gennadi Gretchkosiy-440661

    SSC Rookie

    Points: 38

    Orphan users should be considered as well

    CREATE TABLE #Results

    ([Database Name] sysname COLLATE Latin1_General_CI_AS,

    [Orphaned User] sysname COLLATE Latin1_General_CI_AS)

    SET NOCOUNT ON

    DECLARE @DBName sysname, @Qry nvarchar(4000)

    SET @Qry = ''

    SET @DBName = ''

    WHILE @DBName IS NOT NULL

    BEGIN

    SET @DBName = (SELECT MIN(name)

    FROM master..sysdatabases

    WHERE name NOT IN

    ('master', 'model', 'tempdb', 'msdb',

    'distribution', 'pubs', 'northwind')

    AND DATABASEPROPERTY(name, 'IsOffline') = 0

    AND DATABASEPROPERTY(name, 'IsSuspect') = 0

    AND name > @DBName)

    IF @DBName IS NULL BREAK

    IF SUBSTRING(@@version,23,4)='2000'

    SET @Qry = ' SELECT ''' + @DBName + ''' AS [Database Name],

    CAST(name AS sysname) COLLATE Latin1_General_CI_AS AS [Orphaned User]

    FROM ' + QUOTENAME(@DBName) + '..sysusers su

    WHERE su.islogin = 1

    AND su.name <> ''guest''

    AND NOT EXISTS

    (

    SELECT 1

    FROM master..sysxlogins sl

    WHERE su.sid = sl.sid

    )'

    ELSE

    SET @Qry = ' SELECT ''' + @DBName + ''' AS [Database Name],

    CAST(name AS sysname) COLLATE Latin1_General_CI_AS AS [Orphaned User]

    FROM ' + QUOTENAME(@DBName) + '..sysusers su

    WHERE su.islogin = 1

    AND (su.name <> ''guest''

    AND su.name <> ''INFORMATION_SCHEMA''

    AND su.name <> ''dbo''

    AND su.name <> ''sys'')

    AND NOT EXISTS

    (

    SELECT 1

    FROM master..syslogins sl

    WHERE su.sid = sl.sid

    )'

    print @Qry

    INSERT INTO #Results EXEC (@Qry)

    END

    SELECT *

    FROM #Results

    ORDER BY [Database Name], [Orphaned User]

    DROP TABLE #Results

  • Rudy Panigas

    SSChampion

    Points: 10695

    Hmm, I seem to be getting errors when executing the code.

    Msg 207, Level 16, State 1, Line 2

    Invalid column name 'object_id'.

    Msg 102, Level 15, State 1, Procedure sp_common_server_problems, Line 31

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Procedure sp_common_server_problems, Line 130

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Procedure sp_common_server_problems, Line 132

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Procedure sp_common_server_problems, Line 142

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Procedure sp_common_server_problems, Line 144

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Procedure sp_common_server_problems, Line 156

    Incorrect syntax near '?'.

    Msg 156, Level 15, State 1, Procedure sp_common_server_problems, Line 161

    Incorrect syntax near the keyword 'select'.

    Msg 2812, Level 16, State 62, Line 1

    Could not find stored procedure 'sp_common_server_problems'.

    Anyone have the same problem or know of how to fix this?

    Rudy

  • Rudy Panigas

    SSChampion

    Points: 10695

    I'm having the same errors. Does anyone have the updated script? With Orphan reporting too?

    Rudy

  • paul_richardson

    SSCarpal Tunnel

    Points: 4594

    hi,

    I'll post an updated version of this script at the weekend.

    Paul

  • boris_shvartsman

    Mr or Mrs. 500

    Points: 552

    This procedure doesn't work. It has a syntax error..

    if one wants it.. one will justify it.

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the script.

  • Lowell

    SSC Guru

    Points: 323460

    Iwas Bornready (5/10/2016)


    Thanks for the script.

    i looked through your post history, and you seem to be adding this comment to lots of old scripts; this one was nine years old;

    contribute more to some discussions, instead of bumping your post count unnecessarily.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply