SQL 2005 数据库角色和安全性
我已转而在 Sql Server 2005 中创建/使用架构，其中我还创建了数据库角色并将架构分配给角色。
我遇到的问题是我拥有的数据库角色之一应该具有完全访问权限 - 比如 SuperSysRole。
但是，当我为该角色分配登录名时，它无法执行特定命令。特别是，我希望此角色中的用户能够管理密码重置。但是，当我执行存储过程时，它不会返回数据库中的所有用户。
存储过程已运行，该过程返回详细信息并调用集成了 sysusers 和 sysmembers 表的视图。
用户（例如 myAdmin）已被分配给角色 SuperSysRole。角色 SuperSysRole 已被授予执行存储过程 selectRoles 的权限。
这可能是一件简单的事情，但有人可以告诉我我缺少什么吗？
我的应用程序使用 myAdmin 用户名和密码进行身份验证。当我使用 sa 帐户登录时，它工作正常。
I've moved to creating/using schemas in Sql Server 2005, where I've also created database roles and assigned schemas to the roles.
The problem I have is one of the db roles I have should have full access - say SuperSysRole.
However, when I assign a login to that role, it cannot execute specific commands. In particular, I want a user within this role to manage password resets. However, when I execute the stored procedure, it does not return back all users in the database.
The stored procedure has been run which returns back the details and calls a view which integrates the sysusers and sysmembers tables.
The user, say myAdmin, has been assigned to the role SuperSysRole. The role SuperSysRole has been granted execute to the stored procedure selectRoles.
It's probably a simple thing but can someone please tell me what I am missing?
My application authenticates using the myAdmin user name and password. It works fine when I login with the sa account.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
数据库角色成员资格可通过
sys.database_principals< /a> 和sys.database_role_members目录视图，这是可以使用的正确目录视图，而不是已弃用的 sysusers 和 sysmembers。两者具有相同的安全限制：授予数据库主体权限中列出了必要的权限：
查看定义。没有可以授予查看任何主体和角色定义的权限。最好的选择是创建一个从正确的元数据目录视图中进行选择的过程，并使用添加到
db_securityadmin角色中的凭据签署该过程。有关如何签名的示例，请参阅签署激活的程序一个程序。向需要查看这些主体和角色成员资格的任何人授予执行此过程的权限。然而，所有这些都无法实现您所声明的意图：管理密码重置。只有服务器主体（又名登录名）有密码，并且在数据库主体级别设计的任何方案都不会对服务器主体产生影响，因为它们是错误的抽象。而且由于没有要定义的服务器角色（您只能将 Windows 组作为角色，但您想要使用 SQL Auth），因此您无法通过角色成员资格来完成此操作。
我的建议是再次使用代码签名。创建执行所需活动的过程（查询
sys.server_principals和sys.sql_logins< /a> 检查密码过期，发出 ALTER LOGIN 语句来重置密码等），然后使用代码签名来签署这些过程并向签名授予所需的权限。我再次建议您签署激活程序以获取例如如何做到这一点。然后将这些过程的 EXECUTE 权限授予必须管理这些过程的“特殊”用户。Database role membership is queryable through the
sys.database_principalsandsys.database_role_memberscatalog views, which are the proper catalog views to use instead of the deprecated sysusers and sysmembers. Both have the same security restrictions:The necessary permissions are listed in GRANT Database Principal Permissions:
VIEW DEFINITION. There is no permission that can be granted to view any principal and role definition.Your best option is to create a procedure that selects from the proper metadata catalog views and sign this procedure with a credential that is added into the
db_securityadminrole. See Signing an Activated Procedure for an example how to sign a procedure. The grant EXECUTE on this procedure to whoever required to view these principals and role memberships.However, all of this will get you nowhere with regard with the declared intent: manage password resets. Only server principals (aka. logins) have passwords, and any scheme designed at the database principals level can have no effect on server principals, since they are the wrong abstraction. And since there are no server roles to define (you can only have Windows groups as roles, but you want to use SQL Auth), you cannot have this done by role membership.
My recommendation is, again, to use code signing. Create procedures that do the activities you want (query
sys.server_principalsandsys.sql_loginsto inspect password expiration, issue ALTER LOGIN statements to reset passwords etc) then use code signing to sign these procedures and grant the required privileges to the signature. I refer you again to Signing an activated procedure for an example how to do that. Then grant EXECUTE on these procedures to the 'special' users that have to manage these.