Query Result Modification !

  • U J

    SSCrazy

    Points: 2420

    The Query,

    SELECT OBJECT_NAME(id) as ObjectName,

     USER_NAME(uid) as UserName, protecttype,

     action, USER_NAME(grantor) as Grantor

    FROM sysprotects

    WHERE id = object_id('syslogins')

    The above query will give me output like this=>

    ObjectName UserName ProtectType Action Granter
    syslogins public 205 193 dbo
    syslogins public 206 193 dbo
    syslogins perfstat 205 193 dbo

    but i want some replacement as

    GRANT instead of 205
    REVOKE instead of 206
    SELECT instead of 193

    i.e output will look like as =>

    ObjectName UserName ProtectType Action Granter
    syslogins public

    GRANT

    SELECT

    dbo
    syslogins public

    REVOKE

    SELECT

    dbo
    syslogins perfstat

    GRANT

    SELECT

    dbo

     

    Is it possible..?

    I will be Heartly Thankful for Any Help....

  • Jo Pattyn

    SSC-Dedicated

    Points: 31393

    Hello,

    You could build a tiny table with the translations to join with. Since the values are hardcoded in INFORMATION_SCHEMA.TABLE_PRIVILEGES it isn't likely that the translationtable is already there.

    If you lookup sysprotects in the bookonline it gives you (most of) the possible values

  • Sam Greene

    SSCertifiable

    Points: 5252

    Or use a case statement if there are not too many values.

     

Viewing 3 posts - 1 through 3 (of 3 total)

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