A question on the forum asked how to find all the database mappings for a particular login. If you're on SQL Server 2000 or below, the tables you want to use are syslogins in the master database and sysusers in each database. The key to tying the login to a user is the SID. So if you know the login's SID, you set up your WHERE clause against sysusers and if you get a match, that login has a user mapping in that database. With SQL Server 2005 and above, you'll need to use sys.server_principals and sys.database_principals, respectively. The code I'll include shortly is the SQL Server 2005 and above example. 

Now this query fails to report access in three specific situations. That's because in these cases the login has implicit rights into the database. Those cases are:

• The login is a member of the sysadmin fixed server role. In this case the login comes in as dbo on all databases and you won't see a mapping in sysusers/sys.database_principals.

• The login has CONTROL SERVER rights (SQL Server 2005 and above). In this case, the login functions as above on all databases. 

• The login owns the database. In this case the login comes in as dbo for that particular database and you won't see a mapping in sysusers/sys.database_principals. * See EDIT: below.

Here's the query. If you've got a lot of databases, it may take a little bit to run.

DECLARE @LoginName sysname;
DECLARE @DatabaseName sysname;
DECLARE @SQL NVARCHAR(4000);
DECLARE @sid VARCHAR(255);

SET @LoginName = '<Login Name To Search On>';
SET @sid = (SELECT sid FROM sys.server_principals WHERE [name] = @LoginName);

CREATE TABLE #Mappings (
  DatabaseName sysname,
  UserName sysname);

DECLARE cursDatabases CURSOR FAST_FORWARD FOR SELECT name FROM sys.databases;

OPEN cursDatabases;

FETCH NEXT FROM cursDatabases INTO @DatabaseName;

WHILE (@@FETCH_STATUS = 0)
BEGIN
   SET @SQL = 'INSERT INTO #Mappings (DatabaseName, UserName)
             SELECT ''' + @DatabaseName + ''', name
             FROM [' + @DatabaseName + '].sys.database_principals
             WHERE sid = ''' + @sid + ''';';
                
   EXEC(@SQL);
   
   FETCH NEXT FROM cursDatabases INTO @DatabaseName;
END

CLOSE cursDatabases;
DEALLOCATE cursDatabases;

SELECT * FROM #Mappings;

DROP TABLE #Mappings;



EDIT: The last case I mentioned I was incorrect. If you check sys.database_principals, you'll see that the owner of the database does show as dbo. The dbo user is mapped to the login that owns the database. Now you can go database by database to find out who has access in such a manner or you can query sys.databases to determine the owner.