Two quick questions.

#41

Thanks really appreciated.

"Russell Fields" <russellfields (AT) nomail (DOT) com> wrote

Quote:

Smith,

The only way to get two different sets of columns is to have two selects.
That was what the IF / ELSE code did.

If you want the same set of columns to return, but the values in one
column to be protected, the CASE code does that.

Of course, the tests for either version do not have to be hardcoded on
USER_NAME(). You could have a table of usernames that identifies whether
they get two or three columns back. You could use
IS_MEMBER(roleorgroupname) to determine whether the user is a member of a
particular role or Windows domain group to identify who gets which
columns.

If you are interested in an in-depth discussion of the tricks available
for row and column security, then you should read:
http://www.microsoft.com/technet/pro.../multisec.mspx

RLF

"Smith" <naissani (AT) hotmail (DOT) co> wrote in message
news:C54F839D-9611-4554-8315-657A3B72C480 (AT) microsoft (DOT) com...
Thanks.. Just wondering for Point #1, there no other way right.

"Russell Fields" <russellfields (AT) nomail (DOT) com> wrote in message
news:%23IbHddPaIHA.1376 (AT) TK2MSFTNGP02 (DOT) phx.gbl...
Smith,

Comments in line:

1. I have a stored procedure call SPAccess and in that there are three
columns... (now I want if User1 access that stored procedure he/she can
access 2 columns of data and if User2 access then he/she access all
three columns of data) how I can fullfill that.

You could use an IF to run two different queries depending on the user.

IF USER_NAME() = 'Fred'
SELECT a, b, c FROM MyTable
ELSE
SELECT a, b FROM MyTable

Or you could use a CASE to return all columns be control what
information is revealed.

SELECT a, b,
CASE
WHEN USER_NAME() = 'Fred' THEN
c
ELSE
NULL
END AS c
FROM MyTable

2. If I used truncate table, can I recover that in Point in time
recovery.

If you restored a fully logged database to a point in time prior to the
truncate, you would have the contents up to that point in time. I trust
that you realize that the entire database has to be restored, since
there is no function to restore a single table.

(Workaround for a 'table restore': Restore the database to another name,
e.g. RecoveringMyDatabase, to the proper time. Copy the table contents
from RecoveringMyDatabase to MyDatabase. Deal with any referential
problems that remain.)

RLF

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Two quick questions.

microsoft.public.sqlserver.clients microsoft.public.sqlserver.clients

Re: Two quick questions. - 02-07-2008 , 12:05 PM