May 5, 2012 at 4:45 am
Hi All,
I am getting a strange behavior by TADOQuery component in Delphi7 on Windows XP and Windows7 operating system. Following issue works fine on Windows7 oprating system, but it gives an error on XP operating system.
Let me explain the schenario, I have one TADOConnection component and two TADOQuery components. The TADOConnection component is having the value for "ConnectionString" property as follows:
ADOConnection1.ConnectionString := 'Provider = MSDASQL.1; Password = My Password; User ID = My User ID; Data Source = My Data Source';
I have already applied the role/granted permission to the User ID/Password which I am login to the application.
I have connected TADOConnection component to the TADOQuery components using "Connection" property.
When I execute the first sql then it works fine without any error, but when I execute the second sql that time I am getting following error on Windows XP oprating system.
[Microsoft] [SQL Server Native Client 10.0] [SQL Server] The SELECT permission was denied on the object 'My Table Name', database 'My Database Name', schema 'dbo'
The same code if I execute on Windows7 oprating system it works fine without any error.
Could anybody put focus what could be missing or what could be the issue ?
------------------------------------------------------------------------------------------------------------------------
I have added "Persist Security Info = False;" to a connection string as follows:
ADOConnection1.ConnectionString := 'Provider = MSDASQL.1; Persist Security Info = False; Password = My Password; User ID = My User ID; Data Source = My Data Source';
Now, whenever I execute the sql using TADOQuery component, I need to write following statement every time, even if I have already set the "Connection" property of TADOQuery at the beginning, still I need to set this property whenever I execute the sql.
ADOQuery1.Connection := ADOConnection1;
But as I said, On Windows7 there is no need to change the "ConnectionString" property of TADOConnection component and no need to set the "Connection" property of any TADOQuery component.
Could anybody tell what exactly has to be done ?
If your application is too hugh and lot of places coding changes are required, would anybody agree with that ? And what about testing efforts ? At all the places testing has to be done, right ?
So, I am still in search of proper solution. The question is still unanswered.
If anybody is having any clue or hint or proper solution that would be highly appreciated.
Thanks In Advance.
With Best Regards.
May 7, 2012 at 9:20 am
That is a permissions issue. Do you have the same database username in both XP and Win7? The error message itself is pretty self explanatory.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 7, 2012 at 10:44 am
Hi Sean Lange,
I have already mention in the thread as follows:
When I execute the first sql then it works fine without any error, but when I execute the second sql that time I am getting following error on Windows XP oprating system.
[Microsoft] [SQL Server Native Client 10.0] [SQL Server] The SELECT permission was denied on the object 'My Table Name', database 'My Database Name', schema 'dbo'
With Best Regards.
Vishal
May 7, 2012 at 10:54 am
Well it seems that your connection is perfectly fine. What does the sql do? Does it revoke permissions? Does it create this table?
It seems that this is not a problem on the sql side of things but it is impossible to tell. There is very little information here to work with.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 8, 2012 at 7:13 am
Hi,
I have kept one TADOConnection component and 2 TADOQuery components and one TButton component at design time.
I have written the code at two places:
1. FormShow event:
Here I am making database connection to TADOConnection component and after that assigning all TADOQuery components to it.
2. Button click event:
Here I am first allpying the role to a perticular use and the tring to execute the sql, where it is failing to execute the sql.
Code is as follows:
--------------------------------------------------------------
procedure TForm1.FormShow(Sender: TObject);
var
iCount : Integer;
begin
ADOConnection1.Connected := False;
//SQL execution doesn't work with TADOQuery, following connection string is done using TADOConnection component, even if connection is successful with TADOConnection component
//but while applying the role there is no error.
ADOConnection1.ConnectionString := 'Provider=MSDASQL.1;User ID=My User ID'
+ ';Password=My password;Data Source=My data Source';
OR
//SQL execution doesn't work with TADOQuery, following connection string is done using TADOConnection component, even if connection is successful with TADOConnection component
//but while applying the role there is no error.
ADOConnection1.ConnectionString := 'Provider=MSDASQL.1;Persist Security Info=False;User ID=My User ID;Password=My password;'
+ 'Initial Catalog=My Database Name;Data Source= My data Source';
OR
//SQL execution doesn't work with TADOQuery, following connection string is done using TADOConnection component, even if connection is successful with TADOConnection component
//but while applying the role there is no error.
ADOConnection1.ConnectionString := 'Provider=SQLNCLI10.1;Persist Security Info=False;User ID=My User ID;Password=My password;'
+ 'Data Source=My data Source';
Only following connection string works for all query components, i.e. there is not error while executing the sql with any query component, but when I provide "Data Source=My data Source" it is not connecting at all using TADOConnection component.
ADOConnection1.ConnectionString := 'Provider=SQLNCLI10.1;Persist Security Info=False;User ID=My User ID;Password=My password;'
+ 'Initial Catalog=My Database Name;Data Source= 3.204.35.38,51722';
ADOConnection1.Connected := True;
ADOConnection1.BeginTrans;
for iCount := 0 to ComponentCount - 1 do
begin
if Components[iCount] is TADOQuery then
begin
TADOQuery(Components[iCount]).Connection := ADOConnection1;
end;
end;
ADOConnection1.CommitTrans;
end;
--------------------------------------------------------------
procedure TForm1.Button1Click(Sender: TObject);
begin
\\"test123" is the password
ADOQuery3.Close;
ADOQuery3.SQL.Clear;
ADOQuery3.SQL.Add('EXEC sp_setapprole ''APP_USER_APP'', ''test123''');
ADOQuery3.ExecSQL;
ADOQuery1.Close;
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('Select * from Table1');
ADOQuery1.Open; \\Getting error in this statement
ADOQuery2.Close;
ADOQuery2.SQL.Clear;
ADOQuery2.SQL.Add('Select * from Table1');
ADOQuery2.Open; \\Getting error in this statement
end;
--------------------------------------------------------------
With Best Regards.
Vishal
May 8, 2012 at 7:54 am
So does this app role have permission to select from that table?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 8, 2012 at 10:00 am
Yes, that's why first time there is no error.
With Best Regards.
Vishal
May 8, 2012 at 10:12 am
What happens if you run the following code in SSMS?
EXEC sp_setapprole 'APP_USER_APP', 'test123'
Select * from Table1
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 8, 2012 at 11:32 am
I don't get any error. Means role is set.
With Best Regards.
Vishal
May 8, 2012 at 11:35 am
vishualsoft (5/8/2012)
I don't get any error. Means role is set.With Best Regards.
Vishal
Yeah that means it is something in your code and not on the sql side. I suspect you will probably have better luck in a delphi forum since the error lies somewhere in there.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 8, 2012 at 11:55 am
vishualsoft (5/8/2012)
I don't get any error. Means role is set.With Best Regards.
Vishal
Another thought is that your connection is being close and therefore you are not on the same application role.
What if you changed you delphi code like this?
ADOQuery3.Close;
ADOQuery3.SQL.Clear;
ADOQuery3.SQL.Add('select 1 as Test into #MyTable');
ADOQuery3.ExecSQL;
ADOQuery1.Close;
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('Select * from #MyTable');
ADOQuery1.Open; \\still Getting error in this statement?? if so, what is the error now?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 14, 2012 at 10:19 am
Hi Sean Lange And Hi Dear All,
Hi Sean, I would execute the way you said and then I would come back to you, following thing I am writting before reading your above replay, so kindly sorry for it.
If I execute the following 2 lines of code before executing sql using any TADOQuery component then that time there is no error, could I get any clue what is the reason, when I EXECUTE FOLLOWING @ LINES OF CODE< WHY I don't get "SELECT permission..." error.
ADoConnection1.Connected := False;
ADoConnection1.Connected := True;
-----------------------------------------------------------------
procedure TForm1.Button1Click(Sender: TObject);
begin
\\"test123" is the password
ADOQuery3.Close;
ADOQuery3.SQL.Clear;
ADOQuery3.SQL.Add('EXEC sp_setapprole ''APP_USER_APP'', ''test123''');
ADOQuery3.ExecSQL;
ADoConnection1.Connected := False;
ADoConnection1.Connected := True;
ADOQuery1.Close;
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('Select * from Table1');
ADOQuery1.Open; \\No error in this statement
ADoConnection1.Connected := False;
ADoConnection1.Connected := True;
ADOQuery2.Close;
ADOQuery2.SQL.Clear;
ADOQuery2.SQL.Add('Select * from Table1');
ADOQuery2.Open; \\No error in this statement
end;
-----------------------------------------------------------------
Thanking You And With Best Regards.
Vishal
May 14, 2012 at 11:45 pm
Hi Sean Lange,
When I execute the following code you suggested, I get the same error saying "SELECT permission denied on the object....."
ADOQuery3.Close;
ADOQuery3.SQL.Clear;
ADOQuery3.SQL.Add('select 1 as Test into #MyTable');
ADOQuery3.ExecSQL; //Getting error when executing this statement
With Best Regards.
Vishal
May 15, 2012 at 7:29 am
That sounds to me then like your connection is getting closed and therefore losing your app role. Maybe you should try either not closing the query object in between or adding your call to sp_setapprole in addition to the rest of the sql?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 20, 2012 at 11:12 pm
Hi All,
I have got the starnge solutions for the strange problem. Till now it is working perfectly.
As I said, the SQL execution would not give error untill I use any second TADOQuery component. But the moment I use any other TADOQuery rather than first one, I get an
error like, "SELECT permision was denied on object <'Table Name'>......".
I have found two solutions:
I am using DSN as Datya Source, so even if I use SQL Sever or Oracle as a database, so there is no need to make much changes.
Solution1:
I have just assigned the TADOQuery No. 1 to any other TaDOQuery which I am going to use for SQL execution as below:
ADOConnection1.ConnectionString := 'Provider=MSDASQL.1;Persist Security Info=True;'
+ 'User ID= My User ID;Password= My Password;'
+ 'Data Source= My DSN Data Source Name';
ADOConnection1.Connected := True;
ADOQuery1.Connection := ADOConnection1;
ADOQuery2.Close;
ADOQuery2 := ADOQuery1; //Assigning ADOQuery1 to ADOQuery2
ADOQuery2.SQL.Clear;
ADOQuery2.SQL.Add('Select * from Table1');
ADOQuery2.Open; // OR ADOQuery2.Active := True;
In above sample code, there is no effect whether I use ADOQuery1 for execution for any time or not, i.e. for the first time or never, everything works fine.
So, whenever I need to use any other ADOQuery rather than ADOQuery1, I just assign the ADOQuery1 to the respective ADOQuery component before SQL execution i.e. before
assigning SQL to the respective TADOQuery component as below.
ADOQuery_N := ADOQuery1;
Solution 2:
I have used TClientDataSet and TDataSetProvider component with TADOQuery component as follows:
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('Select * from Table1');
DataSetProvider2.DataSet := ADOQuery1; //As It Is Required, Assigning ADOQuery1 to TDataSetProvider2
ClientDataSet2.SetProvider(DataSetProvider2);
ClientDataSet2.Active := True;
ClientDataSet2.First;
for iCount := 0 to ClientDataSet2.RecordCount - 1 do
begin
ADOQuery1.Close;
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('Select * from Table2 where Column_Name = ' + QuotedStr(ClientDataSet2.FieldByName('Column_N_Name').AsString));
DataSetProvider3.DataSet := ADOQuery1; //As It Is Required, Assigning ADOQuery1 to TDataSetProvider3
ClientDataSet3.SetProvider(DataSetProvider3);
ClientDataSet3.Active := True;
//Workout with ClientDataset3 in your code, then clear the data in it as shown below.
//So, in the loop we could workout freshly with ClientDataSet3.
ClientDataSet3.DisableControls;
ClientDataSet3.EmptyDataSet;
ClientDataSet3.EnableControls;
ClientDataSet2.Next;
end;
Kindly Let me know, if there is anything to say from your side.
With Best Regards.
Vishal
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply