Re: How can I tell if a user has EXEC permission for a stored proc

From: Bojidar Alexandrov (bojo_do_not_spam_at_kodar.net)
Date: 05/13/05

  • Next message: STech: "RE: Failure Audit log" 
    Date: Fri, 13 May 2005 11:33:18 +0300

    You have to query master database for extended sprocs...

    Bojidar Alexandrov

    "Hal Heinrich" <HalHeinrich@discussions.microsoft.com> wrote in message
    news:70C00671-CCEF-4F00-AF5D-6D097FA898EB@microsoft.com...
    > Hi Mark,
    >
    > I tried a few things:
    > First off, if I run:
    > SELECT COUNT(*) FROM dbo.sysprotects WHERE (id =
    OBJECT_ID('xp_loginconfig'))
    > I get zero back. Also
    > SELECT COUNT(*) FROM dbo.sysobjects WHERE (id =
    OBJECT_ID('xp_loginconfig'))
    > returns zero. So this looks like a dead end.
    >
    > Now if I run:
    > SELECT COUNT(*) FROM dbo.sysprotects WHERE (id = OBJECT_ID('myproc'))
    > I get one back. So far so good. Next I run:
    > SELECT dbo.sysusers.name FROM dbo.sysprotects INNER JOIN
    > dbo.sysusers ON dbo.sysprotects.uid = dbo.sysusers.uid
    > WHERE (dbo.sysprotects.id = OBJECT_ID('myproc'))
    > which returns a single name, but not 'Jones' - who does have execute
    > permission.
    > So this also looks like a dead end.
    >
    > Any thoughts or suggestions?
    >
    > Thanks in advance,
    > Hal
    >
    > "Mark Allison" wrote:
    >
    > > Hal
    > >
    > > Have a look at the sysprotects table - all the info you need is in
    there. If
    > > you need help with the query, post back.
    > >
    > > --
    > > Mark Allison, SQL Server MVP
    > > http://www.markallison.co.uk
    > >
    > > Looking for a SQL Server replication book?
    > > http://www.nwsu.com/0974973602m.html
    > >
    > >
    > >
    > >
    > > "Hal Heinrich" wrote:
    > >
    > > > Given a user-name and a stored procedure name, can someone provide me
    with
    > > > the TSQL code to set a boolean indicating whether that user has EXEC
    > > > permission to that proc?
    > > >
    > > > The specific proc I'm interested in is xp_loginconfig.
    > > >
    > > > Thanks in advance for your help,
    > > > Hal Heinrich
    > > > VP Technology
    > > > Aralan Solutions Inc.

  • Next message: STech: "RE: Failure Audit log"

    Relevant Pages

    • Re: WOT: Danish Cartoons and Muhammed
      ... Mark Alan Miller wrote: ... have been dead in childbirth in no time at ... Antibiotics, vaccines, surgeries, and all the rest of modern ... died of liver failure within a few years from Hep B. It would have been ...
      (rec.arts.mystery)
    • Re: Transaction handling - Read Committed
      ... Yes this behavior is due to the Read Committed Isolation level requirements ... and has nothing really to do with SQL Server. ... As Mark pointed out you can ... >>> This query does not return with results till query 1 completes. ...
      (microsoft.public.sqlserver.programming)
    • Re: long running queries
      ... Mark is right -- you don't have much of an option with SQL Server 2000. ... >> that would result in a large data set and complex query, ... > Some of the client tools will warn you if the query looks like its going ...
      (microsoft.public.sqlserver.olap)
    • Re: How to retrieve stored procedure error code in C#?
      ... Mark and William - thx for the replies, as they pointed me in the right ... > All Sql Server stored procedures have a return value parameter, ... > Please do not send email directly to this alias. ...
      (microsoft.public.dotnet.framework.adonet)
    • Re: Identity columns with ClientDataSets?
      ... "Mark A. Deal" wrote in message ... > How do you handle Identity columns with SQL Server and ClientDataSets? ... > I'm not having much luck because I get the error, "No corresponding Master ... > GhostFill Certified Consultant ...
      (borland.public.delphi.database.ado)