|
Joe Smith IX wrote: Am I missing something here? Thanks.
Only a little bit. You are just connecting to the database engine, not any particular database itself.
Look in the "Security\Logins" tab - this lets you see the users that sql server knows about. in a defauly install you have "Builtin\Administrators" this is the windows administrators group. You also have sa.
You can create a new user id in here, and give it a password (rather than associate it with a windows account / group). Also look at the user mappings tab.
Create a database, and go into the DatabaseName\Security\ section, this is where you grant access priveledges to individual user accounts. It may be that user1 can access db1 and db2, and user2 can access db2 and db3. They will all be able to connect to the database engine.
You can restrict access down to the table / view / stored procedure level (e.g. user1 may be able to access db1.table1, but not db1.table2).
And remember, it's the connection string in the application which provides the userID to the database engine. The engine will grant permission based upon the connection string.
I would suggest that if you're serious about preventing rogue / malicious access to you data, you invest in a good book on sql server security. (about 4 years ago I went on a 2 day course for sql2k, which was well worth it). Experiment. It has very robust security features that don't need to be linked with the windows login accoutn.
ChrisB
|
|
|
|
|
I think I finally understand a bit more about how these groups come into play. The easier solution for me now is just simply delete the builtin\administrators and builtin\user, leaving just sa and those ###groups. I believe this break the link to windows login all together, leaving only sa login as the possible SQL login.
Thanks a lot for all your help.
|
|
|
|
|
Joe Smith IX wrote: I believe this break the link to windows login all together, leaving only sa login as the possible SQL login.
Yikes! Bad, very bad, very very bad idea.
Without droning on about how bad an idea it is to leave your login to 'sa' I will direct you to a tut that shows you how to change it.
http://blogs.msdn.com/sqltips/archive/2005/08/27/457184.aspx[^]
http://blogs.msdn.com/sqltips/archive/2005/10/10/479268.aspx[^]
Good luck!
"We've all heard that a million monkeys banging on a million typewriters will eventually reproduce the entire works of Shakespeare. Now, thanks to the internet, we know this is not true." -- Professor Robert Silensky
|
|
|
|
|
Joe Smith IX wrote: At first, I am considering using SQL Express 2005, but since it links the security to Windows login, I can't prevent users from opening the database (since the program I am creating will be run under Admin login).
Incorrect. Take the time to RTFM. You'll be pleasently surprised at how powerful and full featured the **FREE** advanced feature SQL Server 2005 Express edition is.
"We've all heard that a million monkeys banging on a million typewriters will eventually reproduce the entire works of Shakespeare. Now, thanks to the internet, we know this is not true." -- Professor Robert Silensky
|
|
|
|
|
Aaargghhh, I'm getting confused...
Here is my SQL statement:
SELECT DISTINCT(pb.id), name, file, alttext, votecount, showcount, rating, link, lastwin_year, lastwin_month, MAX(place) AS place FROM pb LEFT JOIN pv ON pb.id = pv.id WHERE pv.lastwin_month = $current_month AND pv.lastwin_year = $current_year GROUP BY pb.id, pv.lastwin_year, pv.lastwin_month ORDER BY rating DESC LIMIT 0, 3
The problem is that it returns 0 rows if the table pv is empty. How do I make it return 3 pb rows with highest ratings if pv is empty?
If there are 3+ rows in pv, the SQL statement works perfectly. The only fields that are unique in the two tables are pb.id and pv.id. pv.lastwin_year, pv.lastwin_month are not unique.
Thank you,
|
|
|
|
|
change left join to LEFT OUTER JOIN
Mike Lasseter
|
|
|
|
|
A good idea, but it doesn't solve the problem
I still get 0 rows back.
|
|
|
|
|
pv.lastwin_month = $current_month AND pv.lastwin_year = $current_year
You must allow these two columns to be null as well example
(pv.lastwin_month = $current_month or pv.lastwin_month is null) and
(pv.lastwin_year = $current_year or pv.lastwin_year is null)
Mike Lasseter
|
|
|
|
|
... that will retrieve some rows, but what about the rows where lastwin_month != $current_month and lastwin_month IS NOT NULL (and same for lastwin_year)? These rows are not retrieved
|
|
|
|
|
If you care about rows that != a value, why are you specifing them in the where clause?
Mike Lasseter
|
|
|
|
|
What I want are the 3 rows in the pb table with the highest rating.
If these rows have a match (id) in the pv table, I want a row of pv information for each pb row as well. The information in pv is only relevant if lastwin_year and lastwin_month match the $current_year and $current_month parameters. No information should be retrieved from pv if no matching lastwin_year/lastwin_month can be found. Place should be as low as possible.
For example:
pb
---
id = 1, rating 56
id = 2, rating 63
id = 3, rating 42
id = 4, rating 70
pv
---
id = 2, lastwin_year 2006, lastwin_month 10, place 3
id = 2, lastwin_year 2006, lastwin_month 10, place 1
id = 3, lastwin_year 2006, lastwin_month 7, place 3
id = 4, lastwin_year 2005, lastwin_month 8, place 3
id = 4, lastwin_year 2005, lastwin_month 9, place 3
desired result ($current_year = 2006, $current_month = 10)
---
id = 4, rating 70, NULL
id = 2, rating 63, lastwin_year 2006, lastwin_month 10, place 1
id = 1, rating 56, NULL
|
|
|
|
|
I think this is what you are trying to do
select top 3 *
from pb left outer join
(select * from pv where lastwin_year = year and lastwin_month = month) b on pb.id = b.id
order by value
-- modified at 14:40 Thursday 5th October, 2006
Mike Lasseter
|
|
|
|
|
Brilliant! Exactely what I needed to get the right results back.
Thank you
|
|
|
|
|
Hi,
I currently have a stored procedure which is able to delete a record of my choice..
Instead of having the RECORD deleted, I would like the RECORD to disappear from the Datagrid, and have a boolean value of the 'Deleted' Field in that RECORD be set to 1...
Is this implemented in the Stored Procedure? Any ideas would be appreciated..
|
|
|
|
|
This is a very common approach. There should be no problems implementing this into a stored procedure (except you will have to remove the item from the datagrid in the code).
Mike Lasseter
|
|
|
|
|
Thanks for the reply. Appreciate it. I'll go figure it out.
Take care.
|
|
|
|
|
I would Imagine that you are populating your Datagrid in a method. If you do not have a separate method for this I would highly recommend it. When the user activates the UI control to delete the record from the datagrid I would also call the method that populates your datagrid. If you call this method after you delete the DB record your Datagrid will automatically re-populate with out the record.
Hope this helps.
|
|
|
|
|
I have the below query but would like to use a wild card value to do the search.
I need to pass a value like "on" and find, Jason or Preston. It would be nick if it is also not case sensitive....
This value is coming from VB.Net App and this is messing with me...
My Access (SQL) statement is:
SELECT *
FROM MainTB
WHERE myCustomer Like [@myVar];
But the LIKE needs to be [%myVar%] I think.
Feedback welcome please......
When people make you see red, be thankful your not colour blind.
|
|
|
|
|
SELECT *<br />
FROM MainTB<br />
WHERE myCustomer Like ('%myVar%');
|
|
|
|
|
Running this query from access it does not ask me for an input it is looking for a user of %myVar%
As it is in the ' ' string quotes... I have already found this and tryed it...
Any other thoughts.....
?
When people make you see red, be thankful your not colour blind.
|
|
|
|
|
The string concatenation character might help
select *
from MainTB
where upper(myCustomer) like '%'|upper(@myVar)|'%' This will also make it case insensitive.
Chris Meech
I am Canadian. [heard in a local bar]
Nobody likes jerks. [espeir]
The zen of the soapbox is hard to attain...[Jörgen Sigvardsson]
I wish I could remember what it was like to only have a short term memory.[David Kentley]
|
|
|
|
|
The following error is occuring while attempting to connect to sql server the connection string is as follows:
"conn = new SqlConnection("Password=786;data source=DBSERVER;initial catalog=Venture;persist security info=False;user id=sa;workstation id=Venture;packet size=4096");"
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.)
|
|
|
|
|
I've seen this error occur when SQL Server is not running. Not sure that is your problem, but it is something to eliminate.
"We've all heard that a million monkeys banging on a million typewriters will eventually reproduce the entire works of Shakespeare. Now, thanks to the internet, we know this is not true." -- Professor Robert Silensky
|
|
|
|
|
It could be exactly what it says. There is a configuration tool for SQL 2005 that will let you check this. Check the server protocols. There will be a short list something like this:
Shared Memory Enabled
Named Pipes Disabled
TCPIP Enabled
VIA Disabled
If you right-click on TCPIP and select properties you might be as surprised as me to find this:
IP1
Active Yes
Enabled No
Address 192.168.1.1
...
IP2
Active Yes
Enabled No
Address 127.0.0.1
...
Apparently SQL server comes with most things turned off. Try setting the Enabled(s) to yes and see if your problem changes. I was not using the TCPIP, so could not test it. Please post back if this made a difference.
>>>-----> MikeO
|
|
|
|
|
Thanx!!
It is now working fine.
I just enabled the named pipe, after that it is working.
|
|
|
|