Testing System Stored Procedure

Last Post 23 Mar 2007 12:48 AM by SQLUSA. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
rhuyck
New Member
New Member

--
27 Nov 2006 09:14 AM
In SqlServer 2005 a drop login command or sp_droplogin will drop the login even if the login is a user in a database. I have a stored procedure that will create and remove users which is called from a Proprietary Admin tool to manage users.
In 2000, the sp_droplogin would throw an error and I could capture that error and report that the user was dropped from the database but that the login could not be removed.

Is there a way to get a result from sp_helplogins that will anwer the question:

Is this login name a user in any database with this instance?

Or is there another method for doing this?

Thanks
alanro
New Member
New Member

--
23 Mar 2007 12:08 AM
there is probably a user in a database w/ the same suid as the login you are
trying to drop. Sometimes if you've dumped/loaded from different servers,
the syslogins & sysusers tables can get out of sync.

Try:

select suid,uid,name from dbname..sysusers where suid = X

If it comes back with any rows, just drop that user from that particular
database.
SQLUSA
New Member
New Member

--
23 Mar 2007 12:48 AM
You can try this:

exec sp_helplogins 'DELLSTAR\dbAdmin'


Kalman Toth, Database Architect
SQLUSA: http://www.sqlusa.com
You are not authorized to post a reply.

Acceptable Use Policy