Bug or Feature

I noticed something when using Management Studio that I don't know if it's a bug or a feature. So if you have a login that's mapped to a user name in several databases, and you delete the login from the server using Management Studio, the user name is still left in the databases. This happens on a SQL 2000 or 2005 instance.

If I use Enterprise Manager and do the same function, the login is removed from the server as well as all databases where it was a valid user.

Any ideas?

Thanks,

Jason

It is by design as the user may own objects in some database, and some of the DBs may be offline or read-only. When you drop the login, the corresponding user in any DB will become an “orphan user”, and the behavior will be similar to restoring a database with user information without corresponding logins.

The sysadmin will have the opportunity to cleanup the “orphan users” by changing ownership of the objects in any given database and deleting the user or simply by mapping the orphan user to a different login using sp_change_Users_login.

I hope I was able to answer your question.

-Raul Garcia

SDE/T

SQL Server Engine

|||

Okay, that explains it. Thanks for the info!

It would be nice if when the user owns an object, SQL Server changes the value in some flag for example, and then when the login is dropped from the server, SQL Server looks at that flag and say if it is set to 0, then drop the user from all databases, but if it is set to 1, drop the login but not the user from the database. This way it is less maintenance for the DBA to come through and clean up orphan users. Just my 2 cents.

Jason

Bug in DBCC SQLPerf ?

I've noticed that running DBCC SQLPERF(Threads) shows the login name
truncated to 10 characters, which is a tiny problem if you use Windows
authentication, as all you seem to get is the first 10 characters of the
domain name. This leaves you guessing as to which pesky user is using all
that resource. Anybody know of any useful workarounds?
Thanks.Having thought about it a little harder, I can get the desired result by
loading the data from DBCC SQLPERF(Threads) and sp_who into #tables and
joining them on their respective SPIDs. I suppose the only danger is that
the SPIDS change user between the two calls.
"BarryC" wrote:
> I've noticed that running DBCC SQLPERF(Threads) shows the login name
> truncated to 10 characters, which is a tiny problem if you use Windows
> authentication, as all you seem to get is the first 10 characters of the
> domain name. This leaves you guessing as to which pesky user is using all
> that resource. Anybody know of any useful workarounds?
> Thanks.|||Why use DBCC SQLPERF(THREADS) which is undocumented and unsupported if you
have all information in a documented table (sysprocesses)?
select spid as [Spid],
kpid as [Thread ID],
loginame as [LoginName],
status as [Status],
physical_io as [IO],
cpu as [CPU],
memusage as [MemUsage]
from master.dbo.sysprocesses
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2005 All rights reserved.
"BarryC" <BarryC@.discussions.microsoft.com> wrote in message
news:A4D03A1F-6CAF-4881-82F5-7F6BC8BEDF3A@.microsoft.com...
> Having thought about it a little harder, I can get the desired result by
> loading the data from DBCC SQLPERF(Threads) and sp_who into #tables and
> joining them on their respective SPIDs. I suppose the only danger is that
> the SPIDS change user between the two calls.
> "BarryC" wrote:
>> I've noticed that running DBCC SQLPERF(Threads) shows the login name
>> truncated to 10 characters, which is a tiny problem if you use Windows
>> authentication, as all you seem to get is the first 10 characters of the
>> domain name. This leaves you guessing as to which pesky user is using
>> all
>> that resource. Anybody know of any useful workarounds?
>> Thanks.|||A perfectly reasonable question to which I'm sure there is an excellent
answer. Just makes me feel like a man, I guess.
Thanks, though - you're quite right of course...
"Gert E.R. Drapers" wrote:
> Why use DBCC SQLPERF(THREADS) which is undocumented and unsupported if you
> have all information in a documented table (sysprocesses)?
> select spid as [Spid],
> kpid as [Thread ID],
> loginame as [LoginName],
> status as [Status],
> physical_io as [IO],
> cpu as [CPU],
> memusage as [MemUsage]
> from master.dbo.sysprocesses
>
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> You assume all risk for your use.
> Copyright © SQLDev.Net 1991-2005 All rights reserved.
> "BarryC" <BarryC@.discussions.microsoft.com> wrote in message
> news:A4D03A1F-6CAF-4881-82F5-7F6BC8BEDF3A@.microsoft.com...
> > Having thought about it a little harder, I can get the desired result by
> > loading the data from DBCC SQLPERF(Threads) and sp_who into #tables and
> > joining them on their respective SPIDs. I suppose the only danger is that
> > the SPIDS change user between the two calls.
> >
> > "BarryC" wrote:
> >
> >> I've noticed that running DBCC SQLPERF(Threads) shows the login name
> >> truncated to 10 characters, which is a tiny problem if you use Windows
> >> authentication, as all you seem to get is the first 10 characters of the
> >> domain name. This leaves you guessing as to which pesky user is using
> >> all
> >> that resource. Anybody know of any useful workarounds?
> >>
> >> Thanks.
>
>

Bug in DBCC SQLPerf ?

I've noticed that running DBCC SQLPERF(Threads) shows the login name
truncated to 10 characters, which is a tiny problem if you use Windows
authentication, as all you seem to get is the first 10 characters of the
domain name. This leaves you guessing as to which pesky user is using all
that resource. Anybody know of any useful workarounds?
Thanks.
Having thought about it a little harder, I can get the desired result by
loading the data from DBCC SQLPERF(Threads) and sp_who into #tables and
joining them on their respective SPIDs. I suppose the only danger is that
the SPIDS change user between the two calls.
"BarryC" wrote:

> I've noticed that running DBCC SQLPERF(Threads) shows the login name
> truncated to 10 characters, which is a tiny problem if you use Windows
> authentication, as all you seem to get is the first 10 characters of the
> domain name. This leaves you guessing as to which pesky user is using all
> that resource. Anybody know of any useful workarounds?
> Thanks.
|||Why use DBCC SQLPERF(THREADS) which is undocumented and unsupported if you
have all information in a documented table (sysprocesses)?
select spid as [Spid],
kpid as [Thread ID],
loginame as [LoginName],
status as [Status],
physical_io as [IO],
cpu as [CPU],
memusage as [MemUsage]
from master.dbo.sysprocesses
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"BarryC" <BarryC@.discussions.microsoft.com> wrote in message
news:A4D03A1F-6CAF-4881-82F5-7F6BC8BEDF3A@.microsoft.com...[vbcol=seagreen]
> Having thought about it a little harder, I can get the desired result by
> loading the data from DBCC SQLPERF(Threads) and sp_who into #tables and
> joining them on their respective SPIDs. I suppose the only danger is that
> the SPIDS change user between the two calls.
> "BarryC" wrote:
|||A perfectly reasonable question to which I'm sure there is an excellent
answer. Just makes me feel like a man, I guess.
Thanks, though - you're quite right of course...
"Gert E.R. Drapers" wrote:

> Why use DBCC SQLPERF(THREADS) which is undocumented and unsupported if you
> have all information in a documented table (sysprocesses)?
> select spid as [Spid],
> kpid as [Thread ID],
> loginame as [LoginName],
> status as [Status],
> physical_io as [IO],
> cpu as [CPU],
> memusage as [MemUsage]
> from master.dbo.sysprocesses
>
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> You assume all risk for your use.
> Copyright ? SQLDev.Net 1991-2005 All rights reserved.
> "BarryC" <BarryC@.discussions.microsoft.com> wrote in message
> news:A4D03A1F-6CAF-4881-82F5-7F6BC8BEDF3A@.microsoft.com...
>
>

Bug in DBCC SQLPerf ?

I've noticed that running DBCC SQLPERF(Threads) shows the login name
truncated to 10 characters, which is a tiny problem if you use Windows
authentication, as all you seem to get is the first 10 characters of the
domain name. This leaves you guessing as to which pesky user is using all
that resource. Anybody know of any useful workarounds?
Thanks.Having thought about it a little harder, I can get the desired result by
loading the data from DBCC SQLPERF(Threads) and sp_who into #tables and
joining them on their respective SPIDs. I suppose the only danger is that
the SPIDS change user between the two calls.
"BarryC" wrote:

> I've noticed that running DBCC SQLPERF(Threads) shows the login name
> truncated to 10 characters, which is a tiny problem if you use Windows
> authentication, as all you seem to get is the first 10 characters of the
> domain name. This leaves you guessing as to which pesky user is using all
> that resource. Anybody know of any useful workarounds?
> Thanks.|||Why use DBCC SQLPERF(THREADS) which is undocumented and unsupported if you
have all information in a documented table (sysprocesses)?
select spid as [Spid],
kpid as [Thread ID],
loginame as [LoginName],
status as [Status],
physical_io as [IO],
cpu as [CPU],
memusage as [MemUsage]
from master.dbo.sysprocesses
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"BarryC" <BarryC@.discussions.microsoft.com> wrote in message
news:A4D03A1F-6CAF-4881-82F5-7F6BC8BEDF3A@.microsoft.com...[vbcol=seagreen]
> Having thought about it a little harder, I can get the desired result by
> loading the data from DBCC SQLPERF(Threads) and sp_who into #tables and
> joining them on their respective SPIDs. I suppose the only danger is that
> the SPIDS change user between the two calls.
> "BarryC" wrote:
>|||A perfectly reasonable question to which I'm sure there is an excellent
answer. Just makes me feel like a man, I guess.
Thanks, though - you're quite right of course...
"Gert E.R. Drapers" wrote:

> Why use DBCC SQLPERF(THREADS) which is undocumented and unsupported if you
> have all information in a documented table (sysprocesses)?
> select spid as [Spid],
> kpid as [Thread ID],
> loginame as [LoginName],
> status as [Status],
> physical_io as [IO],
> cpu as [CPU],
> memusage as [MemUsage]
> from master.dbo.sysprocesses
>
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> You assume all risk for your use.
> Copyright ? SQLDev.Net 1991-2005 All rights reserved.
> "BarryC" <BarryC@.discussions.microsoft.com> wrote in message
> news:A4D03A1F-6CAF-4881-82F5-7F6BC8BEDF3A@.microsoft.com...
>
>