SQL Server query help


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 '09 at 11:35 AM in Default

Govin gravatar image

22 2 2 2

(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 '09 at 11:49 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

I voted you up just for the chocolate teapot line :)
Oct 29 '09 at 03:56 PM Blackhawk-17
(comments are locked)
10|1200 characters needed characters left

Type 'P' is a stored procedure I believe.

more ▼

answered Oct 29 '09 at 11:37 AM

EWizard gravatar image


(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 '09 at 11:41 AM

Kev Riley gravatar image

Kev Riley ♦♦
50.7k 43 49 76

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Oct 29 '09 at 11:35 AM

Seen: 1466 times

Last Updated: Oct 29 '09 at 11:46 AM