Assign permissions to allow updates but deny select on table

I have a table that I want to deny users to select, but allow inserts,
updates and deletes. So in the permissions for that table, I have
given the security Group Update, Insert and Delete rights and Denied
Select access. I connect to the table via odbc and have been able to
get the Insert queries working fine. However, my attempts to run
Update or Delete queries continually produce errors.
Here is a sample query I'm trying to run
DELETE tblPasswordsforDirector.PassMasterID
FROM tblPasswordsforDirector
WHERE (((tblPasswordsforDirector.PassMasterID)=339));
Here is a summary of the odbc error I get:
Select Permission denied on column 'password' of object
tblPasswordsforDirector.
In troubleshooting, I tried to use the COLUMN permissions to give
Select access to the two columns I don't care if the users see, and
then Deny Select access on the two columns I don't want them to see
(one of which happens to be the 'password' column shown in error
above).
I guess I'm a little confused at why a DELETE query requires Select
permissions. And if it does, then why does SQL give you the ability to
Allow DELETE permissions and Deny SELECT permissions on the same
table?
Any help would be most appreciated.
Thanks in advance.Tanzen (aaron.nasby@.gmail.com) writes:
> I have a table that I want to deny users to select, but allow inserts,
> updates and deletes. So in the permissions for that table, I have
> given the security Group Update, Insert and Delete rights and Denied
> Select access. I connect to the table via odbc and have been able to
> get the Insert queries working fine. However, my attempts to run
> Update or Delete queries continually produce errors.
> Here is a sample query I'm trying to run
> DELETE tblPasswordsforDirector.PassMasterID
> FROM tblPasswordsforDirector
> WHERE (((tblPasswordsforDirector.PassMasterID)=339));
> Here is a summary of the odbc error I get:
> Select Permission denied on column 'password' of object
> tblPasswordsforDirector.
> In troubleshooting, I tried to use the COLUMN permissions to give
> Select access to the two columns I don't care if the users see, and
> then Deny Select access on the two columns I don't want them to see
> (one of which happens to be the 'password' column shown in error
> above).
I'm not sure what you did, but this appears to work as you want:
CREATE LOGIN Julle WITH PASSWORD = 'Vetebulle'
go
CREATE USER 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
EXECUTE AS LOGIN = 'Julle'
go
DELETE spezial WHERE a = 13
go
REVERT
go
DROP TABLE spezial
DROP USER Julle
DROP LOGIN Julle
I tested this on SQL 2005. I also ran a similar script on SQL 2000.
Could you post a repro that demonstrates the problem?
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|||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|||Tanzen (aaron.nasby@.gmail.com) 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, 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|||On Jul 11, 3:24 pm, Erland Sommarskog <esq...@.sommarskog.se> wrote:
> Tanzen (aaron.na...@.gmail.com) writes:
> 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...@.sommarskog.se
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodte
chnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousv
ersions/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 permissio
n
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.|||Tanzen (aaron.nasby@.gmail.com) writes:
> 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 permiss
ion
> 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.
Yes, they may be more helpful. But you could use SQL Server profiler
to see what statements that Access is submitting. Maybe it's better to
use a stored procedure instead. But the Access folks may know how tell
Access to do the right thing.
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

asp.net want to make SQL time format of 00:00:00:000 to 14:42:51:153 when inserting in dat

I have asp.net 1.1 web form and it inserts date and time in SQL database, but it insert only date not time , It insert date time in following format

2002-01-22 00:00:00.000

some one tell me why it is not inserting time or why it is inserting time as 00:00:00:000.

I want to my time to look like 14:42:51:153. (format)

How can I change my time format

give me asp.net codes for time formating or what do I need to do to resolve the problem,

thank you

maxmax

I think your application is not sending the date properly. Do a response.write of the value you are sending to the db and verify.

ASP.NET / SQL Server Stored Procedure Question

Hello,

I wrote a stored procedure that inserts data into one table, then inserts the value of the identity column into another table:

SET

NOCOUNTON;

INSERTINTO ContactUs_TBL

(FullName, Email, Phone, Message)

VALUES

(@.FullName, @.Email, @.Phone, @.Message)

SELECT@.@.IDENTITY

INSERTINTO ContactUsQuestions_TBL

(QuestionText, ContactId)

VALUES

(@.QuestionText,@.@.IDENTITY)

In the CodeFile in asp.net (c#.net), I'm not what to set the value property to below. Right now I just hardcoded a 2 to see how it would work. Could anyone help me out and tell me what I should put here? Each of the other statements I used were set to the value of a form control, but since this id isn't a form control, just an identity column, I'm not sure what to do:

comm.Parameters.Add(

"@.ContactId",SqlDbType.Int);

comm.Parameters["@.ContactId"].Value = 2;

-- rkeslar

Replace @.@.IDENTITY with SCOPE_IDENTITY(). This isn't related to your problem, but you should do it anyhow.

You don't add a parameter for ContactID. You don't pass it into the stored procedure, and you don't tell .NET about it.

|||

hmm. ContactID is a primary key in the ContactUs_TBL, so I see why you wouldn't pass it into the stored procedure or tell .net about it for that table. But it's also a foreign key in the ContactUsQuestions_TBL so how is it going to get inserted into that table if I don't pass it into the stored procedure and tell .net about it?

Thanks

|||Because the stored procedure picks up the value when it's running and passes it to the second insert statement.