Two quick questions.

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.

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


Thanks in advance.

Smith,

Comments in line:

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


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

Smith,

Comments in line:

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


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

Smith,

Comments in line:

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


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

Smith,

Comments in line:

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


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

Smith,

Comments in line:

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


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

Smith,

Comments in line:

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


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

Smith,

Comments in line:

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


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

Smith,

Comments in line:

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


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

Smith,

Comments in line:

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


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