Re: SQL Server 2005: Checking if Login is connected to any databas

No, there is no such stored procedure. There is no way to tell if a login
can be really dropped - there might be databases that refer to it that are
detached at the moment you do your check. Writing a procedure to check all
databases is the quickest way, but it will not cover the issue above.

Why do you want to make this check? I may be able to suggest a better
alternative if I understand what you are trying to accomplish.

Thanks

--
Laurentiu Cristofor [MSFT]
Software Design Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/

This posting is provided "AS IS" with no warranties, and confers no rights.

"Sebitti" <sebittionline@xxxxxxxxxxxxxxxxx> wrote in message
news:EB9F9ACB-35EC-4C77-A027-2B84AE95F444@xxxxxxxxxxxxxxxx
Hi,

SQL Server 2005 allows dropping login that has mapped users in database. I
need a way to check if login is mapped to any database before I call
sp_droplogin. Is there any stored procedure that does this check?

BR
Johanna Turku

"Uri Dimant" wrote:

Hi
BOL says
A login cannot be dropped while it is logged in. A login that owns any
securable, server-level object, or SQL Server Agent job cannot be
dropped.

You can drop a login to which database users are mapped; however, this
will
create orphaned users. For more information, see Troubleshooting Orphaned
Users.

select * from sys.server_principals

select * from sys.database_principals

select * from sys.user_token





"Sebitti" <sebittionline@xxxxxxxxxxxxxxxxx> wrote in message
news:0AE8D1C4-165B-4115-A2DA-63F0A64C4AC8@xxxxxxxxxxxxxxxx
Hi,

We have an application that allows users to add and drop logins. In Sql
Server 2000 it was not allowed to drop a login that was linked in some
database but in 2005 this is allowed. We need a way to check if login
can
really be dropped.

What is the best way to check if a login is linked to any database? Any
stored procedure that does this check? Only way I can think of is to
list
all
databases and check their users one by one... i hope there is a quicker
way
to do this.

BR
Johanna Turku





.

Relevant Pages

  • Re: How to get list of EventClasses in MSSQLServer2000
    ... statement inside a stored procedure has completed.') ... SQL Server statement or stored procedure.') ... Plan','Displays the plan tree of the Transact-SQL statement executed.') ... Login','Occurs when a SQL Server login is added or removed; ...
    (microsoft.public.sqlserver.security)
  • Re: System Administrator Implied Permissions
    ... > sa login, it assigns it the System Administrator fixed ... > Now, given this, why does SQL Server ... in each database is always a member of the public and db_owner roles. ... Other sysadmin role members have the exact same ...
    (microsoft.public.sqlserver.security)
  • Re: No db access after publishing web site
    ... GRANT UPDATE TO ... If I detach and attach this database on a different PC (according that PC ... Cannot open database "pago" requested by the login. ... Are you detaching/attaching the SQL Server Express database correctly ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: cannot login to the db after...
    ... Jasper Smith (SQL Server MVP) ... I have created a new database, "db_1", using the "sa" ... I then created a new login, "sqluser1" and gave ...
    (microsoft.public.sqlserver.security)
  • Re: Cant view merge agent properties (trying again)
    ... In the List of Actions for the Snapshot Agent History I see this repeated: ... every single database listed. ... So, just now, I went to computername\Administrator Login ID (because it's ... On the computer running SQL Server, ...
    (microsoft.public.sqlserver.replication)