Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Check owners, collations and backups Expand / Collapse
Author
Message
Posted Tuesday, October 23, 2007 10:33 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, November 22, 2013 8:54 AM
Points: 1,108, Visits: 204
Comments posted to this topic are about the item Check owners, collations and backups
Post #414178
Posted Tuesday, November 13, 2007 4:20 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, May 18, 2014 6:11 PM
Points: 110, Visits: 472
I can't see the script. is this only me?
Post #421832
Posted Wednesday, November 14, 2007 11:37 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, November 22, 2013 8:54 AM
Points: 1,108, Visits: 204
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
Post #422251
Posted Wednesday, November 14, 2007 12:56 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, May 18, 2014 6:11 PM
Points: 110, Visits: 472
Thanks Paul. :)
Post #422275
Posted Monday, November 26, 2007 10:55 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 18, 2008 9:01 PM
Points: 2, Visits: 13
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

Post #426174
Posted Monday, November 26, 2007 11:38 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 18, 2008 9:01 PM
Points: 2, Visits: 13
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
Post #426192
Posted Wednesday, January 2, 2008 7:14 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, June 30, 2014 1:13 PM
Points: 311, Visits: 1,096
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?




Post #437966
Posted Tuesday, May 27, 2008 2:25 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, June 30, 2014 1:13 PM
Points: 311, Visits: 1,096
I'm having the same errors. Does anyone have the updated script? With Orphan reporting too?


Post #507253
Posted Tuesday, May 27, 2008 4:08 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, November 22, 2013 8:54 AM
Points: 1,108, Visits: 204
hi,
I'll post an updated version of this script at the weekend.

Paul
Post #507308
Posted Friday, June 24, 2011 1:07 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, May 24, 2013 2:15 PM
Points: 36, Visits: 200
This procedure doesn't work. It has a syntax error..

if one wants it.. one will justify it.
Post #1131405
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse