I'm upgrading to 2005 and have come across an issue with how a script operates. The purpose of this script is to determine which tables and or vies a user id can access. When it runs under 2005 it only returns tables or views granted to public. It should return all the objects granted to the role in which the user id resides.
If I run as dbo or sa, it seems to be fine. also, if I comment out the line of u.uid = user_id AND...it works, well it still lists a few tables several times which I don't understand why.
Understanding that of course the architecture of the environments has changed. I'm asking if anyone else has come across an issue such as this and if a solutions was found.
Here is the script:
SELECT o.name, o.id, o.crdate created,table_description = CONVERT (VARCHAR (150),
'No description available for ' + o.name )
FROM sysusers u, sysobjects o
WHERE o.name LIKE '%' and
user_name(o.uid) LIKE '%' and
charindex(substring(o.type,1,1),'UV') != 0 AND
u.uid = user_id() AND <<<< comment this and it runs but repeats some names >>>
( suser_id() = 1 OR
o.uid = user_id() OR
( (SELECT MAX (((SIGN (uid) * ABS (uid-16383)) * 2) + (~(protecttype/2)) )
FROM sysprotects p
WHERE p.id = o.id AND
( p.uid = 0 OR
p.uid = user_id() OR
p.uid = u.gid )
And (action = 193))&1) = 1)
ORDER BY o.name