Can select from db (read) but cannot write

Question

Post reply

Can select from db (read) but cannot write

Dorian Gray

SSC Rookie

Points: 46
More actions
June 15, 2009 at 9:14 am

#201402

I am at my wits end - any help will be appreciated. I have a tester connecting to a SQL2K5 db installed on my pc at work (we're all within the LAN). I have granted this person all the permissions - default schema "dbo"; "db_datareader" & "db_datawriter" - when I run sp_helprole & sp_helprolemember, I can see that all the right permissions are listed against this test database and the user.
When the tester runs the application - all the screens using a "SELECT" query works; Some of the screens using "DML - Update, Insert" also work; Only a few screens that use "DML - Update" do not work - the message is "object does not exist or permission denied";
Problem is objects *do* exist; user is able to "SELECT" from these objects;I use schemaname.objectname in my queries, so that's not an issue...

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

oops double post sorry folks...continue below

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323518 More actions · Answer 2

Dorian Gray (6/15/2009)
I am at my wits end - any help will be appreciated. I have a tester connecting to a SQL2K5 db installed on my pc at work (we're all within the LAN). I have granted this person all the permissions - default schema ; & - when I run sp_helprole & sp_helprolemember, I can see that all the right permissions are listed against this test database and the user.
When the tester runs the application - all the screens using a query works; Some of the screens using also work; Only a few screens that use do not work - the message is ;
Problem is objects *do* exist; user is able to from these objects;I use schemaname.objectname in my queries, so that's not an issue...

some of what you wrote got stripped out because it was between brackets and was omitted by the forum in case it was an html tag...but somehow a SELECT drop down was allowed to sneak thru...kewl.

i would guess it's all security related.

Is the application windows based or web based? is there any chance, that for some of the forms/pages, it is not using the user's info to connect, but something hardcoded to a different connectionstring with a different user?

I would start profiler and filter it by that users hostname; see if he's generating multiple spids for multiple connections,a nd see if he is passing a query that is raising that missing column issue.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Dorian Gray SSC Rookie Points: 46 More actions · Answer 3

stupid not to have thought of the tags..will repost in a minute.

It is windows based...and no...just one connection string at the beginning stored in an ini file...

point noted about profiler...will start and see what yields...thanks...meanwhile, any other suggestions are welcome...

Steve Jones - SSC Editor SSC Guru Points: 736140 More actions · Answer 4

I'd run a SETUSER to switch to this user, and run the fn_my_permissions (or whatever it's called) to check permissions.

A DENY will override the other permissions.

I'd experiment a bit to be sure that the correct permissions are set from your end. Can you update a table, select from it, etc.

Dorian Gray SSC Rookie Points: 46 More actions · Answer 5

Thank you Lowell and Steve - for your response.

I set a trace using "TSQL_Grouped" template and ran the app ad infinitum; kept tweaking all sprocs that did not specify schema name (have recently upgraded from sql2k) - I got different error messages at different times - too lengthy to go into now;

Bottom line - tracked it down to an old dynamic "disable & enable trigger" in one of the sprocs - syntax has changed with sql2k5 - now it's just "disable trigger trg on dbo.table"; I also had to grant the user "Alter" permissions to be able to execute this trigger;

Having come so far - I'm now stuck on "enabling the trigger" - I keep getting a syntax error on "incorrect syntax near the keyword 'Trigger'" - my syntax is exactly the same as the "disable" syntax - i.e. "enable trigger dbo.trg on dbo.table" - if my googling yields anything, I will post it here. If anyone knows why I cannot enable a trigger...please share!

Dorian Gray SSC Rookie Points: 46 More actions · Answer 6

changed the syntax to include a ";" (semicolon) before the statements - apparently that's how they'll compile...still getting an error when the trigger is fired through the app...regardless, I can sense that the end is near...much progress has been made...and I can finally call it a day!