permissions on a column

hi,

i run a sqlserver 2000 and im having problems setting a permission a
column in a table..

under a database i have a User that has dataread rights on each table
in the database, but in one table i want to prevent the user from
seeing a column in one perticular table.

i have created the user under security and then i choose the database
user - properties..and i set a X in the specified column...

when i log on as the datareader user i cant see any colummn at all in
the table..

what have a done wrong?

Steve[posted and mailed, vnligen svara i nys]

steve (stebo@.privat.utfors.se) writes:
> i run a sqlserver 2000 and im having problems setting a permission a
> column in a table..
> under a database i have a User that has dataread rights on each table
> in the database, but in one table i want to prevent the user from
> seeing a column in one perticular table.
> i have created the user under security and then i choose the database
> user - properties..and i set a X in the specified column...
> when i log on as the datareader user i cant see any colummn at all in
> the table..
> what have a done wrong?

Used a GUI instead of looking up the commands in Books Online. GUIs may
do the what you expect, or they may do something else. The command to
use is DENY. Here is an example:

use tempdb
go
exec sp_addlogin accesstest, secret
exec sp_adduser accesstest
exec sp_addrolemember db_datareader, accesstest
go
CREATE TABLE tbl (a int NOT NULL, b varchar(23) NOT NULL)
INSERT tbl (a, b) VALUES (9, 'Top secret')
go
SETUSER 'accesstest'
go
SELECT * FROM tbl
go
SETUSER
go
DENY ALL ON tbl (b) TO accesstest
go
SETUSER 'accesstest'
go
SELECT * FROM tbl
go
SETUSER
go
DROP TABLE tbl
EXEC sp_dropuser accesstest
EXEC sp_droplogin accesstest

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||thanks for your help!

I will try this, what do you think about using a VIEW, is this a good choice?

BR

Steve
--

Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns959DF3D2FD2D0Yazorman@.127.0.0.1>...
> [posted and mailed, vnligen svara i nys]
> steve (stebo@.privat.utfors.se) writes:
> > i run a sqlserver 2000 and im having problems setting a permission a
> > column in a table..
> > under a database i have a User that has dataread rights on each table
> > in the database, but in one table i want to prevent the user from
> > seeing a column in one perticular table.
> > i have created the user under security and then i choose the database
> > user - properties..and i set a X in the specified column...
> > when i log on as the datareader user i cant see any colummn at all in
> > the table..
> > what have a done wrong?
> Used a GUI instead of looking up the commands in Books Online. GUIs may
> do the what you expect, or they may do something else. The command to
> use is DENY. Here is an example:
> use tempdb
> go
> exec sp_addlogin accesstest, secret
> exec sp_adduser accesstest
> exec sp_addrolemember db_datareader, accesstest
> go
> CREATE TABLE tbl (a int NOT NULL, b varchar(23) NOT NULL)
> INSERT tbl (a, b) VALUES (9, 'Top secret')
> go
> SETUSER 'accesstest'
> go
> SELECT * FROM tbl
> go
> SETUSER
> go
> DENY ALL ON tbl (b) TO accesstest
> go
> SETUSER 'accesstest'
> go
> SELECT * FROM tbl
> go
> SETUSER
> go
> DROP TABLE tbl
> EXEC sp_dropuser accesstest
> EXEC sp_droplogin accesstest|||steve (stebo@.privat.utfors.se) writes:
> thanks for your help!
> I will try this, what do you think about using a VIEW, is this a good
> choice?

I don't know your business problem, so I cannot comment on that.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Permissions not applied

Hi everyone,
I'm facing a weird problem trying to set permissions on a column in Sql
Server 2000. When using Enterprise Manager the permission on some columns
don't get applied, that is setting Select permissions on Column1, Column2 an
d
Column3 only saves the permission on Column1 and Column2 but not on Column3.
I tried setting the permissions with a GRANT script in the Query Analyzer,
but still without any luck.
Service Pack 4 has been applied to SQL Server 2000.
Has anyone had the same type of troubles setting permissions on columns?
TIA,
Mogens NielsenMogens Nielsen - WM-Data A/S
(MogensNielsenWMDataAS@.discussions.microsoft.com) writes:
> I'm facing a weird problem trying to set permissions on a column in Sql
> Server 2000. When using Enterprise Manager the permission on some
> columns don't get applied, that is setting Select permissions on
> Column1, Column2 and Column3 only saves the permission on Column1 and
> Column2 but not on Column3. I tried setting the permissions with a GRANT
> script in the Query Analyzer, but still without any luck.
Would it be possible for your to post the CREATE TABLE statement for
the table, and the GRANT statements you are using?
It would also be interesting to know how you conclude that the
permission is not applied.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi
I did some testing but not usimg EM and it seems to be worked fine
use demo
create table test (c1 int,c2 int,c3 int)
go
grant select on test to test --test no syadmin,db_owner
go
deny select on test (c3) to test
--connected as test
select * from test
--Server: Msg 229, Level 14, State 5, Line 1
--SELECT permission denied on object 'test', database 'demo', owner 'dbo'.
select c1,c2,c3 from test
--Server: Msg 229, Level 14, State 5, Line 1
--SELECT permission denied on object 'test', database 'demo', owner 'dbo'.
select c1,c2 from test
--works fine
"Mogens Nielsen - WM-Data A/S"
<MogensNielsenWMDataAS@.discussions.microsoft.com> wrote in message
news:FC8AFD90-255F-4942-BC78-BE674669A1FE@.microsoft.com...
> Hi everyone,
> I'm facing a weird problem trying to set permissions on a column in Sql
> Server 2000. When using Enterprise Manager the permission on some columns
> don't get applied, that is setting Select permissions on Column1, Column2
> and
> Column3 only saves the permission on Column1 and Column2 but not on
> Column3.
> I tried setting the permissions with a GRANT script in the Query Analyzer,
> but still without any luck.
> Service Pack 4 has been applied to SQL Server 2000.
> Has anyone had the same type of troubles setting permissions on columns?
> TIA,
> Mogens Nielsen|||Hi Uri and Erland,
thank you both for trying to solve my problems... :-)
The table consists of more than 350 columns, which may be the cause. I don't
know, really. As a solution I created a view on a subset of the columns
instead, in which I've managed to set the permissions.
So problem solved or at least avoided...
Regards,
Mogens
"Uri Dimant" wrote:

> Hi
> I did some testing but not usimg EM and it seems to be worked fine
> use demo
> create table test (c1 int,c2 int,c3 int)
> go
> grant select on test to test --test no syadmin,db_owner
> go
> deny select on test (c3) to test
> --connected as test
> select * from test
> --Server: Msg 229, Level 14, State 5, Line 1
> --SELECT permission denied on object 'test', database 'demo', owner 'dbo'
.
> select c1,c2,c3 from test
> --Server: Msg 229, Level 14, State 5, Line 1
> --SELECT permission denied on object 'test', database 'demo', owner 'dbo'
.
> select c1,c2 from test
> --works fine
>
>
>
> "Mogens Nielsen - WM-Data A/S"
> <MogensNielsenWMDataAS@.discussions.microsoft.com> wrote in message
> news:FC8AFD90-255F-4942-BC78-BE674669A1FE@.microsoft.com...
>
>sql