October 26, 2010 at 11:15 am
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
October 26, 2010 at 12:40 pm
Have you tried to put DISTINCT as part of your initial SELECT?
That way you solve the part of when you comment out the line for
--u.uid = user_id() AND --<<<< comment this and it runs but repeats some names >>>
then, it doesn't repeat some names?
October 26, 2010 at 1:06 pm
Thank you for the reply!
yes, I added in distinct and saw that it would remove the repeating values. I'm just concerned why the value repeat in the first place. Perhaps I may encounter another issue with a more complex users permissions. Such as one who is within mulitiple roles for example.
October 26, 2010 at 2:16 pm
I many have come across a script that does the trick. So far so go. Will have to insert into the application and test.
select
sys.objects.name name,
sys.objects.object_id,
sys.objects.create_date created,
table_description = 'No description available for ' + sys.objects.name
from sys.database_permissions join
sys.objects on sys.database_permissions.major_id = sys.objects.object_id
join sys.schemas on sys.objects.schema_id = sys.schemas.schema_id
join sys.database_principals on
sys.database_permissions.grantee_principal_id = sys.database_principals.principal_id
where sys.objects.type IN ('U','V')
order by 1
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply