 |
|
|
|
Next: How to stop duplicate entry
|
| Author |
Message |
External
Since: Jan 22, 2008
Posts: 16
|
(Msg. 1) Posted: Wed Feb 06, 2008 12:00 pm
Post subject: Two quick questions.
Archived from groups: microsoft>public>sqlserver>clients (more info?)
|
|
|
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. |
|
| Back to top |
|
 | |
External
Since: Jan 10, 2008
Posts: 608
|
(Msg. 2) Posted: Wed Feb 06, 2008 2:34 pm
Post subject: Re: Two quick questions. [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
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 |
|
| Back to top |
|
| |
External
Since: Jan 22, 2008
Posts: 16
|
(Msg. 3) Posted: Wed Feb 06, 2008 7:41 pm
Post subject: Re: Two quick questions. [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
Thanks.. Just wondering for Point #1, there no other way right.
"Russell Fields" wrote in message
> 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
> >> Stay informed about: Two quick questions. |
|
| Back to top |
|
| |
External
Since: Jan 10, 2008
Posts: 608
|
(Msg. 4) Posted: Thu Feb 07, 2008 8:37 am
Post subject: Re: Two quick questions. [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
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/prodtechnol/sql/2005/multisec.mspx
RLF
"Smith" wrote in message
> Thanks.. Just wondering for Point #1, there no other way right.
>
>
> "Russell Fields" wrote in message
>
>> 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
>>
> >> Stay informed about: Two quick questions. |
|
| Back to top |
|
| |
External
Since: Jan 22, 2008
Posts: 16
|
(Msg. 5) Posted: Thu Feb 07, 2008 1:05 pm
Post subject: Re: Two quick questions. [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
Thanks really appreciated.
"Russell Fields" wrote in message
> 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/prodtechnol/sql/2005/multisec.mspx
>
> RLF
>
> "Smith" wrote in message
>
>> Thanks.. Just wondering for Point #1, there no other way right.
>>
>>
>> "Russell Fields" wrote in message
>>
>>> 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
>>>
>>
>
> >> Stay informed about: Two quick questions. |
|
| Back to top |
|
| |
| Related Topics: | OLEDB: Prepared cmd with NULL input param for NOT NULL INT.. - Provider: SQLOLEDB Version: SQL Server 2000 8.00.760 Script for creating table: CREATE TABLE [dbo].[ADRVERTRETER] ( <font color=purple> ; [ROWID] [timestamp] NULL ,</font> <font color=purple> ; [ROWVERTRETER] [int] NO...
[MS Design Tools] - Class not registered." - On a Windows 2000 pro workstation I am using SQL 2000 enterprise manager I am trying to return all rows, I get the error message "An unexpected error happened during this operation. [MS Design Tools] - Class not registered." I have reinstall...
Need help choosing front end for SQL Server - I've been working on an Access 97 database that's pretty much reached it's limit in terms of performance and reliability. Although it supports relatively few users (5-10 concurrent) it contains a lot of data (around 30 tables, some with several million..
SQL server tables read-only to ADP - I have successfully migrated an Access 2003 database to SQL server - at least all the tables and queries that resolve into views migrated successfully. I also sucessfully migrated all my forms and code into an ADP project. Everything works with one..
Opening table in SQL Server 2005? - This ought to be easy. But I can't figure out how to do it. Using Enterprise Manager in SQL Server 2000, I point to a table, select it, right click and choose Open Table-> Return all rows. Now I have the table open and I can edit to my heart's.. |
|
You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum
|
|
|
|
|
|
|
FAQ
Search
Profile
Private Messages
Log in
