Re: Incorrect user login information showing in Enterprise Manager



Eric W,

OK, you are using SQL 2005.

When using SSMS you delete a login, you will get this message: "Deleting
server logins does not delete the database users associated with the logins.
To complete the process, delete the users in each database. It may be
necessary to first transfer the ownership of schemas to new users."

But your question is" "Why does the user entry still know the login name?"
The answer is that it records the SID in the user. If you:
select * from sys.database_principals
you will see the SIDs of the logins used to create the users. In fact, if
you copy the SID for a deleted Windows login and paste it into:
SELECT SUSER_SNAME(0x0...9)
it will still return the name of the Windows Login. (In SQL Server 2000,
sysusers maintained the login's SID, but since the rows were usually deleted
automatically, you never saw this behavior manifested.)

To get rid of this, you must also drop the user yourself. Which may mean
that you must first drop that user's schema.

RLF

"EricW" <ewientzek@xxxxxxxxxxx> wrote in message
news:OaZe5gP2HHA.4476@xxxxxxxxxxxxxxxxxxxxxxx
I'm speaking about Managemenst Studio in SQL 2005.


"Russell Fields" <russellfields@xxxxxxxxxx> wrote in message
news:eQrTdlg1HHA.4500@xxxxxxxxxxxxxxxxxxxxxxx
Eric,

Since you say Enterprise Manager, I assume that you are using SQL Server
2000.

If I delete a login in SQL Server 2000 EM, it goes through and deletes
the users.

If I "sp_revokelogin 'domain1\x'" it still leaves the 'x' user behind,
and I can still see 'domain1\x' in EM if I was looking at it earlier.
But, once I refresh the EM user view I still see user 'x' but with a
blank login.

EM does have some latency in refreshing (refresh a couple of times may be
necessary). Could that be your problem?

If not, have you done anything out of the ordinary, such as restoring a
database from another server, or even another domain?

RLF

"EricW" <ewientzek@xxxxxxxxxxx> wrote in message
news:OA8zHbd1HHA.4672@xxxxxxxxxxxxxxxxxxxxxxx

When I check properties for database user x the login name says
domain1\x . If I delete that login from the server then look at the user
x's properties again it still says domain1\x in the login name!

How can this be fixed?









.



Relevant Pages

  • Re: SQL User SID format?
    ... When you move databases to another server, you can remap users to logins ... capability to remap a user to a login with new ALTER USER syntax - I ... The new ALTER USER syntax works for remapping both SQL and Windows ... this means you already have access to a SID from the syslogins catalog; ...
    (microsoft.public.sqlserver.security)
  • Re: SQL Server on XP Home Network
    ... The sa account is the system administrator "God" account witihin SQL ... This account is a SQL-Server login. ... You might be able to use Enterprise Manager to go in and create a new SQL ... Select the "SQL Server Authentication" ...
    (microsoft.public.sqlserver.setup)
  • Microsoft SQL server error:233
    ... Right click on the Instance name - Server Properties ... Restart the SQL Service. ... Login as an Administrator into the SqL Server ... A connection was successfully established with the server, ...
    (microsoft.public.sqlserver.connect)
  • Re: SQL Server 2000 Replication Agents Credentials
    ... the snapshot agent IS using sql login to connect to the ... The pull distribution agent IS using sql ... Do you know where SQL Server 2000 saves the sql ...
    (microsoft.public.sqlserver.replication)
  • Re: Can you update/process AS2005 cube with AMO and SQL authentica
    ... We have cases where the AS server and the Data Source server reside on 2 ... The data source is properly secured using SQL Server ... login and no serious client will accept the login with no password. ...
    (microsoft.public.sqlserver.olap)