/* Microsoft SQL Server - Scripting			*/
/* Server: SQLSERVER					*/
/* Database: TADS					*/
/* Creation Date 8/29/00 10:11:42 AM 			*/

/****** Object:  View dbo.vsysShowPermissions    Script Date: 8/29/00 10:11:42 AM ******/
if exists (select * from sysobjects where id = object_id('dbo.vsysShowPermissions') and sysstat & 0xf = 2)
	drop view dbo.vsysShowPermissions
GO

/****** Object:  View dbo.vsysShowPermissions    Script Date: 8/29/00 10:11:42 AM ******/
Create view vsysShowPermissions
as
select ObjName = convert(char(30), o.name),
   ObjType = 
      CASE o.type
         when "C"  then "Constraint"
         when "D"  then "Default"
         when "F"  then "Foreign Key"
         when "K"  then "Primary Key"
         when "L"  then "Log"
         when "P"  then "Procedure"
         when "R"  then "Rule"
         when "RF" then "Replication SP"
         when "S"  then "SYSTEM TABLE"
         when "TR" then "Trigger"
         when "U"  then "Table"
         when "V"  then "View"
         when "X"  then "Extended SP"
         ELSE "*BAD OBJECT TYPE"
      END,
   UserName = convert(char(20),
      case 
         when p.id is null then " (no users)"
         when u.uid = u.gid then lower(u.name)
         else upper(u.name)
      end),
   Permission = convert(char(20),
      (CASE p.action
         when 26  then "References"
         when 193 then "Select"
         when 195 then "Insert"
         when 196 then "Delete"
         when 197 then "Update"
         when 198 then "Create Table"
         when 203 then "Create Database"
         when 207 then "Create View"
         when 222 then "Create Procedure"
         when 224 then "Execute"
         when 228 then "Dump Database"
         when 233 then "Create Default"
         when 235 then "Dump Transaction"
         when 236 then "Create Rule"
         ELSE " (no permissions)"
      END
   +
      CASE p.protecttype
         when 205 then "/Grant"
         when 206 then "/REVOKE"
         ELSE ""
      END))
--   , p.id, p.uid, p.protecttype, p.action
from sysobjects o
   left join sysprotects p on o.id = p.id
   left join sysusers u on p.uid = u.uid

GO