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