Bug in Enterprise Manager?

[New MSSQL DBA]

Hi all, met with something very scary and want to confirm if anyone
else have met this possible bug with EM?

Got a production DB, say DB_A with quite a few users in it. this day,
when I look into the EM-->management-->Current Activity-->Locks/Object,
I saw a lot of locking on some objects belonging to a user, say User_A
but the problem is, this User_A doesn't own a thing in DB_A!! And even
if I drop this user from DB_A, the EM GUI still showing there are quite
a locks on objects owned by User_A.

And if use Query Analyzer to direct query on system tables, all are
fine, that is those locks are actually on another User_B, which is
fine.

So I've checked that the problem seems to lie in that User_B has a uid
8 in DB_A whereas User_A has the same uid (8) in master db. Once I
remove User_A from master db, then the EM GUI shows no locking at all
(though in system tables, there are still lockings in User_B's object).

So I guess it's a bug in EM but has anyone met with this kind of case
before?

[Mike Epprecht \(SQL MVP\)]

Hi

Not a bug in EM, you have a real security problem.

You user ID's do not match across all your databases. Must have been that a
DB was restored from another server.

You are going to have to remove all the users from the non-system DB's and
then grant them access and permissions again. The only thing you can trust
is syslogins and sysusers in master, model, msdb and tempdb. The rest are
not reliable..

Regards
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: mi**@epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

"New MSSQL DBA" <bo*******@gmail.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

Hi all, met with something very scary and want to confirm if anyone
else have met this possible bug with EM?

Got a production DB, say DB_A with quite a few users in it. this day,
when I look into the EM-->management-->Current Activity-->Locks/Object,
I saw a lot of locking on some objects belonging to a user, say User_A
but the problem is, this User_A doesn't own a thing in DB_A!! And even
if I drop this user from DB_A, the EM GUI still showing there are quite
a locks on objects owned by User_A.

And if use Query Analyzer to direct query on system tables, all are
fine, that is those locks are actually on another User_B, which is
fine.

So I've checked that the problem seems to lie in that User_B has a uid
8 in DB_A whereas User_A has the same uid (8) in master db. Once I
remove User_A from master db, then the EM GUI shows no locking at all
(though in system tables, there are still lockings in User_B's object).

So I guess it's a bug in EM but has anyone met with this kind of case
before?

[Erland Sommarskog]

New MSSQL DBA (bo*******@gmail.com) writes:

Hi all, met with something very scary and want to confirm if anyone
else have met this possible bug with EM?

Got a production DB, say DB_A with quite a few users in it. this day,
when I look into the EM-->management-->Current Activity-->Locks/Object,
I saw a lot of locking on some objects belonging to a user, say User_A
but the problem is, this User_A doesn't own a thing in DB_A!! And even
if I drop this user from DB_A, the EM GUI still showing there are quite
a locks on objects owned by User_A.

Mike is plain wrong when he say there this not a bug in Enterprise Manager.
There is a bug, and I have known about for a long time. In difference to
you, I didn't run into it this way - I found it by looking at the code.

Here is the relevant part from sp_MSset_current_activity (a procedure
that exists only for Enterprise Manager):

select @stmt ='update ' + @locktab + ' set [Table] = name,
[ObjOwner] = user_name(uid) from ' + quotename(@lckdb, '[') +
'.[dbo].[sysobjects] where id = ' +
convert(nvarchar(10), @lckobjid) + ' and [Database] = ''' + @lckdb
+ ''' and [ObjID] = ' + convert(nvarchar(10), @lckobjid)
exec (@stmt)

The context is that the procedure have first found all processes and locks
and saves these in a temp table. Then it iterates over all locked objects
to translate the names in a look. The bug is the part

user_name(uid)

user_name() works in the current database, but here we examine some
other database. the programmer should have joined with
@lckdb + '.dbo.sysusers' instead.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[New MSSQL DBA]

How can uid of users in all the databases be the same??

Say I've got 5 logins, all of them have access to system database and
only 1 of them has access to DB_A and another 1 has access to DB_B,
then how is it possible that the users for these logins in different
database be of the same uid?

[New MSSQL DBA]

Thanks a lot, that's exactly my guess but I couldn't confirm it before.

Wow, I'd say that's a really junior mistake and as I've searched
Microsoft KB, it hasn't been documented as a bug or whatsoever and they
haven't fix it for such a long time.

[Erland Sommarskog]

New MSSQL DBA (bo*******@gmail.com) writes:

How can uid of users in all the databases be the same??

Say I've got 5 logins, all of them have access to system database and
only 1 of them has access to DB_A and another 1 has access to DB_B,
then how is it possible that the users for these logins in different
database be of the same uid?

Not really sure what you mean, but uid is a database-specific id, so id
17 in db_A can be user Ture and in db_B it's user Ingvar. Which may or
may not map to the same login.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp