Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Unanswered: How to tell if user has exec auth on sproc

    And no, not through EM or SSMS

    I want to interogate the catalog, so I can hae a job execute and do the grants in case a developer forgets

    I mean I guess I can do the all everytime, but I don't know what the impact would be. It appears to be none, because of already done that, but in DB2 that would be bad as it would keep adding rows to the system tables

    I have this so far

    Code:
        select * 
          from syspermissions p
    inner join sysusers u
    	on u.uid = p.grantee
    inner join sysobjects o
    	on o.id = p.id
         where u.name = 'mepuser'
           and o.name not like 'dt_%'
    order by p.id
    The M$ catalog is a royal pain
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Is this SQL2K?

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by pootle flump
    Is this SQL2K?

    Yes, but does it matter?

    Are the system views in 2k5 better? Haven't really looked yet
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Disect sp_helprotect, and you should see everything you need. Are you sure you want to give blanket permissions, though?

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    to that user...yes, it's a connection pooling id

    thanks
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Brett Kaiser
    Are the system views in 2k5 better? Haven't really looked yet
    Tons










  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    sp_helprotect

    wow...dynamic sql, temp tables, non ansi joins

    I think I'll just store the results from the output


    and comments like

    /* Columns_Orig where first byte is 1 means off means on and on mean off
    where first byte is 0 means off means off and on mean on */
    Code:
    CREATE Table #t1_Prots
    	(	Id			int			 Null
    		,Type1Code		char(6)			 NOT Null
    		,ObjType		char(2)			 Null
    		,ActionName		varchar(20)		 Null
    		,ActionCategory		char(2)			 Null
    		,ProtectTypeName	char(10)		 Null
    		,Columns_Orig		varbinary(32)		 Null
    		,OwnerName		sysname			 NOT Null
    		,ObjectName		sysname			 NOT Null
    		,GranteeName		sysname			 NOT Null
    		,GrantorName		sysname			 NOT Null
    		,ColumnName		sysname			 Null
    		,ColId			smallint		 Null
    		,Max_ColId		smallint		 Null
    		,All_Col_Bits_On	tinyint			 Null
    		,new_Bit_On		tinyint			 Null )
    Last edited by Brett Kaiser; 02-06-08 at 14:32.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    And now they're just trying to be too slick with this

    Code:
    /*  Output the report	*/
    EXECUTE(
    'Set nocount off
    
    SELECT	''Owner''		= substring (OwnerName   ,1 ,' + @charMaxLenOwner   + ')
    
    		,''Object''		= substring (ObjectName  ,1 ,' + @charMaxLenObject  + ')
    
    		,''Grantee''	= substring (GranteeName ,1 ,' + @charMaxLenGrantee + ')
    
    		,''Grantor''	= substring (GrantorName ,1 ,' + @charMaxLenGrantor + ')
    
    		,''ProtectType''= ProtectTypeName
    
    		,''Action''		= substring (ActionName ,1 ,' + @charMaxLenAction + ')
    
    		,''Column''		= substring (ColumnName ,1 ,' + @charMaxLenColumnName + ')
       from	#t1_Prots
       order by
    		ActionCategory
    		,Owner				,Object
    		,Grantee			,Grantor
    		,ProtectType		,Action
    		,ColId  --Multiple  -123s  ( <0 )  possible
    
    Set nocount on'
    )
    I mean why bother
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    OK, I thought i had something usable in my old SQL directory. Add in an outer join or two, and you should have what you need:

    Code:
    declare @name varchar(120)
    set @name = 'sap'
    
    
    select case when p.action = 26 then 'References'
    	  when p.action = 193 then 'Select'
    	  when p.action = 195 then 'Insert'
    	  when p.action = 196 then 'Delete'
    	  when p.action = 197 then 'Update'
    	  when p.action = 224 then 'Execute'
    	  else 'something else' end as permission, 
    	case when p.protecttype = 206 then 'DENIED'
    	  when p.protecttype = 205 then 'Granted'
    	  when p.protecttype = 204 then 'Granted with grant option'
    	  else 'Something else' end as "deny/grant",
    	o.name as "Object Name",
    	user_name(a.uid) as Grantee
    from sysprotects p join
    	sysobjects o on p.id = o.id join
    	(select u.uid
    	from sysusers u
    	where u.name = @name
    	union all
    	select r.uid
    	from sysusers u join
    		sysmembers m on u.uid = m.memberuid join
    		sysusers r on m.groupuid = r.uid
    	where u.name = @name) a on p.uid = a.uid

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •