确定哪些用户在 SQL Server 中创建了对象
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1255947/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Determine what user created objects in SQL Server
提问by bcasp
I'm trying to go through our development DB right now and clean up some of the old test procs/tables. Is it possible to determine what user created objects in a SQL Server 2005 database? If so, how would I go about finding that information?
我现在正在尝试通过我们的开发数据库并清理一些旧的测试过程/表。是否可以确定哪些用户在 SQL Server 2005 数据库中创建了对象？如果是这样，我将如何查找该信息？
Edit: Just wanted to clarify that the objects in question already exist. Setting up auditing and triggers probably wouldn't do me much good. I guess I was mostly looking for a way to use the system tables/views to get to the information.
编辑：只是想澄清有问题的对象已经存在。设置审计和触发器可能对我没有多大好处。我想我主要是在寻找一种使用系统表/视图来获取信息的方法。
采纳答案by Philip Kelley
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.)
sys.objects（以及 sys.tables、sys.procedures、sys.views 等）具有 principal_id 列。该值是一个与数据库用户列表相关的外键，该列表又可以与 SQL（实例）登录列表连接。（所有这些信息都可以在进一步的系统视图中找到。）
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.
在此处快速检查我们的设置并粗略查看 BOL 表明仅在“与架构所有者不同”时才设置此值（即不为空）。在我们的开发系统中，我们有 dbo + 两个其他模式，一切都显示为 NULL。这可能是因为每个人在这些数据库中都拥有 dbo 权限。
This is using NT authentication. SQL authentication probably works much the same. Also, does everyone have and usea 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.
这是使用 NT 身份验证。SQL 身份验证的工作原理可能大致相同。此外，每个人都拥有并使用唯一的登录名，还是共享？如果您有员工流失并且域（或 SQL）登录被删除，那么数据可能不存在或可能不完整。
You can look this data over (select * from sys.objects), but if principal_id is null, you are probably out of luck.
您可以查看这些数据（从 sys.objects 中选择 *），但如果 principal_id 为 null，您可能就不走运了。
回答by Guillermo Gutiérrez
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":
如果对象是最近创建的，您可以在 SQL Server Management Studio 中查看架构更改历史报告，该报告“提供了默认跟踪记录的数据库中所有已提交 DDL 语句执行的历史记录”：
You then can search for the create statements of the objects. Among all the information displayed, there is the login name of whom executed the DDL statement.
然后您可以搜索对象的创建语句。在显示的所有信息中，有执行 DDL 语句的登录名。
回答by CruelIO
If each user has its own SQL Server login you could try this
如果每个用户都有自己的 SQL Server 登录名，你可以试试这个
select
so.name, su.name, so.crdate
from
sysobjects so
join
sysusers su on so.uid = su.uid
order by
so.crdate
回答by Jonathan
If you need a small and specific mechanism, you can search for DLL Triggersinfo.
如果您需要一个小而特定的机制，您可以搜索DLL 触发器信息。
