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

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

"Laurentiu Cristofor [MSFT]" wrote:

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: 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)
  • Re: Database security design with ASP.net and form-based authentication
    ... Since you already have forms-based security, why not use a single SQL login ... for all database access? ... data entry, guest/view only, admin, report viewer. ... so I'm using SQL Server authentication. ...
    (microsoft.public.sqlserver.security)