Determine What User Created Objects in SQL Server

Determine what user created objects in SQL Server

The answer is "no, you probably can't".

While there is stuff in there that might say who created a given object, there are a lot of "ifs" behind them. A quick (and not necessarily complete) review:

sys.objects (and thus sys.tables, sys.procedures, sys.views, etc.) has column principal_id. This value is a foreign key that relates to the list of database users, which in turn can be joined with the list of SQL (instance) logins. (All of this info can be found in further system views.)

But.

A quick check on our setup here and a cursory review of BOL indicates that this value is only set (i.e. not null) if it is "different from the schema owner". In our development system, and we've got dbo + two other schemas, everything comes up as NULL. This is probably because everyone has dbo rights within these databases.

This is using NT authentication. SQL authentication probably works much the same. Also, does everyone have and use a unique login, or are they shared? If you have employee turnover and domain (or SQL) logins get dropped, once again the data may not be there or may be incomplete.

You can look this data over (select * from sys.objects), but if principal_id is null, you are probably out of luck.

Does SQL Server store the name of the user who originally created a User Defined Function?

Partially, if object was created not by the database owner, otherwise you will see a dbo as the creator:

USE db;
SELECT so.name AS [ObjectName],
       su.name AS [UserName]
FROM sysobjects so
    JOIN sysusers su
        ON su.uid = so.uid
WHERE so.id = OBJECT_ID('FunctionName');

---

Update: Martin Smith comment is correct. Starting from SQL Server 2005:

By default, when developers create objects in a schema, the objects
are owned by the security principal that owns the schema, not the
developer. Object ownership can be transferred with ALTER
AUTHORIZATION Transact-SQL statement.

Therefore, if ownership specified explicitly, it can be retrieved from sys.objects:

ALTER AUTHORIZATION ON OBJECT::myFunction TO someUser

-- returns non-null value after transfer:
SELECT principal_id FROM sys.objects WHERE OBJECT_ID = OBJECT_ID('myFunction')

Otherwise, the newly created object is owned by "schema owner". So there is no way to query who was really the initial owner/creator..

As the last resort a suggestion of @John Cappelletti. His link to similar topic has an answer:

If the object was recently created, you can check the Schema Changes
History report, within the SQL Server Management Studio, which
"provides a history of all committed DDL statement executions within
the Database recorded by the default trace":

How to Know who creates (host name) a database in sql server?

I'd use extended events for this:

CREATE EVENT SESSION [Create Database] ON SERVER 
ADD EVENT sqlserver.database_created(
    ACTION(sqlserver.client_hostname)) 
ADD TARGET package0.ring_buffer;
GO
ALTER EVENT SESSION [Create Database] ON SERVER STATE = START;

I leave the consumption of the target data as an exercise for the reader.

Determine Which Objects Reference a Table in SQL Server

If you need to find database objects (e.g. tables, columns, triggers) by name - have a look at the FREE Red-Gate tool called SQL Search which does this - it searches your entire database for any kind of string(s).

It's a great must-have tool for any DBA or database developer - did I already mention it's absolutely FREE to use for any kind of use??

Find recent object changes in SQL Server Database

Query the sys.objects table to find the objects that changed and filter by modify_date and type; U = User table, P = Stored procedure.

select * 
from sys.objects 
where (type = 'U' or type = 'P') 
  and modify_date > dateadd(m, -3, getdate()) 

This approach will tell you what objects have changed, but not the specific changes.

How to identify system objects when viewing list of SQL Server database objects?

This works on my SQL Server 2008 R2 install. I don't see much at all except for user databases

SELECT 
    *
FROM
   sys.objects
WHERE
   OBJECTPROPERTY(object_id, 'IsMSShipped') = 0

You can change sys.objects to say, sys.tables and it still works, or use the "type" column to filter. Or use OBJECTPROPERTY(object_id, 'IsProcedure') etc.

Note: it's sys.objects in SQL Server 2005+

Note 2: OBJECTPROPERTY will work for SQL Server 2000 too:

SELECT 
    *
FROM
   sysobjects
WHERE
   OBJECTPROPERTY(id, 'IsMSShipped') = 0

Find an object in SQL Server (cross-database)

There is a schema called INFORMATION_SCHEMA schema which contains a set of views on tables from the SYS schema that you can query to get what you want.

A major upside of the INFORMATION_SCHEMA is that the object names are very query friendly and user readable. The downside of the INFORMATION_SCHEMA is that you have to write one query for each type of object.

The Sys schema may seem a little cryptic initially, but it has all the same information (and more) in a single spot.

You'd start with a table called SysObjects (each database has one) that has the names of all objects and their types.

One could search in a database as follows:

Select [name] as ObjectName, Type as ObjectType
From Sys.Objects
Where 1=1
    and [Name] like '%YourObjectName%'

Now, if you wanted to restrict this to only search for tables and stored procs, you would do

Select [name] as ObjectName, Type as ObjectType
From Sys.Objects
Where 1=1
    and [Name] like '%YourObjectName%'
    and Type in ('U', 'P')

If you look up object types, you will find a whole list for views, triggers, etc.

Now, if you want to search for this in each database, you will have to iterate through the databases. You can do one of the following:

If you want to search through each database without any clauses, then use the sp_MSforeachdb as shown in an answer here.

If you only want to search specific databases, use the "USE DBName" and then search command.

You will benefit greatly from having it parameterized in that case. Note that the name of the database you are searching in will have to be replaced in each query (DatabaseOne, DatabaseTwo...). Check this out:

Declare @ObjectName VarChar (100)

Set @ObjectName = '%Customer%'

Select 'DatabaseOne' as DatabaseName, [name] as ObjectName, Type as ObjectType
From DatabaseOne.Sys.Objects
Where 1=1
    and [Name] like @ObjectName
    and Type in ('U', 'P')

UNION ALL

Select 'DatabaseTwo' as DatabaseName, [name] as ObjectName, Type as ObjectType
From DatabaseTwo.Sys.Objects
Where 1=1
    and [Name] like @ObjectName
    and Type in ('U', 'P')

UNION ALL

Select 'DatabaseThree' as DatabaseName, [name] as ObjectName, Type as ObjectType
From DatabaseThree.Sys.Objects
Where 1=1
    and [Name] like @ObjectName
    and Type in ('U', 'P')

---

Related Topics