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.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 sy
min 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|||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" <HalHeinrich@.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.|||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" <HalHeinrich@.discussions.microsoft.com> wrote in message
> news:ADE5D31A-7F11-4782-A560-EDEA7E89D3CA@.microsoft.com...
>
>|||Anith,
Thank you for replying. However, my question is not "how do you grant?",
rather it is "how can you tell?"
Hal
"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 t
he
> master database and members of the sy
min 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
>
>|||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 sy
min fixed
server role, but can be granted to other users.
"Hal Heinrich" <HalHeinrich@.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'))ed">
> I get zero back. Also
> SELECT COUNT(*) FROM dbo.sysobjects WHERE (id =
OBJECT_ID('xp_loginconfig'))[color=darkr
ed]
> 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:
>
that
with|||>> However, my question is not "how do you grant?", rather it is "how can
Have you looked in to the PERMISSIONS() function?
Anith
eralper wrote: