x
login about faq Site discussion (meta-askssc)

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

Govin gravatar image

Govin
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.2k 56 63 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

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

Kev Riley gravatar image

Kev Riley ♦♦
46.1k 38 43 69

(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



Facebook logo Follow Ask SSC on Facebook
Find Ask SSC on Google+
linkedin logo Find us on LinkedIn

Topics:

x321
x272

asked: Oct 29 '09 at 11:35 AM

Seen: 1022 times

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

Copyright © 2002-2012 Simple Talk Publishing. All Rights Reserved. If you have any queries, please contact the site administrators.
Ask SQL Server Central is a community service provided by Red Gate.