Correleated T-SQL Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Correleated T-SQL Problem

Author

Topic

sonnysingh
Starting Member

42 Posts

Posted - 2005-05-20 : 15:02:38

Hi All

Is any body can help me out to solve this fllowing T-SQL please...

T-SQL:

SELECT a.name as UserName, b.name as Rolename FROM sysusers a,
(SELECT name FROM sysusers b
WHERE (b.name NOT LIKE 'db%')
AND (b.name NOT IN ('guest', 'public'))
AND (b.uid = gid)) b

WHERE (a.name NOT LIKE 'db%')
AND (a.name NOT IN ('guest', 'public'))
AND (a.islogin = 1)

Results:

Users Roles
Ians Admin
williams Admin
Ians ExecuteObjects
williams ExecuteObjects

but i want single entry like this:
Users Roles
sonny Admin
williams ExecuteObjects

Thanx in advance

SqlIndia

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-05-20 : 15:24:52

Please format the code ... I found it convoluted and hard to read,
Why the derived table ?

SELECT
	a.name as UserName
	,b.name as Rolename
FROM
	sysusers a	-- users
	JOIN sysusers b	-- groups
		ON a.gid = b.uid --<------------- missing this ?
WHERE
	(a.name NOT LIKE 'db%')
	AND
	(a.name NOT IN ('guest', 'public'))
	AND
	(a.islogin = 1)	-- only users
	AND
	(b.uid = gid)	-- only groups
	AND
	(b.name NOT LIKE 'db%')
	AND
	(b.name NOT IN ('guest', 'public'))

rockmoose

sonnysingh
Starting Member

42 Posts

Posted - 2005-05-20 : 18:09:50

Thanx rockmoose for response

But somehow this query didn't work.. It's come with no result..inspite of I have two user and two roles created in my database.

You see I want to display users and roles by using one query....

Thanx in advance

regards

SqlIndia

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-05-20 : 18:33:41

Ah!
SELECT 'User' AS Type, a.name as Name FROM sysusers a WHERE (a.islogin = 1) -- only users
UNION ALL
SELECT 'Group' AS Type, b.name as Name FROM sysusers b WHERE (b.uid = b.gid) -- only users

But I think that You have to rethink about the formatting of your output.
The way You showed it is not very practical.

rockmoose

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-05-20 : 18:43:10

I will show You how You can do it though...
You will have to put in all the appropriate filtering yourself.

And nope, I don't think there is an easier way

SELECT
	UserName
	,GroupName
FROM
	( SELECT a.name AS UserName,(SELECT COUNT(*) FROM sysusers aa WHERE (aa.islogin = 1) AND aa.name > a.name) AS rank
	FROM sysusers a WHERE (a.islogin = 1) ) AS users
	FULL JOIN
	( SELECT b.name AS GroupName,(SELECT COUNT(*) FROM sysusers bb WHERE (bb.uid = bb.gid) AND bb.name > b.name) AS rank
	FROM sysusers b WHERE (b.uid = b.gid) ) AS groups
	ON users.rank = groups.rank
ORDER BY
	COALESCE(users.rank, groups.rank)

rockmoose

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-05-20 : 20:48:55

Don't overlook the system functions.

exec sp_helprolemember

If you want to perform filtering and/or Ranking you could do something like this:


create table #groupRank (DbRole sysname, MemberName sysName, MemberSID varbinary(85))
go
insert #groupRank exec sp_helprolemember
select MemberName [User], dbRole [Role] from #groupRank where dbrole not like 'db%'

Be One with the Optimizer
TG

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-05-21 : 04:12:32

I must have been very tired

"I ws in the do this in 1 select mood"
Good TG.

rockmoose

sonnysingh
Starting Member

42 Posts

Posted - 2005-05-22 : 05:52:20

Thanx guys.. I have tried the solutions you have been described..

rockmoose..your query worked perfect ( agreed.. wasn't that much clear my detail..)

TG.. thanx for idea.. I have tried this but not exactly using sp_helprolemember rather a query where I joined sysusers and sysmembers tables on uid=memberuid and gid=groupid in order to get list of memebers with roles..

This is the scenario, where I am upgrading target db with souce db and before do upgrade I need to find out
1. list of users who are in target db compare to source db
2. List of Roles (" ")
3. Users who are assigned to specfic roles (" ")

so.. I can able to upgrade target db accordingly.. sp_helprolemember only help in third option....Top of that I was try to do this in one query and check how could i do it or not.. and ofcourse with help of you guys we can do it..

Question:...
MY another curiosity is can we insert result of sp_helpuser and sp_rolehelp by using on T-SQL into one table?? if not then how it is possible..

Thanx for your help guys and really appreciated it.

Regards

SqlIndia

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-05-23 : 00:58:31

quote:

Question:...
MY another curiosity is can we insert result of sp_helpuser and sp_rolehelp by using on T-SQL into one table?? if not then how it is possible..

Thanx for your help guys and really appreciated it.

Create a table having the same structure of the result set and insert records using exec


create table #temp(Username varchar(50),GroupName varchar(50),LoginName varchar(50),
DefDBName varchar(50),Userid int,SID varchar(50))
insert into #temp exec sp_helpuser 
select * from #temp

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources