Does a user have EXEC permission for a stored procedure?

12 May 2005 1:59 PM

Hal Heinrich

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.

12 May 2005 2:50 PM

Anith Sen

SQL Server Books Online states that the execute permissions for
xp_loginconfig default to members of the db_owner fixed database role in the
master database and members of the sysadmin fixed server role, but can be
granted to other database users. So if the user is not a member of the dbo
role in master, you can use a simple GRANT statement like:

GRANT EXECUTE ON xp_loginconfig TO <youruser> ;

--
Anith

12 May 2005 3:00 PM

Armando Prato

Assuming a proc named 'myproc' and a user 'Jones', here's a snippet that
will check for exec priv.

declare @name sysname, @myboolean bit
set @name = 'Jones'

if exists (select *
           from sysprotects
           where id = object_id('myproc')
           and uid = (select uid from sysusers where name = @name)
           and action = 224
           and protecttype <> 206)
    set @myboolean = 1
else
    set @myboolean = 0

select @myboolean

Show quote

"Hal Heinrich" <HalHeinr***@discussions.microsoft.com> wrote in message
news:ADE5D31A-7F11-4782-A560-EDEA7E89D3CA@microsoft.com...
> 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.

12 May 2005 11:03 PM

Hal Heinrich

Hi Armando,

Thanks for your reply - it certainly gave me some things to try.

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, Hal

Show quote

"Armando Prato" wrote:

> Assuming a proc named 'myproc' and a user 'Jones', here's a snippet that
> will check for exec priv.
>
> declare @name sysname, @myboolean bit
> set @name = 'Jones'
>
> if exists (select *
>            from sysprotects
>            where id = object_id('myproc')
>            and uid = (select uid from sysusers where name = @name)
>            and action = 224
>            and protecttype <> 206)
>     set @myboolean = 1
> else
>     set @myboolean = 0
>
> select @myboolean
>
>
> "Hal Heinrich" <HalHeinr***@discussions.microsoft.com> wrote in message
> news:ADE5D31A-7F11-4782-A560-EDEA7E89D3CA@microsoft.com...
> > 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.
>
>
>

13 May 2005 3:22 PM

Armando Prato

Couple of things come to mind

Did you run against the master database? Extended stored procs live
in master and are not found in newly created databases. Also, did you
explicitly
grant execute to the user(s) in question?

The query...

SELECT dbo.sysusers.name
FROM dbo.sysprotects
JOIN dbo.sysusers ON (dbo.sysprotects.uid = dbo.sysusers.uid)
WHERE (dbo.sysprotects.id = OBJECT_ID('xp_loginconfig'))

....looks ok. It just sounds like you need to explicitly grant execute
to all users you expect will have this permission outside of the dbo.

Here is the BOL snippet on this xp:

Execute permissions for xp_loginconfig default to members of the db_owner
fixed database role in the master database and members of the sysadmin fixed
server role, but can be granted to other users.

Show quote

"Hal Heinrich" <HalHeinr***@discussions.microsoft.com> wrote in message
news:81ED433C-8190-45F1-8FC5-07ED38DDCFD4@microsoft.com...
> Hi Armando,
>
> Thanks for your reply - it certainly gave me some things to try.
>
> 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, Hal
>
> "Armando Prato" wrote:
>
> > Assuming a proc named 'myproc' and a user 'Jones', here's a snippet
that
> > will check for exec priv.
> >
> > declare @name sysname, @myboolean bit
> > set @name = 'Jones'
> >
> > if exists (select *
> >            from sysprotects
> >            where id = object_id('myproc')
> >            and uid = (select uid from sysusers where name = @name)
> >            and action = 224
> >            and protecttype <> 206)
> >     set @myboolean = 1
> > else
> >     set @myboolean = 0
> >
> > select @myboolean
> >
> >
> > "Hal Heinrich" <HalHeinr***@discussions.microsoft.com> wrote in message
> > news:ADE5D31A-7F11-4782-A560-EDEA7E89D3CA@microsoft.com...
> > > 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.
> >
> >
> >

12 May 2005 11:04 PM

Hal Heinrich

Anith,

Thank you for replying. However, my question is not "how do you grant?",
rather it is "how can you tell?"

Hal

Show quote

"Anith Sen" wrote:

> SQL Server Books Online states that the execute permissions for
> xp_loginconfig default to members of the db_owner fixed database role in the
> master database and members of the sysadmin fixed server role, but can be
> granted to other database users. So if the user is not a member of the dbo
> role in master, you can use a simple GRANT statement like:
>
> GRANT EXECUTE ON xp_loginconfig TO <youruser> ;
>
> --
> Anith
>
>
>

13 May 2005 9:31 PM

Anith Sen

>> However, my question is not "how do you grant?", rather it is "how can
>> you tell?"

Have you looked in to the PERMISSIONS() function?

--
Anith