SQL script to list particular domain login and objects it owns ?

Question

Hello, have a particular domain login within SQL Server and would like to disable it but would like to be safe first and check if it owns any objects to avoid breaking anything.

Is there a script that can list all objects like DBs, Jobs, etc... that it owns for me to review?

Thanks in advance.

Answer 1

If all you want to do is to disable it, just go ahead. Merely disabling will not break anything. (Dropping it is another matter.)

Answer 2

Hi @techresearch7777777 ,

Try this :

declare @Sql nvarchar(max), @columnName VARCHAR(30) = 's%';  
      
 select @Sql =   
      
      
 (select '  
 SELECT ' + QUOTENAME(name,'''') + ' as [DB Name], table_schema as [Schema Name], table_name as [Table Name], [column_name] from ' +   
 QUOTENAME(Name) + '.INFORMATION_SCHEMA.columns  
 WHERE column_name like @columnName  
 order by [DB Name],[Schema Name], [Table Name], [column_name];' from sys.databases  
 order by name FOR XML PATH(''), TYPE).value('.', 'varchar(max)')  
 PRINT @SQL;  
      
 EXECUTE sp_executeSQL @SQL, N'@columnName varchar(30)', @columnName = @columnName;  

Best regards,
Niko

----------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Thanks NikoXuMSFT for your reply and info, much appreciated.

Sorry but am confused on deciphering the output.

Was looking for something like:

LoginName - ObjectName - Permissions

---------------------------------------------

Domain\joe - CustomerTable - Owner
Domain\joe - SQLAgentJob_Insert_CustomersTable - Owner
etc...

Answer 4

Hi @techresearch7777777 ,

How about this:

;with objects_cte as  
(  
    select  
        o.name,  
        o.type_desc,  
        case  
            when o.principal_id is null then s.principal_id  
            else o.principal_id  
        end as principal_id  
    from sys.objects o  
    inner join sys.schemas s  
    on o.schema_id = s.schema_id  
    where o.is_ms_shipped = 0  
    and o.type in ('U', 'FN', 'FS', 'FT', 'IF', 'P', 'PC', 'TA', 'TF', 'TR', 'V')  
)  
select  
    cte.name,  
    cte.type_desc,  
    dp.name  
from objects_cte cte  
inner join sys.database_principals dp  
on cte.principal_id = dp.principal_id  
where dp.name = 'YourUser';  

This will get objects that are owned by your particular user (substitute 'YourUser' of course). The types of objects this query pulls are:

FN = SQL scalar function
FS = Assembly (CLR) scalar-function
FT = Assembly (CLR) table-valued function
IF = SQL inline table-valued function
P = SQL Stored Procedure
PC = Assembly (CLR) stored-procedure
TA = Assembly (CLR) DML trigger
TF = SQL table-valued-function
TR = SQL DML trigger
U = Table (user-defined)
V = View

Best regards,
Niko

----------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 5

Sorry I meant in my example CustomerDB (not CustomerTable).

Looking for particular Domain SQL Login ownership on a SQL Server level like DBs, SQL Agent Jobs, Linked Servers, etc...

Thanks bunch.