Custom server permissions?

Hi,
I need to grant a user access to one or two specific system stored
procs without giving permission to everything else in the fixed server
role that allows them. Specifically, they need rights to
sp_addlinkedserver and sp_addlinkedsrvlogin, but they shouldn't have
all the rights associated with securityadmin. Is there a way to grant
specific rights to just these?
TIA,
BarryBoth of these system stored procedures have hard coded permission checks in
them. I'm not a big fan of these as it really limits flexibility. The only
way would be to alter them to remove these checks but this would not be a
supported scenario. sp_addlinkedserver has a hard coded check for membership
of the setupadmin server role and sp_addlinkedsrvlogin has a hard coded
check for membership of the securityadmin server role. The only alternative
would be one that I use a lot for scenarios where I want lower privilege
users to be able to do a very specific action and that is to write a queue
system whereby they basically have a table that they can insert rows into
via a stored procedure and this table is polled by a SQL Agent job that runs
once a minute and executes the specific commands they are allowed to run.
This way you can code so they can only do a very specific action (otherwise
you would lead yourself open a large security hole)
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
<barrygilbertusa_no_spam@.yahoo.com> wrote in message
news:1101316733.934708.165050@.f14g2000cwb.googlegroups.com...
> Hi,
> I need to grant a user access to one or two specific system stored
> procs without giving permission to everything else in the fixed server
> role that allows them. Specifically, they need rights to
> sp_addlinkedserver and sp_addlinkedsrvlogin, but they shouldn't have
> all the rights associated with securityadmin. Is there a way to grant
> specific rights to just these?
> TIA,
> Barry
>|||Jasper,
Thanks for your reply. I'll give this a try.
Barry
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message news:<#hwMr6n0EHA.1392@.TK2MSFTNG
P14.phx.gbl>...[vbcol=seagreen]
> Both of these system stored procedures have hard coded permission checks i
n
> them. I'm not a big fan of these as it really limits flexibility. The only
> way would be to alter them to remove these checks but this would not be a
> supported scenario. sp_addlinkedserver has a hard coded check for membersh
ip
> of the setupadmin server role and sp_addlinkedsrvlogin has a hard coded
> check for membership of the securityadmin server role. The only alternativ
e
> would be one that I use a lot for scenarios where I want lower privilege
> users to be able to do a very specific action and that is to write a queue
> system whereby they basically have a table that they can insert rows into
> via a stored procedure and this table is polled by a SQL Agent job that ru
ns
> once a minute and executes the specific commands they are allowed to run.
> This way you can code so they can only do a very specific action (otherwis
e
> you would lead yourself open a large security hole)
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> <barrygilbertusa_no_spam@.yahoo.com> wrote in message
> news:1101316733.934708.165050@.f14g2000cwb.googlegroups.com...