Query Result Modification !

• U J

  SSCrazy

  Points: 2423

  More actions

  February 5, 2007 at 11:30 pm

  #172120

  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: 32433

  More actions

  February 6, 2007 at 3:00 am

  #687955

  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

  More actions

  February 6, 2007 at 9:32 am

  #688086

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