SQL Adding a User account: user

Showing posts with label user. Show all posts

Sunday, March 25, 2012

advice about a worm intrusion alert

XP Home, limited user account. Newbie to this group - I know next to
nothing about ports but am an experienced computer user otherwise.
Can anyone interpret this for me - just started to get these recently -
this is only the second one. Got it while using a user account in my XP
Home machine.
Security Alert - Medium Rick
Norton Internet Worm Protection has detected and blocked an intrusion
attempt.
The text in More Info was as follows:
Intrusion: MS SQL PacketResolution DoS
Intruder: 192.168.1.1 (domain(53))
Risk Level: Medium.
Protocol: UDP
Attacked IP: COMPUTER NAME (192.168.1.2)
Attacked Port: ms-sql-m(1434)
The Intruder address was my router, to which one Win98SE computer is
connected by ethernet (not mentioned in report) and the other on the
192.168.1.2 address is my XP Home machine, wirelessly connected to the
router.
I clicked OK and then the wireless connection lost its IP and
connectivity - and I had no internet access on the wireless XP machine.
Router was still connected to internet fine - all lights glowing properly.
Computer upstairs 192.168.1.3 was on and could connect to the internet -
no one was using it at the time of the alert. It has Zone Alarm free
version to prevent any outgoing stuff, and also NAV and Spybot S&D
resident (teatimer). It is on Win98SE. No alerts showing.
This machine runs Windows XP Home (user account) has NAV, Counterspy and
Zone Alarm free. Wireless network is WPA-PSK with 63 character pw.
Log off and on did not restore the wireless (always does usually).
Log off and then on to Admin acct - again wireless network did not work
but I got a windows error - windows is recovering from a serious error.
Still no connection.
Did a warm reboot and then everything was back to normal.
I do a Norton AV and Counterspy scan daily. Clear.
I think all the Windows/wireless hassle was due to the Norton blocking
the request, and I think the "intrusion" was legitimate - but I don't
want to "allow" it unless someone can explain the details to me. Many
thanks to any network gurus who can interpret please.
Rev Robert M Jones, Wimborne Baptist Church, UK
http://www.wimborne-baptist.org.uk
Free trial of Mailwasher Pro - effective email spam filter - (commission
goes to our partners in Bulgaria)
http://fta.firetrust.com/index.cgi?id=420Port 1434 is the SQL Browser service used for locating SQL Servers.
I would NOT allow Ports 1434 or 1433 to be open to the outside.
Is this a NAT router directly connected to your DSL/Cable modem?
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Robert M Jones" <robert53newsgroups-ms2@.NOSPAMyahoo.co.uk> wrote in message
news:uTWSOL$DHHA.4620@.TK2MSFTNGP04.phx.gbl...
> XP Home, limited user account. Newbie to this group - I know next to
> nothing about ports but am an experienced computer user otherwise.
> Can anyone interpret this for me - just started to get these recently -
> this is only the second one. Got it while using a user account in my XP
> Home machine.
> Security Alert - Medium Rick
> Norton Internet Worm Protection has detected and blocked an intrusion
> attempt.
> The text in More Info was as follows:
> Intrusion: MS SQL PacketResolution DoS
> Intruder: 192.168.1.1 (domain(53))
> Risk Level: Medium.
> Protocol: UDP
> Attacked IP: COMPUTER NAME (192.168.1.2)
> Attacked Port: ms-sql-m(1434)
> The Intruder address was my router, to which one Win98SE computer is
> connected by ethernet (not mentioned in report) and the other on the
> 192.168.1.2 address is my XP Home machine, wirelessly connected to the
> router.
> I clicked OK and then the wireless connection lost its IP and
> connectivity - and I had no internet access on the wireless XP machine.
> Router was still connected to internet fine - all lights glowing properly.
> Computer upstairs 192.168.1.3 was on and could connect to the internet -
> no one was using it at the time of the alert. It has Zone Alarm free
> version to prevent any outgoing stuff, and also NAV and Spybot S&D
> resident (teatimer). It is on Win98SE. No alerts showing.
> This machine runs Windows XP Home (user account) has NAV, Counterspy and
> Zone Alarm free. Wireless network is WPA-PSK with 63 character pw.
> Log off and on did not restore the wireless (always does usually).
> Log off and then on to Admin acct - again wireless network did not work
> but I got a windows error - windows is recovering from a serious error.
> Still no connection.
> Did a warm reboot and then everything was back to normal.
> I do a Norton AV and Counterspy scan daily. Clear.
> I think all the Windows/wireless hassle was due to the Norton blocking the
> request, and I think the "intrusion" was legitimate - but I don't want to
> "allow" it unless someone can explain the details to me. Many thanks to
> any network gurus who can interpret please.
> --
> Rev Robert M Jones, Wimborne Baptist Church, UK
> http://www.wimborne-baptist.org.uk
> Free trial of Mailwasher Pro - effective email spam filter - (commission
> goes to our partners in Bulgaria)
> http://fta.firetrust.com/index.cgi?id=420|||Arnie Rowland wrote:
> Port 1434 is the SQL Browser service used for locating SQL Servers.
> I would NOT allow Ports 1434 or 1433 to be open to the outside.
> Is this a NAT router directly connected to your DSL/Cable modem?
>
Thanks for the reply. This is all a mystery to me.
Set up is an ADSL Router with NATS firewall incorporated. I have Skype
if that is relevant - the entry for that against its icon in Zone Alarm
is "Listening to Port(s) TCP:80,443,14695"
The router is set with IP Filtering enabled, for filtering inbound
traffic - there are no entries in the table in that section.
The section on Virtual Server Configuration DMZ host has:
"Those IP packets from the Internet that do NOT belong to any
applications configured in the port forwarding table will be: Discarded"
There is nothing set up in the port forwarding section
Any more checking I should do? The router NATS seems to do its job in
terms of the Shields Up tests, but I haven't then disabled the NATS to
test the actual ZA software firewall on the machine itself.
Rev Robert M Jones, Wimborne Baptist Church, UK
http://www.wimborne-baptist.org.uk
Free trial of Mailwasher Pro - effective email spam filter - (commission
goes to our partners in Bulgaria)
http://fta.firetrust.com/index.cgi?id=420|||>>> On 11/24/2006 at 11:03 AM, in message
<uTWSOL$DHHA.4620@.TK2MSFTNGP04.phx.gbl>, Robert M
Jones<robert53newsgroups-ms2@.NOSPAMyahoo.co.uk> wrote:
> Security Alert - Medium Rick
> Norton Internet Worm Protection has detected and blocked an
> intrusion
> attempt.
> The text in More Info was as follows:
> Intrusion: MS SQL PacketResolution DoS
> Intruder: 192.168.1.1 (domain(53))
> Risk Level: Medium.
> Protocol: UDP
> Attacked IP: COMPUTER NAME (192.168.1.2)
> Attacked Port: ms-sql-m(1434)
Do you even have SQL installed on your machine? My guess is that you
don't.
As a result, port 1434 is not used by any specific program, but is
available for any program that needs a new UDP port to use.
Because of this, the DNS resolver is using it to make a DNS request
(your name server is probably set as 192.168.1.1). Your DNS server
responds to port 1434. However, Norton incorrectly classifies this as
an attack. It probably isn't.|||Joel Maslak wrote:
> <uTWSOL$DHHA.4620@.TK2MSFTNGP04.phx.gbl>, Robert M
> Jones<robert53newsgroups-ms2@.NOSPAMyahoo.co.uk> wrote:
> Do you even have SQL installed on your machine? My guess is that you
> don't.
> As a result, port 1434 is not used by any specific program, but is
> available for any program that needs a new UDP port to use.
> Because of this, the DNS resolver is using it to make a DNS request
> (your name server is probably set as 192.168.1.1). Your DNS server
> responds to port 1434. However, Norton incorrectly classifies this as
> an attack. It probably isn't.
That's sort of what I thought - but until I can be sure I did not want
to give Norton any instructions to allow or remember - just saying "ok"
when I get the block message.
Any advice on checking I can do (other than routine AV and spyware
scans) most welcome.
Rev Robert M Jones, Wimborne Baptist Church, UK
http://www.wimborne-baptist.org.uk
Free trial of Mailwasher Pro - effective email spam filter - (commission
goes to our partners in Bulgaria)
http://fta.firetrust.com/index.cgi?id=420sql

Thursday, March 22, 2012

AdventureWorks

I've download and installed the sample database but the Server can't see it .. it doesn't appear in the User databases. I installed using the defaults. Anyone ha d the same problem and solved it?

Downloadable user database or not installed upon installation. the datafiles /scripts are just copied to the specified directory. if you want to see them as server attached databases, you will have to either go through the GUI and attach them right-clicking on the Databases node and Select attach database, fo through the GUI and specify the path and file name of the database you want to attach. Another option would be to use the sp_attachdb command from TSQL (see more information about that in the BOL)

Jens K. Suessmeyer

http://www.sqlserver2005.de

Tuesday, March 20, 2012

Advatnages and DisAdvantages of

Running SQL Server as a DOMAIN user account.It is a single, two-edged sword. If SQL Server authenticates to the domain, it can have access to the resources of the domain. That is both a blessing and a curse.

It means that (given the proper permissions), the SQL Server can "see" other resources such as disk, printers, etc. The server can then send mail and other forms of messages (that rely on domain authentication).

In general, I usually have one "intereface" server that uses a domain account, but has no end user connections. It does all of the "cross server" work for the whole farm. The other servers use Local System unless some particular reason forces another choice.

-PatP|||Search for MS Best Practices on SQL Server and SQL Agent service accounts.|||with a domain user account the sqlserver and sql server agent accounts can access the local machine and can be audited through the os.

the mssqlserver service can communicate more efficiently with other servers that are domain members.

you can use sqlmail directly(no workarounds) because you will have an exchange mailbox created for your mssqlserver user account.

you can make the accounts [domain users] but give them admin rights on the local sql server machine to control access and use.

you can avoid having to create a cmdexec proxy account for running activex scripts ..

the accounts allow you the ability to perform active directory delegeation and impersonation.

the domain accounts provide for mutal authentication services through kerberos in active directory.sql

Monday, March 19, 2012

Advanced SELECT for a newbie

I have a table full of Latitudes, Longitudes, address, customername, etc. , I need to grab some input(Latitude, Longitude, range) from the user. So now I have a source lat, long(user) and destination lat, long(rows in dbase). I need to take the 2 points and compute a distance from the user given lat, long to every lat, long in the database and check that distance againt the range given from the user. If the distance is below the range, I need to put that row into a temp table and return the temp table at the end of the stored proc.

As of right now I am completely lost and need some guidance.

I would also like to be able to add the computed distance to a table. Here is the function and stored procedure i have so far...

ALTER PROCEDURE [dbo].[sp_getDistance]

@.srcLat numeric(18,6),
@.srcLong numeric(18,6),
@.range int
AS
BEGIN
SET NOCOUNT ON;

SELECT * FROM dbo.PL_CustomerGeoCode cg
WHERE dbo.fn_computeDistance(@.srcLat, cg.geocodeLat, @.srcLong, cg.geocodeLong) < @.range

END

CREATE FUNCTION fn_computeDistance
(
-- Add the parameters for the function here
@.lat1 numeric(18,6),
@.lat2 numeric(18,6),
@.long1 numeric(18,6),
@.long2 numeric(18,6)
)
RETURNS numeric(18,6)
AS
BEGIN
-- Declare the return variable here
DECLARE @.dist numeric(18,6)

IF ((@.lat1 = @.lat2) AND (@.long1 = @.long2))
SELECT @.dist = 0.0
ELSE
IF (((sin(@.lat1)*sin(@.lat2))+(cos(@.lat1)*cos(@.lat2)*cos(@.long1-@.long2)))) > 1.0
SELECT @.dist = 3963.1*acos(1.0)
ELSE
SELECT @.dist = 3963.1*acos((sin(@.lat1)*sin(@.lat2))+(cos(@.lat1)*cos(@.lat2)*cos(@.long1-@.long2)))

-- Return the result of the function
RETURN @.dist

Thanks,

Kyle

What's the problem you're facing? If you want to add a computed column for the distance to the table, you can use something like:

ALTER TABLE dbo.PL_CustomerGeoCode ADD ComputedDistance AS dbo.fn_computeDistance(@.srcLat, cg.geocodeLat, @.srcLong, cg.geocodeLong)

Thursday, March 8, 2012

ADS user and sql 2005

I wish to use something other than sql's SA account user to connect to
my data warehouse, so I created a user in our active directory user.
Ill use dw as the new user as example.
after I created the user, dw, in ADS, I added the user via Management
Studio in SecurityLogins.
I grant ower of ads\dw to my datawarehouse.
I try to connect to the database engine using SQL Servier
Authentication, Login: ads\dw.
I get Cannot connect to xxxx, Login failed for user 'ads\dw' (Microsoft
SQL Server, Error: 18456).
Next, I add this user to the local server's administrators group (the
server is in admin mode) and login.
Now I can connect to the database as user dw. ( i suspect the users
memebership of administrator is the reason).
I dont wish to have the dw user part of administrator, but I want it to
have control over just the datawarehouse database.
What am I doing wroing?
TIA
RobMore Info:
I checked the server log and the error is state 6. I found a blog on
MSN and it says state 6 is 'Attempt to use a Windows login name with
SQL Authentication'.
Right, exactly what I thought I wanted to do.
I thought that when I added a windows user to a sql servers security
and login, that windows user can access the sql server??|||You need to give this user explicit credentials, typically make hime a
member of a role which has the right you need.

In SQL 2000 you do this under security. It's quite simple.

Regards,
Henrik

*** Sent via Developersdex http://www.developersdex.com ***|||rcamarda (robc390@.hotmail.com) writes:

Quote:

Originally Posted by

Mixing apples and oranges, I see. To log into SQL Server as ADS\dw,
you need to be logged into Windows as ADS\dw. That's what integrated
security is all about. By already being authenticated by Windows,
there is no need for SQL Server to authenticate you again. But you
cannot log into SQL Server with another Windows login than the one
you are logged into Windows with. You can only log into SQL Server
with an explicit username/password with an SQL login.

Quote:

Originally Posted by

Next, I add this user to the local server's administrators group (the
server is in admin mode) and login.

And dw now has sysadmin rights in the server, unless you remove
BUILTIN\Administrators.

Quote:

Originally Posted by

Now I can connect to the database as user dw. ( i suspect the users
memebership of administrator is the reason).
I dont wish to have the dw user part of administrator, but I want it to
have control over just the datawarehouse database.
What am I doing wroing?

First descide whether it's a Windows login or an SQL Login you want.
Next grant this user access to the server and database. Next you grant
him CONTROL on the database. (You are on SQL 2005, right?)

--
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|||Erland,
Yes I am on sql 2005. I am using Cognos' ReportNet (Now Cognos8) to
connect to its Content Store, a database. I need to provide a user and
password. I thought I would set up a user on ADS and provide the
account and password.
Out of confusion/frustration/ignorance I created a local user within
SQL server and it works just fine.
(I have several SQL servers for the database, and I thought using ADS
for user logins and authentication would be better).
So, was my problem more to do with trying to connect as another windows
user with the SQL Management tool? (I did not try to configure Cognos
since I could not connect via the SQL Studio)
Thanks for your help and any other pointers
Rob

ADP connection to SQL

I would like any advice on connecting an Access Project to SQL Server 2000. Authentication is in mixed mode and user enter a name and password. When he click cancel twice, it will appear error - he enter without check a name and password. How I can prevent that?Any chance of changing the authentication to Windows only?|||

I don't understand what is happening in your scenario - can you explain with more details how you set up your project to connect to SQL Server?

Thanks
Laurentiu

ADP connection to SQL

I don't understand what is happening in your scenario - can you explain with more details how you set up your project to connect to SQL Server?

Thanks
Laurentiu

Tuesday, March 6, 2012

Adp And Sql Server 2005

We upgraded SQL Server 2000 to 2005 We have Access 2003 as the user interface. We are having issues with permission now and when I go into the ADP to create a function or a Stored Procedure it tells me that "the version of Microsoft Office Access doesnt support design changes with the version of Microsoft SqL Server your access projet is connected to. See the Mircrosoft Office Update Web site for the latest information and downloas (on the Help menu, click office on the Web). your design changes will not be saved".

And then when I get inside the design view, it says CAUTION: NEW DATABASE FEATURES NOT SUPPORTED. "You have connected to a version of SQL Server later than SQL Server 2000. The version of Visual Studio or Access that you are using was released before the version of SQL Server to which you are connected. For this reason, you might encounter problems.

Please check with Microsoft to see if there is a service pack that you should apply to Visual Studio or Office in order to get support for the version of SQL Server to which you are connected.

You can continue but any new object types might not be enumerated, and it will not be possible to save any objects or database diagrams that you create using the Visual Database Tools."

Can anyone help pleasewell have you checked for an access 2003 service pack? that is where i would start. otherwise you might have to abandon the Access 2003 crutch and do your development directly in SQL Server <gasp>.|||Thank you I dont mind going through development directly its just that before I at least had the option available, I use to do both but now looks like I cant.|||You may be running into the issue described at the end of the quote in post 6:

http://www.dbforums.com/showthread.php?t=1620139

Sunday, February 19, 2012

ADO query restricting to SELECT

Hi,
Can I restrict INSERT, DELETE, UPDATE, INTO queries in ADO? I get the
query as input from user. Run it using ADO connection. Display the result in
grid. However I want to allow only SELECT queries. Is there a property on
ADO which allows this? Otherwise I will have to do the parsing myself.
Thanks in advance.
AjeyHi
You will need to restrict the access with permissions at table level. If you
allow them to write their own queries instead of using a controlled query
builder or stored procedures then you will need to do your own parsing.
You should also read up on SQL Injection such as
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
John
"Ajey" wrote:
> Hi,
> Can I restrict INSERT, DELETE, UPDATE, INTO queries in ADO? I get the
> query as input from user. Run it using ADO connection. Display the result in
> grid. However I want to allow only SELECT queries. Is there a property on
> ADO which allows this? Otherwise I will have to do the parsing myself.
> Thanks in advance.
> Ajey
>
>|||Hi,
By the post I understand that, u have a layee between the database and the
user.
The user need to send a select query and the result is displayed in the Grid.
The users are presently having a flexibility to send the any kind of query.
If they send INSERT, UPDATE or DELETE, your data will be currupted, and you
wante to restrict that.
If my prediction was correct, what I suggest you is, to use a Stored
Procedure for this purpose or open the ADO with read only permissions.
I believe this answered your question. please revert back if u have any issues
thanks and regards
Chandra
"Ajey" wrote:
> Hi,
> Can I restrict INSERT, DELETE, UPDATE, INTO queries in ADO? I get the
> query as input from user. Run it using ADO connection. Display the result in
> grid. However I want to allow only SELECT queries. Is there a property on
> ADO which allows this? Otherwise I will have to do the parsing myself.
> Thanks in advance.
> Ajey
>
>|||Thanks.
Yes, that's what i want to do.
And also I want to prevent sql-injection. Thanks John.
I want to restrict the query to SELECT. I even if the user has permissions
to modify the table I don't want him to alter it through the query.
How can I open ADO with read only permissons?
Thanks in advance.
Ajey
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:CF97CE16-EFCF-4212-811C-A8C95E28D03E@.microsoft.com...
> Hi,
> By the post I understand that, u have a layee between the database and the
> user.
> The user need to send a select query and the result is displayed in the
Grid.
> The users are presently having a flexibility to send the any kind of
query.
> If they send INSERT, UPDATE or DELETE, your data will be currupted, and
you
> wante to restrict that.
> If my prediction was correct, what I suggest you is, to use a Stored
> Procedure for this purpose or open the ADO with read only permissions.
> I believe this answered your question. please revert back if u have any
issues
> thanks and regards
> Chandra
>
> "Ajey" wrote:
> > Hi,
> > Can I restrict INSERT, DELETE, UPDATE, INTO queries in ADO? I get
the
> > query as input from user. Run it using ADO connection. Display the
result in
> > grid. However I want to allow only SELECT queries. Is there a property
on
> > ADO which allows this? Otherwise I will have to do the parsing myself.
> >
> > Thanks in advance.
> > Ajey
> >
> >
> >
> >|||"Ajey" <ajey5@.hotmail.com> wrote in message
news:OfmIdX8SFHA.2560@.TK2MSFTNGP09.phx.gbl...
> Thanks.
> Yes, that's what i want to do.
> And also I want to prevent sql-injection. Thanks John.
> I want to restrict the query to SELECT. I even if the user has permissions
> to modify the table I don't want him to alter it through the query.
> How can I open ADO with read only permissons?
1. Set the Mode property of the underlying connection to adModeRead, and
2. Set the recordset.LockType property to adLockReadOnly
-Mark
> Thanks in advance.
> Ajey
> "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> news:CF97CE16-EFCF-4212-811C-A8C95E28D03E@.microsoft.com...
>> Hi,
>> By the post I understand that, u have a layee between the database and
>> the
>> user.
>> The user need to send a select query and the result is displayed in the
> Grid.
>> The users are presently having a flexibility to send the any kind of
> query.
>> If they send INSERT, UPDATE or DELETE, your data will be currupted, and
> you
>> wante to restrict that.
>> If my prediction was correct, what I suggest you is, to use a Stored
>> Procedure for this purpose or open the ADO with read only permissions.
>> I believe this answered your question. please revert back if u have any
> issues
>> thanks and regards
>> Chandra
>>
>> "Ajey" wrote:
>> > Hi,
>> > Can I restrict INSERT, DELETE, UPDATE, INTO queries in ADO? I get
> the
>> > query as input from user. Run it using ADO connection. Display the
> result in
>> > grid. However I want to allow only SELECT queries. Is there a property
> on
>> > ADO which allows this? Otherwise I will have to do the parsing myself.
>> >
>> > Thanks in advance.
>> > Ajey
>> >
>> >
>> >
>> >
>|||You can do this while you are opening the connection itself.
For More info refer to:
http://www.codeguru.com/vb/gen/vb_database/adonet/article.php/c5153/
"Ajey" wrote:
> Thanks.
> Yes, that's what i want to do.
> And also I want to prevent sql-injection. Thanks John.
> I want to restrict the query to SELECT. I even if the user has permissions
> to modify the table I don't want him to alter it through the query.
> How can I open ADO with read only permissons?
> Thanks in advance.
> Ajey
> "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> news:CF97CE16-EFCF-4212-811C-A8C95E28D03E@.microsoft.com...
> > Hi,
> > By the post I understand that, u have a layee between the database and the
> > user.
> > The user need to send a select query and the result is displayed in the
> Grid.
> >
> > The users are presently having a flexibility to send the any kind of
> query.
> > If they send INSERT, UPDATE or DELETE, your data will be currupted, and
> you
> > wante to restrict that.
> >
> > If my prediction was correct, what I suggest you is, to use a Stored
> > Procedure for this purpose or open the ADO with read only permissions.
> >
> > I believe this answered your question. please revert back if u have any
> issues
> >
> > thanks and regards
> > Chandra
> >
> >
> >
> > "Ajey" wrote:
> >
> > > Hi,
> > > Can I restrict INSERT, DELETE, UPDATE, INTO queries in ADO? I get
> the
> > > query as input from user. Run it using ADO connection. Display the
> result in
> > > grid. However I want to allow only SELECT queries. Is there a property
> on
> > > ADO which allows this? Otherwise I will have to do the parsing myself.
> > >
> > > Thanks in advance.
> > > Ajey
> > >
> > >
> > >
> > >
>
>|||It's not working:
Following is the sample code:
----
--
g_objConn.Mode = adModeRead
g_objConn.Open strConn, , , 0
Debug.Print g_objConn.Mode
strQuery = "select * from sysobjects select * from sysindexes insert
into testtable values ('xyz')"
Set objRS = New Recordset
'Set objRS = g_objConn.Execute(strQuery)
objRS.Open strQuery, g_objConn, adOpenForwardOnly, adLockReadOnly,
adCmdText
----
--
After the Open on recordset the insert is always successful.
Thanks.
Ajey
"Mark J. McGinty" <mmcginty@.spamfromyou.com> wrote in message
news:OWodsc8SFHA.3980@.TK2MSFTNGP12.phx.gbl...
> "Ajey" <ajey5@.hotmail.com> wrote in message
> news:OfmIdX8SFHA.2560@.TK2MSFTNGP09.phx.gbl...
> > Thanks.
> > Yes, that's what i want to do.
> > And also I want to prevent sql-injection. Thanks John.
> > I want to restrict the query to SELECT. I even if the user has
permissions
> > to modify the table I don't want him to alter it through the query.
> >
> > How can I open ADO with read only permissons?
> 1. Set the Mode property of the underlying connection to adModeRead, and
> 2. Set the recordset.LockType property to adLockReadOnly
>
> -Mark
>
>
> > Thanks in advance.
> > Ajey
> >
> > "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> > news:CF97CE16-EFCF-4212-811C-A8C95E28D03E@.microsoft.com...
> >> Hi,
> >> By the post I understand that, u have a layee between the database and
> >> the
> >> user.
> >> The user need to send a select query and the result is displayed in the
> > Grid.
> >>
> >> The users are presently having a flexibility to send the any kind of
> > query.
> >> If they send INSERT, UPDATE or DELETE, your data will be currupted, and
> > you
> >> wante to restrict that.
> >>
> >> If my prediction was correct, what I suggest you is, to use a Stored
> >> Procedure for this purpose or open the ADO with read only permissions.
> >>
> >> I believe this answered your question. please revert back if u have any
> > issues
> >>
> >> thanks and regards
> >> Chandra
> >>
> >>
> >>
> >> "Ajey" wrote:
> >>
> >> > Hi,
> >> > Can I restrict INSERT, DELETE, UPDATE, INTO queries in ADO? I
get
> > the
> >> > query as input from user. Run it using ADO connection. Display the
> > result in
> >> > grid. However I want to allow only SELECT queries. Is there a
property
> > on
> >> > ADO which allows this? Otherwise I will have to do the parsing
myself.
> >> >
> >> > Thanks in advance.
> >> > Ajey
> >> >
> >> >
> >> >
> >> >
> >
> >
>|||I am not using ADO.NET but it's a simple ADO application.
Thanks.
Ajey
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:F419CECC-2DF9-4C52-953F-CEA0334B0336@.microsoft.com...
> You can do this while you are opening the connection itself.
> For More info refer to:
> http://www.codeguru.com/vb/gen/vb_database/adonet/article.php/c5153/
>
>
> "Ajey" wrote:
> > Thanks.
> > Yes, that's what i want to do.
> > And also I want to prevent sql-injection. Thanks John.
> > I want to restrict the query to SELECT. I even if the user has
permissions
> > to modify the table I don't want him to alter it through the query.
> >
> > How can I open ADO with read only permissons?
> >
> > Thanks in advance.
> > Ajey
> >
> > "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> > news:CF97CE16-EFCF-4212-811C-A8C95E28D03E@.microsoft.com...
> > > Hi,
> > > By the post I understand that, u have a layee between the database and
the
> > > user.
> > > The user need to send a select query and the result is displayed in
the
> > Grid.
> > >
> > > The users are presently having a flexibility to send the any kind of
> > query.
> > > If they send INSERT, UPDATE or DELETE, your data will be currupted,
and
> > you
> > > wante to restrict that.
> > >
> > > If my prediction was correct, what I suggest you is, to use a Stored
> > > Procedure for this purpose or open the ADO with read only permissions.
> > >
> > > I believe this answered your question. please revert back if u have
any
> > issues
> > >
> > > thanks and regards
> > > Chandra
> > >
> > >
> > >
> > > "Ajey" wrote:
> > >
> > > > Hi,
> > > > Can I restrict INSERT, DELETE, UPDATE, INTO queries in ADO? I
get
> > the
> > > > query as input from user. Run it using ADO connection. Display the
> > result in
> > > > grid. However I want to allow only SELECT queries. Is there a
property
> > on
> > > > ADO which allows this? Otherwise I will have to do the parsing
myself.
> > > >
> > > > Thanks in advance.
> > > > Ajey
> > > >
> > > >
> > > >
> > > >
> >
> >
> >|||Hi Ajey
Will this be of any help:
===========
Set conn = New ADODB.Connection
conn.Open "dns=<>"
Dim rs As ADODB.Recordset
' Open the table.
Set rs = New ADODB.Recordset
rs.Open Query, conn, adOpenDynamic, adLockReadOnly
===========
thanks and regards
Chandar
"Ajey" wrote:
> I am not using ADO.NET but it's a simple ADO application.
> Thanks.
> Ajey
> "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> news:F419CECC-2DF9-4C52-953F-CEA0334B0336@.microsoft.com...
> > You can do this while you are opening the connection itself.
> >
> > For More info refer to:
> > http://www.codeguru.com/vb/gen/vb_database/adonet/article.php/c5153/
> >
> >
> >
> >
> > "Ajey" wrote:
> >
> > > Thanks.
> > > Yes, that's what i want to do.
> > > And also I want to prevent sql-injection. Thanks John.
> > > I want to restrict the query to SELECT. I even if the user has
> permissions
> > > to modify the table I don't want him to alter it through the query.
> > >
> > > How can I open ADO with read only permissons?
> > >
> > > Thanks in advance.
> > > Ajey
> > >
> > > "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> > > news:CF97CE16-EFCF-4212-811C-A8C95E28D03E@.microsoft.com...
> > > > Hi,
> > > > By the post I understand that, u have a layee between the database and
> the
> > > > user.
> > > > The user need to send a select query and the result is displayed in
> the
> > > Grid.
> > > >
> > > > The users are presently having a flexibility to send the any kind of
> > > query.
> > > > If they send INSERT, UPDATE or DELETE, your data will be currupted,
> and
> > > you
> > > > wante to restrict that.
> > > >
> > > > If my prediction was correct, what I suggest you is, to use a Stored
> > > > Procedure for this purpose or open the ADO with read only permissions.
> > > >
> > > > I believe this answered your question. please revert back if u have
> any
> > > issues
> > > >
> > > > thanks and regards
> > > > Chandra
> > > >
> > > >
> > > >
> > > > "Ajey" wrote:
> > > >
> > > > > Hi,
> > > > > Can I restrict INSERT, DELETE, UPDATE, INTO queries in ADO? I
> get
> > > the
> > > > > query as input from user. Run it using ADO connection. Display the
> > > result in
> > > > > grid. However I want to allow only SELECT queries. Is there a
> property
> > > on
> > > > > ADO which allows this? Otherwise I will have to do the parsing
> myself.
> > > > >
> > > > > Thanks in advance.
> > > > > Ajey
> > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>|||This seems to be doing the trick. I get following error when I try to
execute multiple queries:
sp_cursoropen/sp_cursorprepare: The statement parameter can only be a single
select or a single stored procedure. : Microsoft OLE DB Provider for SQL
Server
But I don't want to use adOpenDynamic but adOpenForwardOnly cursor type.
At least this now blocks the user from executing multiple statement and so i
can check only the first token to be SELECT.
But can I achive same using adOpenForwardOnly cursor type.
Thanks.
Ajey
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:34579EEE-DFDA-4768-9CD8-B5AAFEBE66B3@.microsoft.com...
> Hi Ajey
> Will this be of any help:
> ===========> Set conn = New ADODB.Connection
> conn.Open "dns=<>"
>
> Dim rs As ADODB.Recordset
> ' Open the table.
> Set rs = New ADODB.Recordset
> rs.Open Query, conn, adOpenDynamic, adLockReadOnly
> ===========> thanks and regards
> Chandar
>
> "Ajey" wrote:
> > I am not using ADO.NET but it's a simple ADO application.
> >
> > Thanks.
> > Ajey
> >
> > "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> > news:F419CECC-2DF9-4C52-953F-CEA0334B0336@.microsoft.com...
> > > You can do this while you are opening the connection itself.
> > >
> > > For More info refer to:
> > > http://www.codeguru.com/vb/gen/vb_database/adonet/article.php/c5153/
> > >
> > >
> > >
> > >
> > > "Ajey" wrote:
> > >
> > > > Thanks.
> > > > Yes, that's what i want to do.
> > > > And also I want to prevent sql-injection. Thanks John.
> > > > I want to restrict the query to SELECT. I even if the user has
> > permissions
> > > > to modify the table I don't want him to alter it through the query.
> > > >
> > > > How can I open ADO with read only permissons?
> > > >
> > > > Thanks in advance.
> > > > Ajey
> > > >
> > > > "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> > > > news:CF97CE16-EFCF-4212-811C-A8C95E28D03E@.microsoft.com...
> > > > > Hi,
> > > > > By the post I understand that, u have a layee between the database
and
> > the
> > > > > user.
> > > > > The user need to send a select query and the result is displayed
in
> > the
> > > > Grid.
> > > > >
> > > > > The users are presently having a flexibility to send the any kind
of
> > > > query.
> > > > > If they send INSERT, UPDATE or DELETE, your data will be
currupted,
> > and
> > > > you
> > > > > wante to restrict that.
> > > > >
> > > > > If my prediction was correct, what I suggest you is, to use a
Stored
> > > > > Procedure for this purpose or open the ADO with read only
permissions.
> > > > >
> > > > > I believe this answered your question. please revert back if u
have
> > any
> > > > issues
> > > > >
> > > > > thanks and regards
> > > > > Chandra
> > > > >
> > > > >
> > > > >
> > > > > "Ajey" wrote:
> > > > >
> > > > > > Hi,
> > > > > > Can I restrict INSERT, DELETE, UPDATE, INTO queries in ADO?
I
> > get
> > > > the
> > > > > > query as input from user. Run it using ADO connection. Display
the
> > > > result in
> > > > > > grid. However I want to allow only SELECT queries. Is there a
> > property
> > > > on
> > > > > > ADO which allows this? Otherwise I will have to do the parsing
> > myself.
> > > > > >
> > > > > > Thanks in advance.
> > > > > > Ajey
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >|||Hi Ajey
Good to know that ur problem is getting solved. adLockReadOnly will prevent
the users from using INSERT, DELETE and UPDATE. The cursor type is optional.
You can ignore it and procede further.
thanks and regards
Chandra
"Ajey" wrote:
> This seems to be doing the trick. I get following error when I try to
> execute multiple queries:
> sp_cursoropen/sp_cursorprepare: The statement parameter can only be a single
> select or a single stored procedure. : Microsoft OLE DB Provider for SQL
> Server
> But I don't want to use adOpenDynamic but adOpenForwardOnly cursor type.
> At least this now blocks the user from executing multiple statement and so i
> can check only the first token to be SELECT.
> But can I achive same using adOpenForwardOnly cursor type.
> Thanks.
> Ajey
> "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> news:34579EEE-DFDA-4768-9CD8-B5AAFEBE66B3@.microsoft.com...
> > Hi Ajey
> >
> > Will this be of any help:
> >
> > ===========> >
> > Set conn = New ADODB.Connection
> > conn.Open "dns=<>"
> >
> >
> >
> > Dim rs As ADODB.Recordset
> >
> > ' Open the table.
> > Set rs = New ADODB.Recordset
> > rs.Open Query, conn, adOpenDynamic, adLockReadOnly
> >
> > ===========> >
> > thanks and regards
> > Chandar
> >
> >
> > "Ajey" wrote:
> >
> > > I am not using ADO.NET but it's a simple ADO application.
> > >
> > > Thanks.
> > > Ajey
> > >
> > > "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> > > news:F419CECC-2DF9-4C52-953F-CEA0334B0336@.microsoft.com...
> > > > You can do this while you are opening the connection itself.
> > > >
> > > > For More info refer to:
> > > > http://www.codeguru.com/vb/gen/vb_database/adonet/article.php/c5153/
> > > >
> > > >
> > > >
> > > >
> > > > "Ajey" wrote:
> > > >
> > > > > Thanks.
> > > > > Yes, that's what i want to do.
> > > > > And also I want to prevent sql-injection. Thanks John.
> > > > > I want to restrict the query to SELECT. I even if the user has
> > > permissions
> > > > > to modify the table I don't want him to alter it through the query.
> > > > >
> > > > > How can I open ADO with read only permissons?
> > > > >
> > > > > Thanks in advance.
> > > > > Ajey
> > > > >
> > > > > "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> > > > > news:CF97CE16-EFCF-4212-811C-A8C95E28D03E@.microsoft.com...
> > > > > > Hi,
> > > > > > By the post I understand that, u have a layee between the database
> and
> > > the
> > > > > > user.
> > > > > > The user need to send a select query and the result is displayed
> in
> > > the
> > > > > Grid.
> > > > > >
> > > > > > The users are presently having a flexibility to send the any kind
> of
> > > > > query.
> > > > > > If they send INSERT, UPDATE or DELETE, your data will be
> currupted,
> > > and
> > > > > you
> > > > > > wante to restrict that.
> > > > > >
> > > > > > If my prediction was correct, what I suggest you is, to use a
> Stored
> > > > > > Procedure for this purpose or open the ADO with read only
> permissions.
> > > > > >
> > > > > > I believe this answered your question. please revert back if u
> have
> > > any
> > > > > issues
> > > > > >
> > > > > > thanks and regards
> > > > > > Chandra
> > > > > >
> > > > > >
> > > > > >
> > > > > > "Ajey" wrote:
> > > > > >
> > > > > > > Hi,
> > > > > > > Can I restrict INSERT, DELETE, UPDATE, INTO queries in ADO?
> I
> > > get
> > > > > the
> > > > > > > query as input from user. Run it using ADO connection. Display
> the
> > > > > result in
> > > > > > > grid. However I want to allow only SELECT queries. Is there a
> > > property
> > > > > on
> > > > > > > ADO which allows this? Otherwise I will have to do the parsing
> > > myself.
> > > > > > >
> > > > > > > Thanks in advance.
> > > > > > > Ajey
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>|||The error comes only when I specify the cusrosor type as adOpenDynamic and
not when adOpenForwardOnly
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:7F7945AF-AACD-4545-B5F2-1ACE9D3FD1D7@.microsoft.com...
> Hi Ajey
> Good to know that ur problem is getting solved. adLockReadOnly will
prevent
> the users from using INSERT, DELETE and UPDATE. The cursor type is
optional.
> You can ignore it and procede further.
> thanks and regards
> Chandra
>
>
> "Ajey" wrote:
> > This seems to be doing the trick. I get following error when I try to
> > execute multiple queries:
> > sp_cursoropen/sp_cursorprepare: The statement parameter can only be a
single
> > select or a single stored procedure. : Microsoft OLE DB Provider for SQL
> > Server
> >
> > But I don't want to use adOpenDynamic but adOpenForwardOnly cursor type.
> >
> > At least this now blocks the user from executing multiple statement and
so i
> > can check only the first token to be SELECT.
> > But can I achive same using adOpenForwardOnly cursor type.
> >
> > Thanks.
> > Ajey
> >
> > "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> > news:34579EEE-DFDA-4768-9CD8-B5AAFEBE66B3@.microsoft.com...
> > > Hi Ajey
> > >
> > > Will this be of any help:
> > >
> > > ===========> > >
> > > Set conn = New ADODB.Connection
> > > conn.Open "dns=<>"
> > >
> > >
> > >
> > > Dim rs As ADODB.Recordset
> > >
> > > ' Open the table.
> > > Set rs = New ADODB.Recordset
> > > rs.Open Query, conn, adOpenDynamic, adLockReadOnly
> > >
> > > ===========> > >
> > > thanks and regards
> > > Chandar
> > >
> > >
> > > "Ajey" wrote:
> > >
> > > > I am not using ADO.NET but it's a simple ADO application.
> > > >
> > > > Thanks.
> > > > Ajey
> > > >
> > > > "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> > > > news:F419CECC-2DF9-4C52-953F-CEA0334B0336@.microsoft.com...
> > > > > You can do this while you are opening the connection itself.
> > > > >
> > > > > For More info refer to:
> > > > >
http://www.codeguru.com/vb/gen/vb_database/adonet/article.php/c5153/
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > "Ajey" wrote:
> > > > >
> > > > > > Thanks.
> > > > > > Yes, that's what i want to do.
> > > > > > And also I want to prevent sql-injection. Thanks John.
> > > > > > I want to restrict the query to SELECT. I even if the user has
> > > > permissions
> > > > > > to modify the table I don't want him to alter it through the
query.
> > > > > >
> > > > > > How can I open ADO with read only permissons?
> > > > > >
> > > > > > Thanks in advance.
> > > > > > Ajey
> > > > > >
> > > > > > "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> > > > > > news:CF97CE16-EFCF-4212-811C-A8C95E28D03E@.microsoft.com...
> > > > > > > Hi,
> > > > > > > By the post I understand that, u have a layee between the
database
> > and
> > > > the
> > > > > > > user.
> > > > > > > The user need to send a select query and the result is
displayed
> > in
> > > > the
> > > > > > Grid.
> > > > > > >
> > > > > > > The users are presently having a flexibility to send the any
kind
> > of
> > > > > > query.
> > > > > > > If they send INSERT, UPDATE or DELETE, your data will be
> > currupted,
> > > > and
> > > > > > you
> > > > > > > wante to restrict that.
> > > > > > >
> > > > > > > If my prediction was correct, what I suggest you is, to use a
> > Stored
> > > > > > > Procedure for this purpose or open the ADO with read only
> > permissions.
> > > > > > >
> > > > > > > I believe this answered your question. please revert back if u
> > have
> > > > any
> > > > > > issues
> > > > > > >
> > > > > > > thanks and regards
> > > > > > > Chandra
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > "Ajey" wrote:
> > > > > > >
> > > > > > > > Hi,
> > > > > > > > Can I restrict INSERT, DELETE, UPDATE, INTO queries in
ADO?
> > I
> > > > get
> > > > > > the
> > > > > > > > query as input from user. Run it using ADO connection.
Display
> > the
> > > > > > result in
> > > > > > > > grid. However I want to allow only SELECT queries. Is there
a
> > > > property
> > > > > > on
> > > > > > > > ADO which allows this? Otherwise I will have to do the
parsing
> > > > myself.
> > > > > > > >
> > > > > > > > Thanks in advance.
> > > > > > > > Ajey
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >|||Also, I created a stored procedure sp_my_sp2 which raises an error on
execution.
When I try to run multiple queries as:
sp_my_sp2 select * from sysobjects
I can verify that the stored proc as well as the SELECT query gets executed.
But for the following:
select * from sysobjects sp_my_sp2
only the SELECT query gets executed.
What's the behavior for multiple queries in SQL Server?
Thanks in advance.
Ajey
"Ajey" <ajey5@.hotmail.com> wrote in message
news:e8bxOD8SFHA.612@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Can I restrict INSERT, DELETE, UPDATE, INTO queries in ADO? I get the
> query as input from user. Run it using ADO connection. Display the result
in
> grid. However I want to allow only SELECT queries. Is there a property on
> ADO which allows this? Otherwise I will have to do the parsing myself.
> Thanks in advance.
> Ajey
>
>|||"Ajey" <ajey5@.hotmail.com> wrote in message
news:%23Dd%23xs$SFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Also, I created a stored procedure sp_my_sp2 which raises an error on
> execution.
> When I try to run multiple queries as:
> sp_my_sp2 select * from sysobjects
> I can verify that the stored proc as well as the SELECT query gets
> executed.
> But for the following:
> select * from sysobjects sp_my_sp2
select * from sysobjects; exec sp_my_sp2
-Mark
> only the SELECT query gets executed.
> What's the behavior for multiple queries in SQL Server?
> Thanks in advance.
> Ajey
>
> "Ajey" <ajey5@.hotmail.com> wrote in message
> news:e8bxOD8SFHA.612@.TK2MSFTNGP12.phx.gbl...
>> Hi,
>> Can I restrict INSERT, DELETE, UPDATE, INTO queries in ADO? I get
>> the
>> query as input from user. Run it using ADO connection. Display the result
> in
>> grid. However I want to allow only SELECT queries. Is there a property on
>> ADO which allows this? Otherwise I will have to do the parsing myself.
>> Thanks in advance.
>> Ajey
>>
>|||That helps to execute the stored procedure as well
I wanted to make sure that user does not enter any other statement other
than SELECT. I can put restriction on having keywords INSERT, UPDATE,
DELETE, INTO, EXEC, EXECUTE in the query string but what about stored
procedure given as mentioned below. With the execution i verified that in
this case the stored procedure does not get executed. But is this behavior
documented?
"Mark J. McGinty" <mmcginty@.spamfromyou.com> wrote in message
news:2j6ce.1$ZN.0@.fed1read07...
> "Ajey" <ajey5@.hotmail.com> wrote in message
> news:%23Dd%23xs$SFHA.2520@.TK2MSFTNGP09.phx.gbl...
> > Also, I created a stored procedure sp_my_sp2 which raises an error on
> > execution.
> > When I try to run multiple queries as:
> > sp_my_sp2 select * from sysobjects
> > I can verify that the stored proc as well as the SELECT query gets
> > executed.
> >
> > But for the following:
> > select * from sysobjects sp_my_sp2
>
> select * from sysobjects; exec sp_my_sp2
>
> -Mark
>
>
> > only the SELECT query gets executed.
> >
> > What's the behavior for multiple queries in SQL Server?
> >
> > Thanks in advance.
> > Ajey
> >
> >
> > "Ajey" <ajey5@.hotmail.com> wrote in message
> > news:e8bxOD8SFHA.612@.TK2MSFTNGP12.phx.gbl...
> >> Hi,
> >> Can I restrict INSERT, DELETE, UPDATE, INTO queries in ADO? I get
> >> the
> >> query as input from user. Run it using ADO connection. Display the
result
> > in
> >> grid. However I want to allow only SELECT queries. Is there a property
on
> >> ADO which allows this? Otherwise I will have to do the parsing myself.
> >>
> >> Thanks in advance.
> >> Ajey
> >>
> >>
> >>
> >
> >
>|||"Ajey" <ajey5@.hotmail.com> wrote in message
news:uAiO1EATFHA.2996@.TK2MSFTNGP15.phx.gbl...
> That helps to execute the stored procedure as well
> I wanted to make sure that user does not enter any other statement other
> than SELECT. I can put restriction on having keywords INSERT, UPDATE,
> DELETE, INTO, EXEC, EXECUTE in the query string but what about stored
> procedure given as mentioned below. With the execution i verified that in
> this case the stored procedure does not get executed. But is this behavior
> documented?
Not sure if it's documented, but it has been that way forever (given that
SQL 6.5 marked the beginning of time.)
Just the stored procedure name (with args if any) alone will work for a
single statement, but you must use exec for each statement in a batch.
-Mark
> "Mark J. McGinty" <mmcginty@.spamfromyou.com> wrote in message
> news:2j6ce.1$ZN.0@.fed1read07...
>> "Ajey" <ajey5@.hotmail.com> wrote in message
>> news:%23Dd%23xs$SFHA.2520@.TK2MSFTNGP09.phx.gbl...
>> > Also, I created a stored procedure sp_my_sp2 which raises an error on
>> > execution.
>> > When I try to run multiple queries as:
>> > sp_my_sp2 select * from sysobjects
>> > I can verify that the stored proc as well as the SELECT query gets
>> > executed.
>> >
>> > But for the following:
>> > select * from sysobjects sp_my_sp2
>>
>> select * from sysobjects; exec sp_my_sp2
>>
>> -Mark
>>
>>
>> > only the SELECT query gets executed.
>> >
>> > What's the behavior for multiple queries in SQL Server?
>> >
>> > Thanks in advance.
>> > Ajey
>> >
>> >
>> > "Ajey" <ajey5@.hotmail.com> wrote in message
>> > news:e8bxOD8SFHA.612@.TK2MSFTNGP12.phx.gbl...
>> >> Hi,
>> >> Can I restrict INSERT, DELETE, UPDATE, INTO queries in ADO? I get
>> >> the
>> >> query as input from user. Run it using ADO connection. Display the
> result
>> > in
>> >> grid. However I want to allow only SELECT queries. Is there a property
> on
>> >> ADO which allows this? Otherwise I will have to do the parsing myself.
>> >>
>> >> Thanks in advance.
>> >> Ajey
>> >>
>> >>
>> >>
>> >
>> >
>>
>|||Thank Mark.
I even verified with the query execution plan to make sure that the stored
proc does not get executed. But then why no error is shown for the stored
proc name.
"Mark J. McGinty" <mmcginty@.spamfromyou.com> wrote in message
news:2g8ce.17$ZN.14@.fed1read07...
> "Ajey" <ajey5@.hotmail.com> wrote in message
> news:uAiO1EATFHA.2996@.TK2MSFTNGP15.phx.gbl...
> > That helps to execute the stored procedure as well
> > I wanted to make sure that user does not enter any other statement other
> > than SELECT. I can put restriction on having keywords INSERT, UPDATE,
> > DELETE, INTO, EXEC, EXECUTE in the query string but what about stored
> > procedure given as mentioned below. With the execution i verified that
in
> > this case the stored procedure does not get executed. But is this
behavior
> > documented?
> Not sure if it's documented, but it has been that way forever (given that
> SQL 6.5 marked the beginning of time.)
> Just the stored procedure name (with args if any) alone will work for a
> single statement, but you must use exec for each statement in a batch.
> -Mark
>
>
> > "Mark J. McGinty" <mmcginty@.spamfromyou.com> wrote in message
> > news:2j6ce.1$ZN.0@.fed1read07...
> >>
> >> "Ajey" <ajey5@.hotmail.com> wrote in message
> >> news:%23Dd%23xs$SFHA.2520@.TK2MSFTNGP09.phx.gbl...
> >> > Also, I created a stored procedure sp_my_sp2 which raises an error on
> >> > execution.
> >> > When I try to run multiple queries as:
> >> > sp_my_sp2 select * from sysobjects
> >> > I can verify that the stored proc as well as the SELECT query gets
> >> > executed.
> >> >
> >> > But for the following:
> >> > select * from sysobjects sp_my_sp2
> >>
> >>
> >> select * from sysobjects; exec sp_my_sp2
> >>
> >>
> >> -Mark
> >>
> >>
> >>
> >>
> >>
> >> > only the SELECT query gets executed.
> >> >
> >> > What's the behavior for multiple queries in SQL Server?
> >> >
> >> > Thanks in advance.
> >> > Ajey
> >> >
> >> >
> >> > "Ajey" <ajey5@.hotmail.com> wrote in message
> >> > news:e8bxOD8SFHA.612@.TK2MSFTNGP12.phx.gbl...
> >> >> Hi,
> >> >> Can I restrict INSERT, DELETE, UPDATE, INTO queries in ADO? I
get
> >> >> the
> >> >> query as input from user. Run it using ADO connection. Display the
> > result
> >> > in
> >> >> grid. However I want to allow only SELECT queries. Is there a
property
> > on
> >> >> ADO which allows this? Otherwise I will have to do the parsing
myself.
> >> >>
> >> >> Thanks in advance.
> >> >> Ajey
> >> >>
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>

ADO Integrated Security Pass Through Again

The previous thread died so I'm sorry for the repost.
I am working on making my application inherit the workstation login user
information so that users aren't presented with multiple logins.
Does anyone have any experience linking internal application user management
(ie: USERS table) with windows authentication?
I have access rights that a specific to my application such as menu
options, reports, specific actions, etc. The application was developed 8
years ago and supported multiple database platforms. We have moved to only
supporting MS SQL Server 7, 2000 and 2005.
What's the best way to link these together since DBAs wouldn't be able to
assign my application specific access rights via MS SQL Server Management
Studio/Enterprise Manager?
Any comments or suggestions would be great.Hi!
First, your application should use ADO connection string like
"SERVER=mymssql; Integrated Security=SSPI;"
thus application will connect to SQL with current logged user
credentials. Naturally, user must have some rights on SQL server.
You can set those right for NT group, not for individual user accounts.
The user account name is accessible in t-sql and you can build some
additional logic:
CREATE PROC GetCustomUserRights
AS
declare @.NT_login varchar(64)
set @.NT_login = SYSTEM_USER
select UserRightName,UserRightValue from USERS where UserName = @.NT_login
RETURN 0
GO
implying the table USERS has structure and data like:
UserName, UserRightName, UserRightValue
"ACME\user1", "AdvancedMenu", "True"
"ACME\user2", "AdvancedMenu ", "False"
...
Best regards, Anatoli
"Isaac Alexander" <isaacNOSPAM@.goNOSPAMprocura.com> wrote
> The previous thread died so I'm sorry for the repost.
> I am working on making my application inherit the workstation login user
> information so that users aren't presented with multiple logins.
> Does anyone have any experience linking internal application user
> management
> (ie: USERS table) with windows authentication?
> I have access rights that a specific to my application such as menu
> options, reports, specific actions, etc. The application was developed 8
> years ago and supported multiple database platforms. We have moved to only
> supporting MS SQL Server 7, 2000 and 2005.
> What's the best way to link these together since DBAs wouldn't be able to
> assign my application specific access rights via MS SQL Server Management
> Studio/Enterprise Manager?
> Any comments or suggestions would be great.
>|||"Anatoli Dontsov" <Anatoli@.dontsov.com> wrote in message
news:eXg$wQOvGHA.4296@.TK2MSFTNGP06.phx.gbl...
> Hi!
> First, your application should use ADO connection string like
> "SERVER=mymssql; Integrated Security=SSPI;"
> thus application will connect to SQL with current logged user
> credentials. Naturally, user must have some rights on SQL server.
> You can set those right for NT group, not for individual user accounts.
> The user account name is accessible in t-sql and you can build some
> additional logic:
> CREATE PROC GetCustomUserRights
> AS
> declare @.NT_login varchar(64)
> set @.NT_login = SYSTEM_USER
> select UserRightName,UserRightValue from USERS where UserName = @.NT_login
> RETURN 0
> GO
> implying the table USERS has structure and data like:
> UserName, UserRightName, UserRightValue
> "ACME\user1", "AdvancedMenu", "True"
> "ACME\user2", "AdvancedMenu ", "False"
> ...
Thanks. Has anyone had any experience creating the list of users for the
access rights? For example: a user logs in and I check their access rights,
is the best solution to have admin type in the NT login name into my app and
add access rights? Or can I provide a dropdown of users based in some
network query?

ADO Integrated Security Pass Through

I am working on making my application inherit the workstation login user
information so that users aren't presented with multiple logins.
Is it safe enough to use Integrated Security via the connection string. If
that succeeds, read the windows user name and check that against an internal
username?
Does anyone have any experience linking internal application user management
(ie: USERS table) with windows authentication?
Any comments or suggestions would be great.First key point: By using Windows authentication, you do NOT have to have a
USERS table. In SQL Server, you assign all permissions to the Windows login
or the Windows network group. (It makes life so much easier for the DBA.)
Windows Integrated Security (for SQL 2000) is far superior to SQL
authentication, and far better than trying to keep a USERS table up to date.
In SQL Server, use the SYSTEM_USER system function to retrieve the users
login name (in the form of [domain\username].)
Example:
SELECT SYSTEM_USER
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Isaac Alexander" <isaacNOSPAM@.goNOSPAMprocura.com> wrote in message
news:uJVfx3ntGHA.2392@.TK2MSFTNGP05.phx.gbl...
>I am working on making my application inherit the workstation login user
>information so that users aren't presented with multiple logins.
> Is it safe enough to use Integrated Security via the connection string. If
> that succeeds, read the windows user name and check that against an
> internal username?
> Does anyone have any experience linking internal application user
> management (ie: USERS table) with windows authentication?
> Any comments or suggestions would be great.
>|||"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23Mmz2potGHA.324@.TK2MSFTNGP06.phx.gbl...
> First key point: By using Windows authentication, you do NOT have to have
> a USERS table. In SQL Server, you assign all permissions to the Windows
> login or the Windows network group. (It makes life so much easier for the
> DBA.)
> Windows Integrated Security (for SQL 2000) is far superior to SQL
> authentication, and far better than trying to keep a USERS table up to
> date.
> In SQL Server, use the SYSTEM_USER system function to retrieve the users
> login name (in the form of [domain\username].)
> Example:
> SELECT SYSTEM_USER
> --
Thanks Arnie. That call is very helpful.
However, I have access rights that a specific to my application such as menu
options, reports, specific actions, etc. The application was developed 8
years ago and supported multiple database platforms. We have moved to only
supporting MS SQL Server 7, 2000 and 2005.
What's the best way to link these together since DBAs wouldn't be able to
assign my application specific access rights via MS SQL Server Management
Studio/Enterprise Manager?

ADO InfoMessage doesn't return all messages

I would like to return messages from a 3 'action' stored procedure to
the user of an Access front end via ADO. I've tried looping through
MyConnection.Errors within the InfoMessage event and this returns the
message I see in QA for each action if the user has only changed 1
record. However if the user first updates more than 1 record in Access
and then executes this stored procedure (via a command object) I still
get back only 1 message per action as opposed to 1 message per action
per record as I do in QA. MyConnection.Errors.Count always stays at 3
no matter how many records are updated, though in QA I get 3 * the
number of records messages. I can also reproduce this with print. QA
will display say 9 print messages for 3 records but ADO will only
display 3 messages, those for the first record in the batch.
I can load all the info into an output parameter but would like to use
InfoMessages to build 1 complete string for Msgbox.You might try adding SET NOCOUNT ON to the beginning of your procs. This
will suppress DONE_IN_PROC (including rowcounts) that can cause problems
with ADO application processing of resultsets and messages.
Hope this helps.
Dan Guzman
SQL Server MVP
"Steve" <morriszone@.hotmail.com> wrote in message
news:1110927775.827057.213060@.o13g2000cwo.googlegroups.com...
>I would like to return messages from a 3 'action' stored procedure to
> the user of an Access front end via ADO. I've tried looping through
> MyConnection.Errors within the InfoMessage event and this returns the
> message I see in QA for each action if the user has only changed 1
> record. However if the user first updates more than 1 record in Access
> and then executes this stored procedure (via a command object) I still
> get back only 1 message per action as opposed to 1 message per action
> per record as I do in QA. MyConnection.Errors.Count always stays at 3
> no matter how many records are updated, though in QA I get 3 * the
> number of records messages. I can also reproduce this with print. QA
> will display say 9 print messages for 3 records but ADO will only
> display 3 messages, those for the first record in the batch.
> I can load all the info into an output parameter but would like to use
> InfoMessages to build 1 complete string for Msgbox.
>

Monday, February 13, 2012

ADO connection

In VB6 I use more Forms where users can Add, Edit, Delete Records from more tables.

WHICH IS THE BEST METHOD

1. To open a connection when user start the application and I close the connection when user leave the application.

In Login form (Public cn As ADODB.Connection)

SirConectare_SQL = "Provider=SQLOLEDB.1" & _
";Password='" & Pass & "'" & _
";Persist Security Info=False" & _
";User ID='" & UserName & "'" & _
";Initial Catalog='" & DataBaseName & "'" & _
";Data Source='" & ServerName & "'"

Set cn = New ADODB.Connection

With cn
.ConnectionString = SirConectare_SQL
.Open
End With

OR

2. To Open a connection in each Form and close the connection when Form is UnLoadetThe first case is not possible because

Set cn = New ADODB.Connection (this is in Form Login)
In other form it is necesary to set the connection again.|||The rule is that you only keep the connection open as long as needed - and no longer. What are your concerns ?|||How many clients will use this application ?|||5-8 users|||Will the security credentials be different for each user ? What is the purpose for the application and the multiple forms ? Are you thinking about opening 1 connection object that will be used for multiple recordset/command objects ?|||How I can open a connection when user start the application and kepp open until user leave the application ?

In module:
Public cn As ADODB.Connection

In first form when is load:
SirConectare_SQL = "Provider=SQLOLEDB.1" & _
";Password='" & Pass & "'" & _
";Persist Security Info=False" & _
";User ID='" & UserName & "'" & _
";Initial Catalog='" & DataBaseName & "'" & _
";Data Source='" & ServerName & "'"

Set cn = New ADODB.Connection

With cn
.ConnectionString = SirConectare_SQL
.Open
End With

If I want to use "cn" in other forms is not possible (is not open)|||Are you destroying/closing the connection before the 2nd form is loaded ?|||I don`t close the connection, but I close (Unload) the first form.
The connection is closed ?|||Where are you declaring - Public cn As ADODB.Connection ?|||This declaration is made on a Module

Public cn As ADODB.Connection

In the Startup Form (where user input UserName and Password)

Set cn = New ADODB.Connection

With cn
.ConnectionString = SirConectare_SQL
.Open
End With

If I want to use this connection on other forms I can't because is Closed.
I want to open the connection only one time(when application startup), and if it possible to use the connection in all form.|||Open the connection in a module or class when login in form successfull. Close it when application main form is closed.|||This Looks Like VB code If it is Do not forget to set the DBconnection ( and all all objects associated to it) = nothing

set cn = nothing

I personnally like to use 1 public DB connection instead of multiple connections per app.

Hope this helps.
LJ|||Hello Gurus out there!

I want to know what would be the best method in opening a connection to server:

1. a connection (declared globally) that is open once during Login and access thru all forms and be closed only when application is terminated
What is the advantage/disadvantage of this method in my SQL Server 2k resources or in any RDBMS?

2. a connection is opened only when needed but everytime i execute a query against the database i have to open also that connection and terminate when it is not used...What is the advantage/disadvantage of this method in my SQL Server 2k resources or in any RDBMS?

Secondly, how can we know the resources used by the users that are connected to my SQL SERVER in terms of memory usage and CPU?

Im using VB/FOxPro and I want to know the best practice in terms of opening a connection to the database coz Im expecting to have 20 or more users online simultanously and hook to my server as soon as we are finished with our system.

I hope you can light up our minds with these concerns.

Thanks,

Bernie|||Bernie - What driver are you using ?

ADO Can, EM and QA cannot

Dear all,
I'm using SQl Server 2K on a webserver and the sa password is "abcde". Using
this user name and password, and with ADO, I established our website.
Now the website is still working well,can input data,show data!!
But I cannot login to this SQL Server with "Enterprise Manager" or "Query
Analyzer" on the server. Either with sa or Windows-Authenticate. (I user the
administrator to login to the server).
I'm running EM and QA on the webserver(With administrator account), not on
the client PC.
The EM and QA can work well before.
One thing is, the webserver may be infected by virus of Spida Worm.
Currently, when I try to run EM or QA, then a long time waiting. Then told
me: connection failed,...
But the website system is still working well.
Please tell me how to run up the Enterprise Manager? Thanks a lot.Hi
It is not a good idea to have SQL Server on an externally exposed server,
and it is always better to dedicate the server for a single use.
If you are connecting using ADO then you should be able to use the client
tools using the same login, check that you are using the same protocols.
John
"goldenshine" wrote:
> Dear all,
> I'm using SQl Server 2K on a webserver and the sa password is "abcde". Using
> this user name and password, and with ADO, I established our website.
> Now the website is still working well,can input data,show data!!
> But I cannot login to this SQL Server with "Enterprise Manager" or "Query
> Analyzer" on the server. Either with sa or Windows-Authenticate. (I user the
> administrator to login to the server).
> I'm running EM and QA on the webserver(With administrator account), not on
> the client PC.
> The EM and QA can work well before.
> One thing is, the webserver may be infected by virus of Spida Worm.
> Currently, when I try to run EM or QA, then a long time waiting. Then told
> me: connection failed,...
> But the website system is still working well.
> Please tell me how to run up the Enterprise Manager? Thanks a lot.|||Thanks,John,
Can you please tell me how to check the protocols? Since the website(
Asp.net) can use ADO to connect SQL Server and works well, why
EnterpriseManager cannot?
Thanks
"John Bell" wrote:
> Hi
> It is not a good idea to have SQL Server on an externally exposed server,
> and it is always better to dedicate the server for a single use.
> If you are connecting using ADO then you should be able to use the client
> tools using the same login, check that you are using the same protocols.
> John
>
> "goldenshine" wrote:
> > Dear all,
> > I'm using SQl Server 2K on a webserver and the sa password is "abcde". Using
> > this user name and password, and with ADO, I established our website.
> > Now the website is still working well,can input data,show data!!
> >
> > But I cannot login to this SQL Server with "Enterprise Manager" or "Query
> > Analyzer" on the server. Either with sa or Windows-Authenticate. (I user the
> > administrator to login to the server).
> >
> > I'm running EM and QA on the webserver(With administrator account), not on
> > the client PC.
> > The EM and QA can work well before.
> > One thing is, the webserver may be infected by virus of Spida Worm.
> > Currently, when I try to run EM or QA, then a long time waiting. Then told
> > me: connection failed,...
> > But the website system is still working well.
> >
> > Please tell me how to run up the Enterprise Manager? Thanks a lot.|||Hi
Look in the client network configuration utility on the client and the
server network configuration utitlity on the server.
John
"goldenshine" <goldenshine@.discussions.microsoft.com> wrote in message
news:FA91D6BA-7F42-4BAA-A01C-969E63685059@.microsoft.com...
> Thanks,John,
> Can you please tell me how to check the protocols? Since the website(
> Asp.net) can use ADO to connect SQL Server and works well, why
> EnterpriseManager cannot?
> Thanks
>
> "John Bell" wrote:
>> Hi
>> It is not a good idea to have SQL Server on an externally exposed server,
>> and it is always better to dedicate the server for a single use.
>> If you are connecting using ADO then you should be able to use the client
>> tools using the same login, check that you are using the same protocols.
>> John
>>
>> "goldenshine" wrote:
>> > Dear all,
>> > I'm using SQl Server 2K on a webserver and the sa password is "abcde".
>> > Using
>> > this user name and password, and with ADO, I established our website.
>> > Now the website is still working well,can input data,show data!!
>> >
>> > But I cannot login to this SQL Server with "Enterprise Manager" or
>> > "Query
>> > Analyzer" on the server. Either with sa or Windows-Authenticate. (I
>> > user the
>> > administrator to login to the server).
>> >
>> > I'm running EM and QA on the webserver(With administrator account), not
>> > on
>> > the client PC.
>> > The EM and QA can work well before.
>> > One thing is, the webserver may be infected by virus of Spida Worm.
>> > Currently, when I try to run EM or QA, then a long time waiting. Then
>> > told
>> > me: connection failed,...
>> > But the website system is still working well.
>> >
>> > Please tell me how to run up the Enterprise Manager? Thanks a lot.

Sunday, March 25, 2012

advice about a worm intrusion alert

Thursday, March 22, 2012

AdventureWorks

Tuesday, March 20, 2012

Advatnages and DisAdvantages of

Monday, March 19, 2012

Advanced SELECT for a newbie

Thursday, March 8, 2012

ADS user and sql 2005

ADP connection to SQL

ADP connection to SQL

Tuesday, March 6, 2012

Adp And Sql Server 2005

Sunday, February 19, 2012

ADO query restricting to SELECT

ADO Integrated Security Pass Through Again

ADO Integrated Security Pass Through

ADO InfoMessage doesn't return all messages

Monday, February 13, 2012

ADO connection

ADO Can, EM and QA cannot

ADO Can, EM and QA cannot

ADO Can, EM and QA cannot

ADO can run, EnterpriseManager cannot ?

ADO can run, EnterpriseManager cannot ?

SQL Adding a User account

Blog Archive

About Me