Check owners, collations and backups

Question

Post reply

Check owners, collations and backups

paul_richardson

SSCarpal Tunnel

Points: 4594
More actions
October 23, 2007 at 10:33 pm

#376007

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

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

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

dr.kusnadi Say Hey Kid Points: 696 More actions · Answer 1

dr.kusnadi

Say Hey Kid

Points: 696

November 13, 2007 at 4:20 pm

#750878

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

paul_richardson SSCarpal Tunnel Points: 4594 More actions · Answer 2

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 More actions · Answer 3

dr.kusnadi

Say Hey Kid

Points: 696

November 14, 2007 at 12:56 pm

#751227

Thanks Paul. 🙂

Gennadi Gretchkosiy-440661 SSC Rookie Points: 38 More actions · Answer 4

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 More actions · Answer 5

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: 10702 More actions · Answer 6

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: 10702 More actions · Answer 7

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

Rudy

paul_richardson SSCarpal Tunnel Points: 4594 More actions · Answer 8

hi,

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

Paul

boris_shvartsman Mr or Mrs. 500 Points: 582 More actions · Answer 9

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

if one wants it.. one will justify it.

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 10

akljfhnlaflkj

SSC Guru

Points: 76202

May 10, 2016 at 1:53 pm

#1877963

Thanks for the script.

Lowell SSC Guru Points: 323518 More actions · Answer 11

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!