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...

Custom Order By...Revisited

Hi

I would like to return rows in a custom order, similar to this post. However, in my case I do not have a fixed number of 'sort by' items. The order I would like to have returned from Col2 is:

'A' Numeric Values in numeric order 0-99 All other alpha values

For example:
'A', '0', '1', '3', '65', 'MyValue1', 'MyValue2'

How could I achieve this?

thanks

Richard

Here You Go...

Code Snippet

Create Table #sorttest (

[Col2] Varchar(100)

);

Insert Into #sorttest Values('A');

Insert Into #sorttest Values('12');

Insert Into #sorttest Values('1');

Insert Into #sorttest Values('3');

Insert Into #sorttest Values('5');

Insert Into #sorttest Values('6');

Insert Into #sorttest Values('78');

Insert Into #sorttest Values('100');

Insert Into #sorttest Values('MyValue1');

Insert Into #sorttest Values('MyValue3');

Insert Into #sorttest Values('MyValue2');

Select

*

From

#sorttest

Order By

Case When Col2 Like '[A-Z]' Then 1

When Isnumeric(Col2)=1 Then 2

Else 3 End,

Case When Isnumeric(Col2)=1 Then Convert(float,Col2) End,

Col2

/*

::Output

Col2

-

A

1

3

5

6

12

78

100

MyValue1

MyValue2

MyValue3

*/

|||hi, try this

SELECT MyMixedField
FROM MyTable
WHERE PATINDEX('%[0-9]%', MyMixedField) = 0
UNION ALL
SELECT MyMixedField
FROM MyTable
WHERE ISNUMERIC(MyMixedField) = 1
UNION ALL
SELECT MyMixedField
FROM MyTable
WHERE PATINDEX('%[0-9]%', MyMixedField) > 0
and ISNUMERIC(MyMixedField) <> 1|||i think manivannan's solution is much better,

@.manivannan..

it think its better to use patindex('%[0-9]%', col2) = 0 for alpha valued records

Order By

Case When patindex('%[0-9]%', col2) = 0 Then 1

When Isnumeric(Col2)=1 Then 2

Else 3 End,

Case When Isnumeric(Col2)=1 Then Convert(float,Col2) End,

Col2|||thank you both very much for your quick and accurate responses!

Works perfectly
|||

It seems that manivannan's suggested use of

Like '[A-Z]'

is most likely the best option to sort on single letter alpha. Don't you think that the

patindex('%[0-9]%', col2) = 0

suggestion 'might' include things like an asterisk, period, @. symbol, $ symbol, etc.? (-As well as allowing multi-character alpha entries on the first sort level...)

However, I would caution that using manivannan's suggestion to use isnumeric() 'could' cause problems. For example, the following evaluates to TRUE

SELECT isnumeric( '$' )

But is it a number? Should it sort with the alphas?

|||Hi

Good point, but actually in this case we don't allow non-alphanumeric characters in this field, so it's not really an issue!

thanks everyone for help on this