Re: One Web Service updates SQL, another can't?

From: Steve Ricketts (steve_at_velocedge.com)
Date: 10/26/04 

Date: Tue, 26 Oct 2004 13:43:06 -0400

I set the trace for the problem database and am receiving exactly what I
sent. (I'm using sa just to eliminate as much of the permissions problems
as possible). I'm stepping through the Visual Studio .Net debugger and it's
showing me the returned data record when its a "select" but as soon as an
"update" is issued, I get "Operation is not allowed when the object is
closed". The SQL Trace shows:

update cmi set revised = '10/26/2004 12:32:30 PM' where person_int = 23720
and course_int = 645 and lesson_int = 2

Which is what I sent from the web service... and why would this work if it's
the first Web Service started and the other Service fails. It doesn't seem
to matter which database I use, the one I start first works and the second
one doesn't.

Steve

"Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
news:eMxsH%233uEHA.1260@TK2MSFTNGP12.phx.gbl...
> If it's connecting as sa - which, BTW, is a bad thing - then the security
is
> essentially bypassed. The next thing I'd do is run a profiler trace and
try
> and see what's coming at your server. Hopefully, you can run the web
> service in debug mode to step through it.
>
> --
> Tom
>
> ---------------------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
>
> "Steve Ricketts" <steve@velocedge.com> wrote in message
> news:uw%23Fp63uEHA.2172@TK2MSFTNGP14.phx.gbl...
> I'm sending raw SQL and should have told you that I was opening the
database
> as "sa". db_denydatawriter for sa is not checked. Does that help?
>
> sr
>
>
>
> "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
> news:uichWz3uEHA.2520@TK2MSFTNGP15.phx.gbl...
> > Assuming that you're using raw T-SQL - not stored procs - I'd look at
who
> is
> > a member of the db_denydatawriter role in the problem database. Another
> > thing to look at is the permissions for that user in EM. That may tell
> you
> > if there are any explicit DENY's.
> >
> > --
> > Tom
> >
> > ---------------------------------------------------------------
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Columnist, SQL Server Professional
> > Toronto, ON Canada
> > www.pinnaclepublishing.com
> >
> >
> > "Steve Ricketts" <steve@velocedge.com> wrote in message
> > news:eZ5PcW3uEHA.3828@TK2MSFTNGP12.phx.gbl...
> > I have a production and development system both running on one PC under
> W2K
> > with SQL7sp4. One Web Service can access and update the SQL Server 7
> > database fine. Another instance of the same Web Service code, accessing
a
> > different, but identical database, can read but not update records. It
> > seems like whichever Web Service I start first is allowed to read and
> write,
> > but the other has only read permissions.
> >
> > Is there a exclusive, read-only, permissions setting that I've missed
> > somewhere? The problem is simply the second Web Service can't write to
> SQL,
> > but why is the big question. Any help would be greatly appreciated,
I'm
> > into days on this one!
> >
> > Thanks,
> >
> > Steve Ricketts
> >
> >
>
>

Relevant Pages

  • REPOST: One Web Service updates SQL, the other cant
    ... I posted this on .sqlserver.security under the same subject and Tom Moreau ... One Web Service can access and update the database via ADO ... Columnist, SQL Server Professional ...
    (microsoft.public.vb.database.ado)
  • ADP, Application Role, and objects
    ... The above link is to an atricle on how to implement SQL Server Application ... After you connect with your ADP, fire a bit of code to set the ... third party tools to view the data on the same database. ... Scenario 1 - If I explicitly grant permissions on that object to the user ...
    (microsoft.public.access.adp.sqlserver)
  • Re: REPOST: One Web Service updates SQL, the other cant
    ... One Web Service can access and update the database via ADO ... > possible using the following connection string: ... > Columnist, SQL Server Professional ...
    (microsoft.public.vb.database.ado)
  • Re: One Web Service updates SQL, another cant?
    ... I guess its because this is my first Web Service and I ... it opens the database connection. ... > Columnist, SQL Server Professional ...
    (microsoft.public.sqlserver.security)
  • Re: Execute Persmission denied on object sp_OACreate
    ... > SQL Server is creating a job behind the scenes. ... > permissions. ... > SA account password and gaining access to the database. ... >>> How can get a user permissions to execute these stored procedures ...
    (microsoft.public.sqlserver.security)