SQL Server 登录配置和自动化
管理和维护多个环境中存在的多个数据库的 SQL Server 登录名、服务器角色和个人访问权限的好方法是什么？ 您的最佳实践是什么？
关于我的情况的一些信息：
- SQL Server 2005
- 我们有 N 个具有相同架构的“客户端”数据库（至少在理论上）
- 我们有一个中央“管理”数据库，它引用每个客户端数据库并可以保存配置值
- 这个“管理/客户端”模式在多个环境（dev/qa/stage/prod）中重复。
- 一些用户（例如测试人员）需要基于环境的不同权限
- 我们经常需要从一个环境中提取客户端数据库备份以在另一个环境中恢复以用于开发或测试目的
- 我们将我们的存储过程和脚本保留在源代码管理中并在构建周期中进行部署
现在，我的组织很混乱，而且我们没有遵循良好的安全实践。 我们没有正式的 DBA。 然而，如果我们变得更加复杂，那么一直维护它就会变得很麻烦。 如果我们尝试直接通过 Management Studio IDE 进行配置，我发现迁移到新服务器或从灾难中恢复非常耗时。
What's a good way to manage and maintain SQL Server logins, server roles, and individual access rights across multiple databases that exist in multiple environments? What are your best practices?
Some info about my situation:
- SQL Server 2005
- We have N amount of "client" databases with identical schemas (in theory, at least)
- We have a central "admin" database that references each client database and can hold configuration values
- This "admin/client" pattern is duplicated across multiple environments (dev/qa/stage/prod)
- Some users, like testers, need different rights based on evironment
- We frequently have to pull client db backups from one environment to restore on another for development or testing purposes
- We keep our stored procedures and scripts in source control and deploy in a build cycle
Right now my organization is chaotic and we don't follow good security practices. We have no formal DBA. However, if we got any more complex it would be a constant hassle to maintain it all the time. I could see migrating to a new server or recovering from disaster being extremely time consuming if we where to attempt configuring it directly through the management studio IDE.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
使权限变得更容易的一种方法是在数据库中创建名为 Dev、QA、Test、Prod 的卷，并向这些角色授予正确的权限。 然后，当您将数据库恢复到每个环境时，只需将开发人员分配到正确的角色即可。
A way to make the rights easier would be to create rolls in the database called Dev, QA, Test, Prod and grant the correct rights to those roles. Then as you restore the databases to each environment just drop the developers in the correct role.
我们使用活动目录组并强制执行 Windows 身份验证登录。 然后，在 SQL Server 内部，我们可以通过为每个 AD 组创建一个 SQL Server 登录名来根据用户所在的 AD 组定义访问权限。 不确定这比数据库角色更好还是更差，但这意味着角色是在每个数据库外部管理的。
然后，传播对数据库的访问要么是手动操作，要么是简短的 SQL 脚本，以确保数据库中的登录名指向有效的 SQL Server 登录名（这又是 AD 组）。
一般来说，这对于一般情况很有效。 然后，我们可以使用数据库角色将内置角色（例如，db_datareader）分配给每个 AD 组。
很少有人需要对此模型之外的数据库进行某些特定访问。 如果它不会具有侵入性或关键性，我们最终要么将其向整个组开放，要么最终创建一个必须单独管理的每用户帐户。 我们努力将这些问题保持在最低限度，并时不时地清理它们，这样它们就不会被滥用/遗忘。
We use active directory groups and enforce windows authenticated logins. From within SQL Server we can then define access based on the AD group the user is in by creating a single SQL Server login per AD group. Not sure if this is any better or worse than DB roles, but it means the roles are managed outside each database.
Propagating access to databases is then either a manual operation or a short SQL script to ensure the logins in the database point to a valid SQL Server login (which in turn is an AD group).
Generally this works well for the general case. We can use DB roles then to assign the builtin roles (e.g, db_datareader) to each AD group
Rarely someone needs some specific access to a database outside this model. We either end up opening it up to the group as a whole if it's not going to be invasive or critical or we'll end up creating a per-user account that has to be managed separately. We endevour to keep these to an absolute minimum, and clean them up every now and then so they're not abused/forgotten about.
首先，为了更轻松地将数据库恢复到不同的服务器，请使用 sp_help_revlogin 存储过程，用于在您创建登录的第一台服务器上编写登录脚本，然后使用该脚本在其他服务器上创建登录。 当您恢复数据库时，这可以使数据库用户正确映射到登录名。
无论您如何解决这个问题，根据环境在数据库级别拥有不同的权限都会很麻烦。 我在 master 中有一个存储过程，作为恢复过程的一部分，在我的开发服务器上调用该存储过程，该过程在数据库上执行额外的 GRANT，以便开发人员能够进行更改。 这是我能想到的解决类似问题的最好方法。
First, to make restoring a database to a different server easier, make sure that your logins all have the same SID on all of your servers by using the sp_help_revlogin stored procedure from Microsoft to script the login on the first server you create it on and then use the script to create the login on your other servers. This keeps the database user mapped to the login correctly when you restore the database.
Having different permissions at the database level depending on the environment is going to be a hassle to a point no matter how you role this out. I have a stored procedure in master that gets called on my Dev Server as a part of my restore process that performs the additional GRANT's on the database to give the developers access to make changes. That's the best I have been able to come up with to solve similar problems.