SQL Server Performance Forum – Threads Archive

delteting login still leaves orphaned db users

Does anyone have any thoughts as to why Microsoft didn’t fix this in SQL 2005? Whould a DBA ever want orphaned db users in the database after deleting a login?

Hi, the reason may be, One Login can be mapped to many users. Login to Users relation is not one to one but one to many. so your argument have a counter argument; if the relation was One to One then it make sense, but it is one to many. One login may or may not related to many users in many database. So, in your case system has to delete all related users from all databases. So what about the Schema owned by these users in different database. That has to be transferred to whom. Who will decided that, system can not decided. So here you will have orphaned Schemas. So , creating /deleting Login/users should be a well documented and planned process. To be in safer side , what I feel is this is the better architecture. From SQL 2005 onwards the introduction of schema has provided lot of flexibility for the administrators. This is purely based on my understanding , I would like to know others views also. Madhu

Indeed, this is a common problem, and one long experienced by DBAs in all versions of SQL Server. It is also a common gotcha for novice SQL Server DBAs who can’t figure out what is going on. I have no explanation why this problem exists, but it does, along with many other security gotchas, such as when database IDs are orphaned when a database is moved from one server to another. Its just one of those problems that never seems to go away, and keeps DBAs from doing more fun work. —————————–
Brad M. McGehee, SQL Server MVP

same goes for views when tables are dropped, although there it is considered to be a nice feature because at maintenance time we only want to toutch the actual objects that change due to the implementation. (I hope this is still clear to follow [?] )
At first usage time the view gets recompiled and fails if nolonger valid.
Is this a problem … ?
when it fails … yes [B)]
… else ….. maybe not [:I]

At least in 2005 you get a warning/reminder dialog if you delete logins via SSMS….

I believe it is a good work for DBA to take care of such things and not to leave everything to the system, this way DBA might know who is leaving and having issues with access. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

If everthing works as it suppose to then who needs DBAs<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Mohammed U.

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by MohammedU If everthing works as it suppose to then who needs DBAs<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Mohammed U. <hr height="1" noshade id="quote"></blockquote id="quote"> [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] <pre id="code"> Thanks, Name ——— Dilli Grg (1 row(s) affected) </pre id="code">

]]>