Cleaning up server logins

I have a host of server logins, NT and SQL access level on my server. I'd
like to iterate through the databases and find users who have no logins to
non-system databases. Using master.dbo.sysdatabases, I can retrieve a list
of all databases on the server, but not the type (System or User), unless a
SID of 0x01 is an appropriate measure of a system database. After obtaining
a list of databases, how can I iterate through them with T-SQL code?Using cursors.
Example:
use northwind
go
declare @.db sysname
declare databases_cursor cursor
local
static
read_only
for
select
[name]
from
master..sysdatabases
where
dbid > 6
order by
[name]
open databases_cursor
while 1 = 1
begin
fetch next from databases_cursor into @.db
if @.@.error <> 0 or @.@.fetch_status <> 0 break
print @.db
end
close databases_cursor
deallocate databases_cursor
go
"Kevin Bowker" wrote:

> I have a host of server logins, NT and SQL access level on my server. I'd
> like to iterate through the databases and find users who have no logins to
> non-system databases. Using master.dbo.sysdatabases, I can retrieve a lis
t
> of all databases on the server, but not the type (System or User), unless
a
> SID of 0x01 is an appropriate measure of a system database. After obtaini
ng
> a list of databases, how can I iterate through them with T-SQL code?

Choosing RAID level

Hi,
In your opinion what would be a better choice for running Microsoft SQL
Server 2000 on Microsoft Windows 2000 Advanced Server:
1) 4-disk RAID10 for OS and tempdb,
2) 2x2-(same)disk RAID1 - one for OS, one for tempdb
Why?
The server will be dedicated only for running the SQL Server so paging
shouldn't be an issue.
Many thanks,
Oskar
You might want to start here:
http://b.wunder.home.comcast.net/18960.htm
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:51D6E5C7-88C3-45B4-96A9-14C3CCDCCD2C@.microsoft.com...
> Hi,
> In your opinion what would be a better choice for running Microsoft SQL
> Server 2000 on Microsoft Windows 2000 Advanced Server:
> 1) 4-disk RAID10 for OS and tempdb,
> 2) 2x2-(same)disk RAID1 - one for OS, one for tempdb
> Why?
> The server will be dedicated only for running the SQL Server so paging
> shouldn't be an issue.
> --
> Many thanks,
> Oskar
>

Choosing RAID level

Hi,
In your opinion what would be a better choice for running Microsoft SQL
Server 2000 on Microsoft Windows 2000 Advanced Server:
1) 4-disk RAID10 for OS and tempdb,
2) 2x2-(same)disk RAID1 - one for OS, one for tempdb
Why?
The server will be dedicated only for running the SQL Server so paging
shouldn't be an issue.
Many thanks,
OskarYou might want to start here:
http://b.wunder.home.comcast.net/18960.htm
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:51D6E5C7-88C3-45B4-96A9-14C3CCDCCD2C@.microsoft.com...
> Hi,
> In your opinion what would be a better choice for running Microsoft SQL
> Server 2000 on Microsoft Windows 2000 Advanced Server:
> 1) 4-disk RAID10 for OS and tempdb,
> 2) 2x2-(same)disk RAID1 - one for OS, one for tempdb
> Why?
> The server will be dedicated only for running the SQL Server so paging
> shouldn't be an issue.
> --
> Many thanks,
> Oskar
>

Choosing RAID level

Hi,
In your opinion what would be a better choice for running Microsoft SQL
Server 2000 on Microsoft Windows 2000 Advanced Server:
1) 4-disk RAID10 for OS and tempdb,
2) 2x2-(same)disk RAID1 - one for OS, one for tempdb
Why?
The server will be dedicated only for running the SQL Server so paging
shouldn't be an issue.
--
Many thanks,
OskarYou might want to start here:
http://b.wunder.home.comcast.net/18960.htm
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:51D6E5C7-88C3-45B4-96A9-14C3CCDCCD2C@.microsoft.com...
> Hi,
> In your opinion what would be a better choice for running Microsoft SQL
> Server 2000 on Microsoft Windows 2000 Advanced Server:
> 1) 4-disk RAID10 for OS and tempdb,
> 2) 2x2-(same)disk RAID1 - one for OS, one for tempdb
> Why?
> The server will be dedicated only for running the SQL Server so paging
> shouldn't be an issue.
> --
> Many thanks,
> Oskar
>

Checking columns level dependencies

Hi all,
Need a big favour, I have to collect information like
TableName-> it's all columnNames--> dependent table name of EachColumn(where this column is refrenced)

I have managed to get some info like:-

select so.name as 'Table Name',sc.name as 'Column Name',st.[name], sc.length,sc.isnullable,so.crdate
from syscolumns sc
inner join sysobjects so on so.id=sc.id
inner join systypes st on st.xtype= sc.xtype
where so.xtype='U'
order by so.name

sp_depends and sp_MSdependencies is not returning me same fields..I would start with sysobjects, syscolumns and sysreferences. Try the query and if you get stuck, let us know

CHECKDB found 0 allocation errors and 2 consistency errors in data

Hi,
SQL 2000, SP4
Getting CHECKDB found 0 allocation errors and 2 consistency errors in
database after running dbcc checkdb:
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'abc', index 'SyncServerState.PK_SyncServerState' (ID
327672215) (index ID 2). Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:677:2) with values (Id = 3 and ServerConfigId = 0 and Type = 'DevMgmtServerConfigHistoryId') points to the data row identified by ().
Also seeing in SQL error log:
Could not find the index entry for RID
'3600000000020000010044004400650076004d0067006d00740053006500720076006500720043006f006e00660069006700' in index page (1:152369),
help would be apperciated
thxTry rebuilding those indexes and you should be fine as they are
nonclustered.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"stoney" <stoney@.discussions.microsoft.com> wrote in message
news:388A8392-1139-4315-9911-38607DB51AD6@.microsoft.com...
> Hi,
> SQL 2000, SP4
> Getting CHECKDB found 0 allocation errors and 2 consistency errors in
> database after running dbcc checkdb:
> Server: Msg 8952, Level 16, State 1, Line 1
> Table error: Database 'abc', index 'SyncServerState.PK_SyncServerState'
> (ID
> 327672215) (index ID 2). Extra or invalid key for the keys:
> Server: Msg 8956, Level 16, State 1, Line 1
> Index row (1:677:2) with values (Id = 3 and ServerConfigId = 0 and Type => 'DevMgmtServerConfigHistoryId') points to the data row identified by ().
> Also seeing in SQL error log:
> Could not find the index entry for RID
> '3600000000020000010044004400650076004d0067006d00740053006500720076006500720043006f006e00660069006700'
> in index page (1:152369),
> help would be apperciated
> thx|||Hi,
I am looking at manage index window and it says that it is Clustered,
"stoney" wrote:
> Hi,
> SQL 2000, SP4
> Getting CHECKDB found 0 allocation errors and 2 consistency errors in
> database after running dbcc checkdb:
> Server: Msg 8952, Level 16, State 1, Line 1
> Table error: Database 'abc', index 'SyncServerState.PK_SyncServerState' (ID
> 327672215) (index ID 2). Extra or invalid key for the keys:
> Server: Msg 8956, Level 16, State 1, Line 1
> Index row (1:677:2) with values (Id = 3 and ServerConfigId = 0 and Type => 'DevMgmtServerConfigHistoryId') points to the data row identified by ().
> Also seeing in SQL error log:
> Could not find the index entry for RID
> '3600000000020000010044004400650076004d0067006d00740053006500720076006500720043006f006e00660069006700' in index page (1:152369),
> help would be apperciated
> thx|||This definately says the index ID is 2. The index ID has to be 1 for it to
be a clustered index.
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'abc', index 'SyncServerState.PK_SyncServerState' (ID
327672215) (index ID 2). Extra or invalid key for the keys:
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"stoney" <stoney@.discussions.microsoft.com> wrote in message
news:B4EE6885-40EE-4842-9188-F02B61CCEE38@.microsoft.com...
> Hi,
> I am looking at manage index window and it says that it is Clustered,
> "stoney" wrote:
>> Hi,
>> SQL 2000, SP4
>> Getting CHECKDB found 0 allocation errors and 2 consistency errors in
>> database after running dbcc checkdb:
>> Server: Msg 8952, Level 16, State 1, Line 1
>> Table error: Database 'abc', index 'SyncServerState.PK_SyncServerState'
>> (ID
>> 327672215) (index ID 2). Extra or invalid key for the keys:
>> Server: Msg 8956, Level 16, State 1, Line 1
>> Index row (1:677:2) with values (Id = 3 and ServerConfigId = 0 and Type =>> 'DevMgmtServerConfigHistoryId') points to the data row identified by ().
>> Also seeing in SQL error log:
>> Could not find the index entry for RID
>> '3600000000020000010044004400650076004d0067006d00740053006500720076006500720043006f006e00660069006700'
>> in index page (1:152369),
>> help would be apperciated
>> thx|||thx Andrew, I have rebuilt the index and all seems to be working fine.
Allthough I see in the errorlog 'Skipping startup of clean database id 5' and
that is the id of the db that I rebuilt the inex on.
Any thoughts
"Andrew J. Kelly" wrote:
> Try rebuilding those indexes and you should be fine as they are
> nonclustered.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "stoney" <stoney@.discussions.microsoft.com> wrote in message
> news:388A8392-1139-4315-9911-38607DB51AD6@.microsoft.com...
> > Hi,
> >
> > SQL 2000, SP4
> > Getting CHECKDB found 0 allocation errors and 2 consistency errors in
> > database after running dbcc checkdb:
> >
> > Server: Msg 8952, Level 16, State 1, Line 1
> > Table error: Database 'abc', index 'SyncServerState.PK_SyncServerState'
> > (ID
> > 327672215) (index ID 2). Extra or invalid key for the keys:
> >
> > Server: Msg 8956, Level 16, State 1, Line 1
> > Index row (1:677:2) with values (Id = 3 and ServerConfigId = 0 and Type => > 'DevMgmtServerConfigHistoryId') points to the data row identified by ().
> >
> > Also seeing in SQL error log:
> >
> > Could not find the index entry for RID
> > '3600000000020000010044004400650076004d0067006d00740053006500720076006500720043006f006e00660069006700'
> > in index page (1:152369),
> >
> > help would be apperciated
> >
> > thx
>

Check the syntax

I'm getting the following error messages:
Incorrect syntax near the keyword 'in'.
Server: Msg 156, Level 15, State 1, Line 38
Incorrect syntax near the keyword 'group'.

/* create temp tables */
select distinct d_vst_id as 'DRW_ID'
,d_vst_instid as 'DRW_INSTID'
into temp_tb1
from dnr_vst_db_rec
where d_vst_instid = ''
and d_vst_dontyp = 'WB'
and d_vst_status = 'DN'
and d_vst_date between 20020301 and 20030228
order by d_vst_id

Select distinct
n_per_id as 'ID1'
,n_per_gender as 'GENDER'
,n_per_birth as 'BIRTH1'
,d_bty_abo + d_bty_rhesus as 'ABO1'
,n_adr_city as 'CITY1'
,n_adr_zip as 'ZIP1'
into temp_tb3
from temp_tb1 right outer join nat_per_db_rec
on DRW_ID = n_per_id
right outer join dnr_bty_db_rec
on DRW_ID = d_bty_id
right outer join nat_adr_db_rec
on DRW_ID = n_adr_id
where DRW_INSTID = ''
order by n_per_id

select distinct getdate()
,d_aaa_insthdg
, case (d_vst_btcdte - n_per_birth) / 10000
when in (14,15,16) the '14-16'
when in (17,18,19,20) then '17-20'
when in (21, 22,23,24,25) then '21-25'
when in (26,27,28,29,30) then '26-30'
when in (31,32,33,34,35) then '31-35'
when in (36,37,38,39,40) then '36-40'
when in (41,42,43,44,45) then '41-45'
when in (46,47,48,49,50) then '46-50'
when in (51,52,53,54,55) then '51-55'
when in (56,57,58,59,60) then '56-60'
when in (61,62,63,64,65) then '61-65'
when in (66,67,68,69,70) then '66-70'*/
else
71+
end as 'AGE'
,sum(case a.d_vst_dontyp when '1' then 1 else 0 end ) as 'DRAW1'
,sum(case a.d_vst_dontyp when 'xx' then 1 else 1 end ) as 'TOTAL'
from dnr_aaa_db_rec, dnr_dud_db_rec, temp_tb3, dnr_vst_db_rec a
where a.d_vst_instid = ''
and a.d_vst_instid = d_aaa_instid
and a.d_vst_id = ID1
and a.d_vst_instid = n_per_instid
and a.d_vst_id = n_per_id
and n_per_gender = 'M'
and a.d_vst_btcdte between 20020301 and 20030228
and a.d_vst_btcdte = (Select max(b.d_vst_btcdte)
from dnr_vst_db_rec b
where b.d_vst_instid = ''
and b.d_vst_status = 'DN'
and b.d_vst_dontyp = 'WB'
and b.d_vst_id = a.d_vst_id
and b.d_vst_btcdte between 20020301 and 20030228)

group by
d_aaa_insthdg
,case (d_vst_btcdte - n_per_birth) / 10000
when in (14,15,16) the '14-16'
when in (17,18,19,20) then '17-20'
when in (21, 22,23,24,25) then '21-25'
when in (26,27,28,29,30) then '26-30'
when in (31,32,33,34,35) then '31-35'
when in (36,37,38,39,40) then '36-40'
when in (41,42,43,44,45) then '41-45'
when in (46,47,48,49,50) then '46-50'
when in (51,52,53,54,55) then '51-55'
when in (56,57,58,59,60) then '56-60'
when in (61,62,63,64,65) then '61-65'
when in (66,67,68,69,70) then '66-70'*/
else
71+
end asI think you have 2 problems. First, move (d_vst_btcdte - n_per_birth) / 10000 to be between "when" and "in". Second, in both case statements you have 71+ followed by "*/". You need to surround 71+ with apostrophes and remove */.