SQL 2000 script does not work in SQL 2005

  • Jersey Moe

    SSChasing Mays

    Points: 652

    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

  • SQLSalas

    Ten Centuries

    Points: 1325

    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?

  • Jersey Moe

    SSChasing Mays

    Points: 652

    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.

  • Jersey Moe

    SSChasing Mays

    Points: 652

    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 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply