SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
No assistance provided for one server
Goto page Previous  1, 2, 3
 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
No assistance provided for one server
Author Message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
Quote:
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 View user's profile Send private message
judahr



Joined: 09 Mar 2007
Posts: 319
Country: United States

Post Reply with quote
Doesn't work:

Code:
 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 View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
Here is the complete code, I simplified it even further, to return only schemas with objects.

Code:
select distinct name, 'SC', uid
from /*db.*/dbo.sysobjects
where uid between 1 and 16383

Mon Oct 08, 2007 11:28 am View user's profile Send private message
judahr



Joined: 09 Mar 2007
Posts: 319
Country: United States

Post Reply with quote
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 View user's profile Send private message
judahr



Joined: 09 Mar 2007
Posts: 319
Country: United States

Post Reply with quote
Error only occurs in Management Studio, not in Query Analyzer. BTW, this is the correct schema query:

Code:
select distinct name, 'SC', uid
from /*db.*/dbo.sysusers
where uid between 1 and 16383

Mon Oct 08, 2007 1:45 pm View user's profile Send private message
SysTema



Joined: 01 Dec 2006
Posts: 3

Post Reply with quote
Here is the corrected query
http://www.softtreetech.com/support/phpBB2/viewtopic.php?p=19349#19349

The idea was to remove 'sysdba','SL' from the result set.
Mon Oct 08, 2007 11:42 pm View user's profile Send private message
judahr



Joined: 09 Mar 2007
Posts: 319
Country: United States

Post Fixed! Reply with quote
Perfect! It works awesome now. Thanks so much.
Tue Oct 09, 2007 8:05 am View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Goto page Previous  1, 2, 3
Page 3 of 3

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.