SQL Server：找出哪些用户对哪些表具有写权限？

发布于 2024-08-08 07:13:20 字数 345 浏览 5 评论 0原文

在 SQL Server 7.0 中，我需要找出哪些用户对特定数据库中的哪些表具有写入权限。

我知道我可以在企业管理器中通过浏览数据库中的每个表并查看授予这些表的访问权限来完成此操作 - 但数据库中有几百个表。由于我只关心少数用户，因此我宁愿从用户角度来接近它。

是否可以在系统表上运行查询来实现此目的？是否有通过企业管理器的另一种方法？

我所追求的基本上是这样的：

UserOne has write access to Table1, Table2 and Table3
UserTwo has write access to Table2 and Table3
etc.

原文

In SQL Server 7.0, I need to find out which users have write access to which tables in a particular database.

I know that I can do this in Enterprise Manager by going through each table in the database and looking at the access granted to those tables - but there are a few hundred tables in the database. As I'm only concerned with a handful of users, I'd rather be approaching it from the users.

Is there a query I can run on system tables to achieve this? Is there another approach via Enterprise Manager?

What I'm after is something that basically says:

UserOne has write access to Table1, Table2 and Table3
UserTwo has write access to Table2 and Table3
etc.

分享到QQ

分享到微博

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

发布评论

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

苏辞 2024-08-15 07:13:20

SELECT u.name, o.name
FROM syspermissions p, sysobjects o, sysusers u
WHERE p.id = o.id
AND u.uid = p.grantee
AND u.name IN ('UserOne', 'UserTwo', 'UserThree')
AND o.xtype = 'U'
AND p.actadd = 27

神奇的 27 是由 1 (SELECT) + 2 (UPDATE) + 8 (INSERT) + 16 (DELETE) 构建而成，

感谢 Matt Lacey（和 Google！）让我走上了正确的道路： http://blog.mrlacey.co.uk/2007/06/checking-数据库权限-in-sql.html

SELECT u.name, o.name
FROM syspermissions p, sysobjects o, sysusers u
WHERE p.id = o.id
AND u.uid = p.grantee
AND u.name IN ('UserOne', 'UserTwo', 'UserThree')
AND o.xtype = 'U'
AND p.actadd = 27

The magic 27 is built from 1 (SELECT) + 2 (UPDATE) + 8 (INSERT) + 16 (DELETE)

Thanks to Matt Lacey (and Google!) for setting me on the right course: http://blog.mrlacey.co.uk/2007/06/checking-database-permissions-in-sql.html

回复收藏 0 原文

~没有更多了~