September 3, 2011 at 4:32 pm
When it comes to the mapping of Logins to (SQL Instance level) and Users(DB level) under SQL Authentication is there any kind of best practices or things to be aware of if you are looking at using them in some way other then 1:1 (where the DB User is the same name of the login and therefore only 1 user within a DB is mapped to a single login?
Currently we have a 1:1 setup but I've been thinking this may be unnecessary. The SQL Login/DB User (and related password) that the user uses is not known to them. The Accounting system we use has its own security that by default provides users with a name & passwsord it manages and it connects (behind the scenes and unbeknownst to the user) with whatever SQL Login/DB User you've setup on SQL Server. I've got each real world user mapped to their own Login & DB User (is only DB on SQL Server Instance) and I'm thinking it might be a lot easier to manage if groups of common real world users shared a single Login but with unique DB USers (being mapped to that SQL Login).
Thoughts
Kindest Regards,
Just say No to Facebook!September 4, 2011 at 3:29 am
it depends.
How is auditing handled ? ( who did what, e.g. who last modified a row,...)
We use a databases dbgroups. these actually hold the grants. a sqluser is only added to a dbgroup according to the requested auth level.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 8, 2011 at 1:42 pm
In my opinion you have the best case scenario in terms of being able to associate every DB session with a specific person. In my experience most applications do not maintain traceability from person to Server Login and it amounts to more than a simple hassle when troubleshooting server issues. Yes, it is a bit more work to issue each person a Server Login unique to them, however when troubleshooting you have that much more information to help resolve the problem.
I would say leave it as is and focus on ways to make the login creation and dropping process more efficient.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 8, 2011 at 3:54 pm
All the auditing is handled by the application and oustide of SQL Server. I do keep tabs on thinsg myself outside of the apps paramaters but thats more passive then active.
Thanks
Kindest Regards,
Just say No to Facebook!September 8, 2011 at 4:02 pm
opc.three (9/8/2011)
In my opinion you have the best case scenario in terms of being able to associate every DB session with a specific person. In my experience most applications do not maintain traceability from person to Server Login and it amounts to more than a simple hassle when troubleshooting server issues. Yes, it is a bit more work to issue each person a Server Login unique to them, however when troubleshooting you have that much more information to help resolve the problem.I would say leave it as is and focus on ways to make the login creation and dropping process more efficient.
I knew that unique logins mapped to DB Users would provide extensive tracking and auditing capabilities but with just under a thousand users I was wondering if perhaps Multiple DB Users to Logins might be feasible. The DB server that’s runs this has no other application DB's or anything like it to manage save for my own DBA Admin database (one on all of my servers to keep admin functions and anything I want to use but store outside of a regular database) and so all the users and logins would be for just this DB. I was pondering the idea of creating 1 login for each remote site (any location off site that connects in via VPN regularly during business hours) and assigning multiple DB Users to that.
Currently I have some T-SQL code I run to create the T-SQL for creating any new logins and Users as well as disabling past users (when they leave) . SO creating and dropping the users isn’t soo much a hassle as keeping track of them all. For example when using SSMS Activity Monitor I have a lot of rows in the results because hundreds of users are active at one time. Even after hours there are several dozen logins at any point save for middle of the night.
If you have any more thoughts on this that differ after reading the above please share.
Thanks for taking the time to reply
Kindest Regards,
Just say No to Facebook!September 8, 2011 at 4:28 pm
YSLGuru (9/8/2011)
opc.three (9/8/2011)
In my opinion you have the best case scenario in terms of being able to associate every DB session with a specific person. In my experience most applications do not maintain traceability from person to Server Login and it amounts to more than a simple hassle when troubleshooting server issues. Yes, it is a bit more work to issue each person a Server Login unique to them, however when troubleshooting you have that much more information to help resolve the problem.I would say leave it as is and focus on ways to make the login creation and dropping process more efficient.
I knew that unique logins mapped to DB Users would provide extensive tracking and auditing capabilities but with just under a thousand users I was wondering if perhaps Multiple DB Users to Logins might be feasible.
I am not sure if you have a misconception going, and it may just be a difference in terminology I am picking up on but I am wondering...to make sure we're on the same page, know that a Database User can map to zero or one Server Login, and that a Server Login can map to one and only one User in a particular Database, i.e. you cannot have one Server Login map to multiple Database Users or have multiple Server Logins map to the same Database User.
The DB server that’s runs this has no other application DB's or anything like it to manage save for my own DBA Admin database (one on all of my servers to keep admin functions and anything I want to use but store outside of a regular database) and so all the users and logins would be for just this DB. I was pondering the idea of creating 1 login for each remote site (any location off site that connects in via VPN regularly during business hours) and assigning multiple DB Users to that.
While having trackability from DB session to person is a great thing to have when troubleshooting, sometimes invaluable, now that you say you have thousands of unique users it has me thinking about connection pooling. Each Server Login will end up with a connection in the pool, whereas if all persons in a logical grouping used the same Server Login they could all share a small number of connections in the pool. i.e. you are not getting the most out any connection pooling technology available at the application layer. That should be weighed appropriately.
How many of those thousand users are concurrent at any one time? That will be a more important indicator than the total number of unique users with logins to weigh when considering pooling.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply