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 |
|
jhunt
Starting Member
21 Posts |
 Posted - 2002-06-19 : 03:10:51
|
| We have a multi client application that uses SQL Server as the server.At present when users are added to the application, a user for each app DB(Min 3, could be as many as 8 or 9) and an SQL server login is also added to the SQL Server.A simpler method would be to have the application log all users in under the same login. But then, by my understanding transaction locks would cease to exist, since transactions are locked on a user basis.(Is that right?)We have looked at using an Application role but this doesn't seem to avoid the above problem.The problem we are facing is that managing all these users and databases is an administrative nightmare and it is quite possible tat we will run into bugs and problem down the line. We have already hit a few with backups and restores between servers.Incidentally we can't use windows authentication because many of our users run win 98/me.Am I wrong about the transaction lock process? If you have any expericence of a multi client app, I would really appreciate hearing how you set up your user authentication process.Thanks |
|
|
MakeYourDaddyProud
184 Posts |
Posted - 2002-06-19 : 04:22:57
|
| Hi Sir,SQL Server Lock resources are allocated on a connection thread basis meaning that two users (regardless if they are the same user) cannot share a write lock resource on data pages. Each connection thread will have to wait for a write lock on a page to be released before locking that page for its own use (unless a specific isolation level is enabled for that data). Read locks are by default shared.The best approach under both NT and SS authentication would be to create user groups with certain levels of priveleges within your app and simply add new users to those groups. This will make your DB user management far simpler to adminster in the long run.HTHDaniel Small CEOwww.danielsmall.com IT Factoring |
 |
|
|
jhunt
Starting Member
21 Posts |
Posted - 2002-06-19 : 04:37:10
|
| Thanks for your reply. Actually each of our databases does contain a role to help administer permissions to users. Maybe I'm lazy but having users and roles and logins to administer is a bit daunting. However all users will have the same permissions to each database. So if the App.were to use the same SQL login details for each user, without losing the ability to lock transactions then user management would become much simpler. So that would be good news.I seem to have read somewhere about locks being on a thread by thread basis. However I tested logging in as the same user and logging in as a different user during a transaction and only in the case of the different user id did the lock appear to be applied.But I will have another look at that. Thanks for your helpJustin |
|
|
|
|
|
|
Users and transactions