Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
DevonsDad
Starting Member
2 Posts |
 Posted - 2010-10-20 : 10:20:14
|
| I am completely new to SQL this week. I have installed 2008 R2, imported an existing access db. What I have found seems strange. When I use a connection string using Windows Authentication, everything works fine. When I use SQL Authentication, suddenly I'm getting errors all over the place. Granted, I'm sure my coding isn't great - but can someone explain how I might resolve this - or why this is?For example, I get "Incorrect Syntax" errors relating to my SELECT Statments. These go away if I quit using * in favor of reciting every last field that I'd like to use. (OK - I guess I've been lazy here - but still...) Also, it seems selective too (pardon the pun). If I put "on error resume next" I find that SOME fields will pull from the database fine, but others will not. No logic as far as I can tell...Again - with Windows Authentication - the EXACT same page renders perfectly. So.... how can this be - that the method of authentication makes a difference in how your code is interpretted?Cheers everyone!! |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-10-21 : 07:08:10
|
"These go away if I quit using * in favor of reciting every last field that I'd like to use. (OK - I guess I've been lazy here - but still...) "No "but still" on that one, you need to do that period!If you do SELECT * your application will get all columns. (Lets assume, at the moment, that is exactly the columns that it needs. There are some issues about the fact that SQL has to work out what "*" actually means, which may be bad for performance, but lets ignore than - its probably trivial anyway).Then in 10 months time you have thousands of queries using SELECT * FROM SomeTable and you now add a TEXT column with a lengthy description - the notes that the Call Centre wants to keep - or you decide to add a Photograph image of all your clients. Now your SELECT * is pulling an extra MB or two of data from that new column on EVERY row - and the application will NOT be using that at all (until specific queries are modified to make use of that extra column, and not EVERY query will EVER need it!!). Performance will be dire and it will be VERY time consuming to fix because there will be 1,000's of SELECT *'s everywhere (I know this only too well because I have been the consultant on one more than one occasion that has had to explain to a company's DEVs just why their application has suddenly and catastrophically taken a nose dive on performance - and at one of those the Call Centre so loved having File Notes on the Client Screen that they spent a cheerful week inputting all the PostIt notes and scraps of paper they could find because it revolutionised their lives!!!)Most likely cause of your problem is that when you connect with WIndows Authentication you have "Higher" permissions (e.g. System Administrator?  ) so you can do anything / access anything. If you connect as a user with "lesser" permissions and have (say) NO Select permissions on a particular table then it will be just as if that table did not exist - you won't be able to see it at all - hence you will get syntax errors.You can GRANT SELECT ON MyTable TO SomeUserand that should fix the problem. Beware what you are granting though, as you are "opening up" your security.If you have several users then it would be better to do (syntax is not correct, so this is just pseudo code):CREATE GROUP MyReadOnlyGroupGRANT SELECT ON MyTable TO MyReadOnlyGroupADD USER SomeUser1 TO MyReadOnlyGroupADD USER SomeUser2 TO MyReadOnlyGroupCREATE GROUP MyReadOnlyGroupGRANT INSERT, UPDATE ON MyTable TO MyUpdatersGroupADD USER SomeUser1 TO MyUpdatersGroupHowever, as you are a newbie you may want to consider the following:An alternative is that you create a Stored Procedure for your query. This Sproc is created by "you" and is created with the permissions that "you" have - so it can SELECT from MyTable, for example.You then GRANT EXECUTE ON MySproc TO MyReadOnlyGroupand now that user(s) does not have ANY permissions on the underlying table, they can only Execute the Sproc. (So if the SProc deletes a record they can only delete one record at a time, whereas if you give them DELETE permission on MyTable AND they get direct access to the database they can delete the whole lot in one strike  )DELETE FROM MyTable deletes everything. If you were about to add WHERE CustomerID = 1234 but pressed "Run" a bit quick then TOUGH!Users can get access the to the database using Excel - its not hard  - hence "grown up" (apologies ....) applications tend to limit access to the database to only be via Sprocs.(Another benefit is that an SProc is a discrete unit of logic. It is easy to fix / enhance / improve, test in isolation, and deploy an improved version. e.g. a bug fix, or a change in Business Rules Logic.However, if you don't like typing out all the column names in full you'll probably hate having to create a whole SProc for each query I'm afraid  ) |
 |
|
|
DevonsDad
Starting Member
2 Posts |
Posted - 2010-10-21 : 15:46:52
|
| Cheers Kristen - you've certainly given me a lot to read up on. Very interesting. I never really thought about the consequences of using * - my applications tend to be pretty small, but I appreciate getting into good habits for sure. I know both user logins have the same permissions - they both have public and sysadmin roles. I also gave the same schema (dbo) to both users. I'd just really like to understand where the permissions are different. I see you can assign users to tables, but they are all blank. Can you think of anywhere else I could look? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-10-21 : 17:56:11
|
| If they have the same permissions (especially if both have SA) then there will (should?!!) be no difference.So suggest you check that they genuinely do both have SysAdminEXEC sp_helpuser will give you a list of users in the current database, andEXEC sp_helpuser 'MyUserName'will show the ROLEs (in the current DB) for that specific user Sorry, can't remember how to see the "effective" permissions on a given table, for a specific user. But that would help in seeing if there is anything different between the two users.If you have explicitly DENY'd permissions on a table for one of the users that might be a reason. Seems unlikely that could have happened though? |
|
|
|
|
|
|
|
|
Windows v. SQL Authentication -asp coding matters?