This question comes up a lot in the forums: "How do I know if the login owns any objects?" Usually the reason this question is asked is to be able to find those objects and change the ownership so the login/server principal could be dropped. The first key is to map the server principal to the database principals in the various databases. Once you understand how that works, it's just a matter of seeing what objects are owned by what database principals. There are several cases where this could occur:

• The database principal owns an object (SQL Server 2000 or below) or has been granted ownership using ALTER AUTHORIZATION on the object (SQL Server 2005 and above).

• The database principal owns a schema.

• The database principal owns an assembly.

• The database principal owns an asymmetric key.

• The database principal owns a symmetric key.

• The database principal owns a certificate.

There may be other cases, but those are the ones I most commonly come across. The first one doesn't occur much any more with SQL Server 2005/2008, but I've included it because if you look at sys.all_objects (both user and system objects) or sys.objects (user objects only), you see the principal_id column, and it explains how that might be populated. So if we put all that together, it's a simple matter to query the various catalog views, dump the result to a temporary table, and then return the whole table. Keep in mind that this may take some time to run because effectively there are six queries being run in each database, all of which involve the use of joins.

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

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

CREATE TABLE #Objects ( 
  DatabaseName sysname, 
  UserName sysname,
  ObjectName sysname,
  ObjectType NVARCHAR(60)); 

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

OPEN cursDatabases; 

FETCH NEXT FROM cursDatabases INTO @DatabaseName; 

WHILE (@@FETCH_STATUS = 0) 
BEGIN 

   -- Checking for cases in sys.objects where ALTER AUTHORIZATION has been used
   SET @SQL = 'INSERT INTO #Objects (DatabaseName, UserName, ObjectName, ObjectType) 
             SELECT ''' + @DatabaseName + ''', dp.name, so.name, so.type_desc 
             FROM [' + @DatabaseName + '].sys.database_principals dp
               JOIN [' + @DatabaseName + '].sys.objects so 
                 ON dp.principal_id = so.principal_id
             WHERE dp.sid = ''' + @sid + ''';'; 
                 
   EXEC(@SQL); 

   -- Checking for cases where the login owns one or more schema
   SET @SQL = 'INSERT INTO #Objects (DatabaseName, UserName, ObjectName, ObjectType) 
             SELECT ''' + @DatabaseName + ''', dp.name, sch.name, ''SCHEMA'' 
             FROM [' + @DatabaseName + '].sys.database_principals dp
               JOIN [' + @DatabaseName + '].sys.schemas sch 
                 ON dp.principal_id = sch.principal_id
             WHERE dp.sid = ''' + @sid + ''';'; 
   EXEC(@SQL);

   -- Checking for cases where the login owns assemblies
   SET @SQL = 'INSERT INTO #Objects (DatabaseName, UserName, ObjectName, ObjectType) 
             SELECT ''' + @DatabaseName + ''', dp.name, assemb.name, ''Assembly'' 
             FROM [' + @DatabaseName + '].sys.database_principals dp
               JOIN [' + @DatabaseName + '].sys.assemblies assemb
                 ON dp.principal_id = assemb.principal_id
             WHERE dp.sid = ''' + @sid + ''';'; 
   EXEC(@SQL);
   
   -- Checking for cases where the login owns asymmetric keys
   SET @SQL = 'INSERT INTO #Objects (DatabaseName, UserName, ObjectName, ObjectType) 
             SELECT ''' + @DatabaseName + ''', dp.name, asym.name, ''Asymm. Key'' 
             FROM [' + @DatabaseName + '].sys.database_principals dp
               JOIN [' + @DatabaseName + '].sys.asymmetric_keys asym 
                 ON dp.principal_id = asym.principal_id
             WHERE dp.sid = ''' + @sid + ''';'; 
   EXEC(@SQL);
       
   -- Checking for cases where the login owns symmetric keys
   SET @SQL = 'INSERT INTO #Objects (DatabaseName, UserName, ObjectName, ObjectType) 
             SELECT ''' + @DatabaseName + ''', dp.name, sym.name, ''Symm. Key'' 
             FROM [' + @DatabaseName + '].sys.database_principals dp
               JOIN [' + @DatabaseName + '].sys.symmetric_keys sym 
                 ON dp.principal_id = sym.principal_id
             WHERE dp.sid = ''' + @sid + ''';'; 
   EXEC(@SQL);
          
   -- Checking for cases where the login owns certificates
   SET @SQL = 'INSERT INTO #Objects (DatabaseName, UserName, ObjectName, ObjectType) 
             SELECT ''' + @DatabaseName + ''', dp.name, cert.name, ''Certificate'' 
             FROM [' + @DatabaseName + '].sys.database_principals dp
               JOIN [' + @DatabaseName + '].sys.certificates cert
                 ON dp.principal_id = cert.principal_id
             WHERE dp.sid = ''' + @sid + ''';'; 
   EXEC(@SQL);
          
   FETCH NEXT FROM cursDatabases INTO @DatabaseName; 
END 

CLOSE cursDatabases; 
DEALLOCATE cursDatabases; 

SELECT * FROM #Objects; 

DROP TABLE #Objects;