Re: Assign permissions to allow updates but deny select on table
- From: Tanzen <aaron.nasby@xxxxxxxxx>
- Date: Wed, 11 Jul 2007 15:33:01 -0700
On Jul 11, 3:24 pm, Erland Sommarskog <esq...@xxxxxxxxxxxxx> wrote:
Tanzen (aaron.na...@xxxxxxxxx) writes:
Some of the commands you show in your example don't seem to work for
me. I am running SQL 2000. Forgive my ignorance, but I'm not sure what
those commands should be in SQL 2000
I hope you've now learnt that you should always say which version of SQL
Server you are using. :-)
Below is the SQL 2000 version of the script that I posted. By the way,
you did not include the full text of your error message.
sp_addlogin Julle, 'Vetebulle'
go
sp_adduser Julle
go
CREATE TABLE spezial (a int NOT NULL,
secret varchar(23) NOT NULL)
go
GRANT DELETE, INSERT, UPDATE, SELECT ON spezial TO Julle
go
DENY SELECT ON spezial (secret) TO Julle
go
INSERT spezial (a, secret) VALUES (1, ')(2344')
INSERT spezial (a, secret) VALUES (13, '25344')
go
SETUSER 'Julle'
go
DELETE spezial WHERE a = 13
go
SETUSER
go
DROP TABLE spezial
EXEC sp_dropuser Julle
EXEC sp_droplogin Julle
--
Erland Sommarskog, SQL Server MVP, esq...@xxxxxxxxxxxxx
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Your commands were very helpful. I was able to change the script for
my existing table and confirmed the security settings are working when
I use query analyzer. If I try to do a select queryI am denied
permission. If I run a Delete query, it succeeds.
I am using an MS Access 2003 database with an ODBC connection to the
tables to perform the same delete queries, but they fail. The error
when I run the delete query from MS Access 2003 is:
---------------------------
ODBC --call failed.
[Microsoft][ODBC SQL Server Driver][SQL Server] Select permission
denied on column 'password' of object 'tblPasswordsforDirector',
database 'Passwords_IT", owner 'dbo'. (#230) [Microsoft][ODBC SQL
Server Driver][SQL server]SELECT permission denied on column
'UserName' of object 'tblPasswordsforDirector', database
'Passwords_IT', owner 'dbo'. (#230)
---------------------------
I'm assuming I need to post this to an Access forum.
So unless you have any ideas, I'll move this on to another forum.
Thank you again for your help.
.
- Follow-Ups:
- Re: Assign permissions to allow updates but deny select on table
- From: Erland Sommarskog
- Re: Assign permissions to allow updates but deny select on table
- References:
- Assign permissions to allow updates but deny select on table
- From: Tanzen
- Re: Assign permissions to allow updates but deny select on table
- From: Erland Sommarskog
- Re: Assign permissions to allow updates but deny select on table
- From: Tanzen
- Re: Assign permissions to allow updates but deny select on table
- From: Erland Sommarskog
- Assign permissions to allow updates but deny select on table
- Prev by Date: Re: How can I see what permissions "VIEW SERVER STATE" has?
- Next by Date: Re: Assign permissions to allow updates but deny select on table
- Previous by thread: Re: Assign permissions to allow updates but deny select on table
- Next by thread: Re: Assign permissions to allow updates but deny select on table
- Index(es):
Relevant Pages
|
|
