SQL Server 2005: Checking if Login is connected to any database

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sebitti - 23 Aug 2006 11:37 GMT

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

Reply to this Message

Uri Dimant - 23 Aug 2006 13:07 GMT

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

> Hi,
>
[quoted text clipped - 12 lines]
> BR
> Johanna Turku

Reply to this Message

Sebitti - 25 Aug 2006 12:33 GMT

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

> Hi
> BOL says
[quoted text clipped - 27 lines]
> > BR
> > Johanna Turku

Reply to this Message

Laurentiu Cristofor [MSFT] - 25 Aug 2006 22:55 GMT

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

Signature

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.

> Hi,
>
[quoted text clipped - 40 lines]
>> > BR
>> > Johanna Turku

Reply to this Message

Sebitti - 28 Aug 2006 12:05 GMT

Our application allows users to manage SQL Server logins and map them to
databases without having to install any separate tool to clients (like
management studio or enterprice manager). They can add logins and map them to
the current database and also edit users application-level rights at the same
UI (which are saved to a database table).

We also need to provide a function to delete users so that old users dont
hang in the logins list. But of course if the login is mapped to other
databases then we don't want to allow the drop. So I need a way to check if
dropping a login is ok or not.

We provide this functionality because users want to do all user-related
functions in the same place. Not so that they first have to user a different
tool to add / map / drop logins and then open our application and edit
application-level rights there.

BR
Johanna Turku

> 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
[quoted text clipped - 50 lines]
> >> > BR
> >> > Johanna Turku

Reply to this Message

Laurentiu Cristofor [MSFT] - 28 Aug 2006 21:41 GMT

I see. So you can write a procedure to test by iterating through all
available databases, but you should keep in mind that if a database is not
available, then you might remove a login that is still used.

Thanks

Signature

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.

> Our application allows users to manage SQL Server logins and map them to
> databases without having to install any separate tool to clients (like
[quoted text clipped - 82 lines]
>> >> > BR
>> >> > Johanna Turku

Reply to this Message