inner joins for system tables - 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)

inner joins for system tables

Author

Topic

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-06 : 04:45:43

hope someone helps me...

-----------
declare @user nvarchar(100)
set @user='username'

SELECT sysobjects.name, syspermissions.id,sysusers.uid
FROM sysobjects
INNER JOIN syspermissions ON sysobjects.id = syspermissions.id
INNER JOIN sysusers ON sysobjects.uid = sysusers.uid
INNER JOIN sysmembers ON sysusers.uid = sysmembers.memberuid
WHERE (sysobjects.xtype in ('U','P','FN')) and (sysobjects.name not like 'dt_%') and (sysusers.name = @user)
group by sysobjects.name,syspermissions.id,sysusers.uid
-----------

What i expect to retrieve:
user id with corresponding permission id and object name
Criteria:
only user defined objects such as tables, sproc and udf
Input Parameter:
loginame or username

The above snippet doesn't return anything (if i remove the @user, always dbo) even if the user exists and with corresponding permissions.

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2004-09-06 : 05:26:18

Hi
This should get what you want:

DECLARE @User NVARCHAR(100)
SET @User = N'username'

SELECT
usr.uid AS UserID,
usr.name AS UserName,
obj.id AS ObjectID,
obj.name AS ObjectName

FROM
syspermissions AS per
JOIN sysusers AS usr
ON per.grantee = usr.uid
JOIN sysobjects AS obj
ON obj.id = per.id
WHERE
obj.xtype IN ('U', 'P', 'FN')
AND obj.name NOT LIKE 'dt_%'
AND usr.name LIKE @User

The uid column in sysobjects refers to the owner of the object. This is why you're losing rows.
There doesn't seem to be any point in including sysmembers unless you are going to constrain on it or return anything from it in your resultset.

Mark

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-06 : 06:02:20

That's great Mark! It works perfectly. I thought the uid in sysobjects refers to the user which has permission to that object following the naming conventions for other system tables.

many thanks,
Jen

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2004-09-07 : 05:13:54

No problem jen

Mark

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-08 : 00:35:15

Now I remember...

I'm supposed to get the permissions for each user for n objects.

the join above is ok but it doesn't tell me what specific permission does the user have eg. table1 - is it select, insert, update, delete?

that's why i also added the sysmembers coz i'm checking if the user is member of a group such as datareader

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-09-08 : 01:11:17

Just a tip - you'd be better off using the INFORMATION_SCHEMA views instead of the sys tables if you can. There's no guarantee that sysXXXX tables will still be the same after service packing/updated versions of SQL Server.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-09 : 07:07:35

i agree and it's much easier coz the columns are there but they won't do, i need to know if the user has privilege on a view or routine. only tables are returned with specific privileges for each user.

if you could direct me to one, i'd appreciate it.

currently, i'm using the system tables coz i can see no other way.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources