SQL 2005 找出谁创建了视图

发布于 2024-07-24 07:24:58 字数 38 浏览 2 评论 0原文

SQL 是否存储有关谁最初创建视图或谁最后修改视图的任何信息？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

年华零落成诗 2024-07-31 07:24:58

现在已经太晚了，但是如果您使用的是 2008，您可以创建一个审核将跟踪未来的更改。

编辑：找到了！

    select p.name, v.* 
from sys.all_views v, sys.database_principals p, sys.schemas s
where p.principal_id = s.principal_id
and v.schema_id = s.schema_id
and v.name = 'your_view_name'

这将产生有关数据库中视图的许多有趣的详细信息，包括principal_id 列。加入 sys.database_principals 上的principal_id 作为用户名！

It's too late now, but if you were using 2008 you could create an audit that will track future changes.

EDIT: found it!

    select p.name, v.* 
from sys.all_views v, sys.database_principals p, sys.schemas s
where p.principal_id = s.principal_id
and v.schema_id = s.schema_id
and v.name = 'your_view_name'

This will produce a number of interesting details about the views in your database, including the column principal_id. Join with sys.database_principals on principal_id for the username!

回复收藏 0 原文

望喜 2024-07-31 07:24:58

我不确定是否有办法查看谁创建了视图，但是 sp_help 将为您提供有关视图创建时间的一些信息

sp_help viewMyView

sp_help 可以用于任何和所有数据库对象。

I am not sure if there is a way to see who created the view but sp_help will get you some information on when it was created

sp_help viewMyView

sp_help works on any and all database objects BTW.

回复收藏 0 原文

删除→记忆 2024-07-31 07:24:58

SQL Server 不存储有关谁创建或修改对象的显式信息。元数据目录中包含有关谁是给定对象的所有者或该对象属于哪个架构的信息：

select * from sys.objects where object_id = object_id('<object name>');

根据对象类型，principal_id 填充为所有者的数据库主体 ID，或者 schema_id 为使用对象所属架构的 Id 进行填充。所有模式都有一个所有者，并且可以从元数据目录中检索：

select * from sys.schemas

但是请注意，这些只会显示对象的所有者。所有者不一定是指创建或修改它的用户。可以在创建期间或创建后使用 ALTER AUTHORIZATION 声明，使得通过所有权进行的身份识别充其量是不可靠的。此外，sysadmin 角色的所有成员都映射到每个数据库中的同一数据库主体 dbo。

要正确识别创建对象的用户，您应该部署审核方法，但这需要事先部署审核。如果未部署审计，则可以在事后进行一些取证：

您可以深入研究日志文件，Paul Randal 他最近的博客中有一个示例。
您可以查看默认跟踪

SQL Server does not store explicit information about who created or modified an object. There is information in the metadata catalog about who is the owner of a given object, or to what schema does the object belong to:

select * from sys.objects where object_id = object_id('<object name>');

Depending on the object type either the principal_id is populated with the database principal ID of the owner, or the schema_id is populated with the Id of the schema to which the object belongs. All schemas have an owner and which can be retrieved from the metadata catalog:

select * from sys.schemas

However note that these will only reveal the owner of the object. The owner does not necessarily means the user that created it or modified it. Ownership of objects can be changed during creation or after creation with the ALTER AUTHORIZATION statement, making identification by ownership unreliable at best. Also all members of sysadmin role map to the same database principal, dbo, in every database.

To properly identify the the user that created an object you should deploy auditing methods, but that require prior deployment of the audit. Some forensics can be done after the fact if audit was not deployed: