Author |
Message |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7966
|
|
|
|
 |
 |
Can you correct this section of the query: |
Replace schema_id <16384> 0 with schema_id between 1 and 16383
|
|
Mon Oct 08, 2007 10:13 am |
|
 |
judahr
Joined: 09 Mar 2007 Posts: 319 Country: United States |
|
|
|
Doesn't work:
 |
 |
select
name collate Latin1_General_CI_AS,
case when type = 'R' then 'SR' else 'SL' end,
principal_id
from
/*db.*/sys.server_principals sp
where
type in ('S','U','G','R')
and not exists (
select null from /*db.*/sys.schemas where schema_id between 1 and 16383 then 'SR' else 'SC' end,
cast(uid as int)
from
/*db.*/dbo.sysusers
where
issqluser = 1 or isntname = 1
or issqlrole = 1 or isapprole = 1 |
It looks like the forum software took the > from one query messed up the < in another, or vice-versa. I need both select queries, the one from server_principals and the one from sysusers.
|
|
Mon Oct 08, 2007 11:16 am |
|
 |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7966
|
|
|
|
Here is the complete code, I simplified it even further, to return only schemas with objects.
 |
 |
select distinct name, 'SC', uid
from /*db.*/dbo.sysobjects
where uid between 1 and 16383 |
|
|
Mon Oct 08, 2007 11:28 am |
|
 |
judahr
Joined: 09 Mar 2007 Posts: 319 Country: United States |
|
|
|
Using that query for schemas caused this error:
---------------------------
SQL Assistant - Error
---------------------------
Message: Specified cast is not valid.
Context: Fetch
select distinct name, 'SC', uid
from [Database].dbo.sysobjects
where uid between 1 and 16383
---------------------------
OK
---------------------------
error ocurred on:
Select
|
|
Mon Oct 08, 2007 1:08 pm |
|
 |
judahr
Joined: 09 Mar 2007 Posts: 319 Country: United States |
|
|
|
Error only occurs in Management Studio, not in Query Analyzer. BTW, this is the correct schema query:
 |
 |
select distinct name, 'SC', uid
from /*db.*/dbo.sysusers
where uid between 1 and 16383 |
|
|
Mon Oct 08, 2007 1:45 pm |
|
 |
SysTema
Joined: 01 Dec 2006 Posts: 3
|
|
|
Mon Oct 08, 2007 11:42 pm |
|
 |
judahr
Joined: 09 Mar 2007 Posts: 319 Country: United States |
|
Fixed! |
|
Perfect! It works awesome now. Thanks so much.
|
|
Tue Oct 09, 2007 8:05 am |
|
 |
|