http://www.sqlservercentral.com/blogs/brian_kelley/2009/02/24/determining-if-a-server-principal-owns-database-objects/

Printed 2014/09/30 04:12AM

Determining if a Server Principal Owns Database Objects

2009/02/24

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:

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


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.