x

SQL Server query help

Hi,

Can some please explain the following query? particularly type = 'P'? I do not see much help available on spt_values. Thanks for the help

select    
    convert(sysname,user_name(o.uid)) TABLE_OWNER,
    convert(sysname,object_name(o.id)) TABLE_NAME,
    convert(sysname,user_name(u.uid)) GRANTOR,
    convert(sysname,user_name(o.uid)) GRANTEE,
    convert(varchar(32),case v.number
        when 193 then 'SELECT'
        when 195 then 'INSERT'
        when 196 then 'DELETE'
        when 197 then 'UPDATE'
        else 'REFERENCES'
    end) PRIVILEGE,
    convert(varchar(3),'YES') IS_GRANTABLE
from dbname.dbo.sysobjects o, master.dbo.spt_values v, dbname.dbo.sysusers u
where
     o.type in ('U','V','S')
    and u.uid = 1        
    and v.type = 'P'    
    and v.number in (26,193,195,196,197)
    and not exists (    
        select *
        from fis_ctrl.dbo.sysprotects p1
        where
            p1.protecttype = 206
            and p1.action = v.number
            and p1.id = o.id
            and p1.uid = o.uid)

more ▼

asked Oct 29, 2009 at 11:35 AM in Default

avatar image

Govin
22 2 2 4

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

What you have there is a rough equivalent of the source of the view [INFORMATION_SCHEMA].[TABLE_PRIVILEGES]. The MSDN reference is here, but that's about as useful as a chocolate teapot.

For reference, this is the source of [INFORMATION_SCHEMA].[TABLE_PRIVILEGES] on a 2000 server:

--Identifies privileges granted to or by the current user create view [INFORMATION_SCHEMA].[TABLE_PRIVILEGES] as select user_name(p.grantor) as GRANTOR ,user_name(p.uid) as GRANTEE ,db_name() as TABLE_CATALOG ,user_name(o.uid) as TABLE_SCHEMA ,o.name as TABLE_NAME ,case p.action when 26 then 'REFERENCES' when 193 then 'SELECT' when 195 then 'INSERT' when 196 then 'DELETE' when 197 then 'UPDATE' end as PRIVILEGE_TYPE ,case when p.protecttype = 205 then 'NO' else 'YES' end as IS_GRANTABLE from sysprotects p, sysobjects o where (is_member(user_name(p.uid)) = 1 or p.grantor = user_id()) and (p.protecttype = 204 or /*grant exists without same grant with grant */ (p.protecttype = 205 and not exists(select * from sysprotects p2 where p2.id = p.id and p2.uid = p.uid and p2.action = p.action and p2.columns = p.columns and p2.grantor = p.grantor and p2.protecttype = 204))) and p.action in (26,193,195,196,197) and p.id = o.id and o.xtype in ('U', 'V') and 0 != (permissions(o.id) & case p.action when 26 then 4 /*REFERENCES basebit on all columns */ when 193 then 1 /*SELECT basebit on all columns */ when 195 then 8 /*INSERT basebit */ when 196 then 16 /*DELETE basebit */ when 197 then 2 /*UPDATE basebit on all columns */ end)

GO

more ▼

answered Oct 29, 2009 at 11:49 AM

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

(comments are locked)
10|1200 characters needed characters left

Type 'P' is a stored procedure I believe.

more ▼

answered Oct 29, 2009 at 11:37 AM

avatar image

EWizard
1

(comments are locked)
10|1200 characters needed characters left

master.dbo.spt_values is an internal table used by SQL Server to store run time configuration values.

What are you trying to acheive?

more ▼

answered Oct 29, 2009 at 11:41 AM

avatar image

Kev Riley ♦♦
64k 48 61 81

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x426
x409

asked: Oct 29, 2009 at 11:35 AM

Seen: 2003 times

Last Updated: Oct 29, 2009 at 11:46 AM

Copyright 2016 Redgate Software. Privacy Policy