确定哪些用户在 SQL Server 中创建了对象 [英] Determine what user created objects in SQL Server

问题描述

我现在正在尝试检查我们的开发数据库并清理一些旧的测试过程/表.是否可以确定哪些用户在 SQL Server 2005 数据库中创建了对象?如果是这样，我将如何查找该信息?

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?

只是想澄清相关对象已经存在.设置审计和触发器可能对我没有多大好处.我想我主要是在寻找一种使用系统表/视图来获取信息的方法.

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.

推荐答案

答案是不，你可能不能".

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(以及 sys.tables、sys.procedures、sys.views 等)具有 principal_id 列.该值是一个与数据库用户列表相关的外键，该列表又可以与 SQL(实例)登录列表连接.(所有这些信息都可以在进一步的系统视图中找到.)

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.)

但是.

在此处快速检查我们的设置并粗略查看 BOL 表明该值仅在与架构所有者不同"时才设置(即不为空).在我们的开发系统中，我们有 dbo + 两个其他模式，一切都显示为 NULL.这可能是因为每个人都拥有这些数据库中的 dbo 权限.

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.

这是使用 NT 身份验证.SQL 身份验证的工作原理可能大致相同.另外，每个人都并使用一个唯一的登录名，还是共享?如果您的员工更替并且域(或 SQL)登录被删除，则数据可能不存在或可能不完整.

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.

您可以查看此数据(从 sys.objects 中选择 *)，但如果 principal_id 为 null，则您可能不走运.

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

这篇关于确定哪些用户在 SQL Server 中创建了对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！