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 |
|
ahallett
Starting Member
7 Posts |
 Posted - 2007-06-15 : 09:46:56
|
| As a beginner, I'm attempting to set up security for SQL Server But it's not working and am fairly confused at the moment.The current problem is to do with getting SQL Security logins to work. I've added a login for a 'reader' user (no updates allowed) and have set the database user to use only the 'db_datareader' role. Yet, when I logon as that user, I can update data without a problem.The system is a standalone PC using XP Pro with SQL Express installed. The connection string is valid (and works, allowing access to all data) and is of the type: "server=xxxxx;database=xxxx;Trusted_Connection=True;"The application is written is VB.NETI hope I'm making some simple mistake here and I'd really appreciate any thoughts ...Many thanks |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-06-15 : 14:36:42
|
| Is it windows account? Is it member of local admin group on the server? Did you put it in any server role? |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-06-15 : 15:50:22
|
| looks like you are not actually logging in with the account you are setting up! Look closely at your connection string, you are not specifying a username or password at all, you are just using your windows account to authenticate.Connection strings aren't just techno-mumbo-jumbo -- be sure to read them carefully and understand how they work.- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
ahallett
Starting Member
7 Posts |
Posted - 2007-06-16 : 08:15:15
|
| Thanks to both of you for the replies.rmiao - yes, it is a windows account; and no, I didn't include it in a server role, just a database role. i didn't think this was required. Also - re being a member of local admin group on the server, I'm not entirely sure what you mean by the question, but the users is listed as an administrator on the XP Pro system. (This is a standalone PC and not a server system.)Jeff - I have indeed checked out the syntax I am using and I thought that was the point about trusted connections: that you didn't need to supply a user and password. My info from http://www.connectionstrings.com/?carrier=sqlserverand other sites. But are these wrong? Advice much appreciated ... |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-06-16 : 17:28:13
|
| Sql puts windows local administrators group on the machine in sysadmin role be default. Every windows account in that group has sa rights if you didn't remove builtin\administrators from sysadmin. |
|
|
|
ahallett
Starting Member
7 Posts |
Posted - 2007-06-17 : 05:32:25
|
| Thanks for this. Before I do this, I presume I need to make sure that I have set up all other users with a login and so on. I'm worried that deleting builtin\adminsitrators will mean that I then can't do anything ...Do you happen to know of a good site that gives a simple (for beginners!) description of the security model? I have found quite a few sites that attempt it (including the SQL books on line) but they appear to expect you to know the concepts already.Thanks again |
|
|
|
ahallett
Starting Member
7 Posts |
Posted - 2007-06-18 : 09:04:31
|
| All working. Many thanks - deleting builtin\administrators did the trick ... |
|
|
|
|
|
|
|
|
Datareader Security