sql server授予对特定数据库登录的访问权限。 [英] sql server grants access to specific login to database.

问题描述

你好朋友......

我为Intranet创建了网站并将其托管在服务器上。因为我需要在sql server 2008中创建登录IIS APPPOOL\hi，以便我的应用程序访问我的reportdb数据库。 IIS APPPOOL \hi在sql server 2008中有sysadmin和公共服务器角色。我创建了另一个登录sa与IIS APPPOOL \\相同\\hi的。这些正常工作。

现在我希望这两个登录访问数据库中的所有操作reportdb，并且应该拒绝所有登录以访问reportdb 的。我的Sql Server 2008具有混合模式（Windows身份验证和Sql身份验证）。 PLZ帮帮我

hello friends...
i have created website for intranet and hosted it on server. for that i needed to create login "IIS APPPOOL\hi" in sql server 2008 for my application to access my "reportdb" database. "IIS APPPOOL\hi" has sysadmin and public server roles in sql server 2008. And i had created another login "sa" same as "IIS APPPOOL\hi". these are working correctly.
Now I want these two logins to access "reportdb" for all operations in database and remaining all logins should be denied to access "reportdb". My Sql Server 2008 is having mixed mode (windows authentication and Sql authentication). plz help me

推荐答案

你可以去数据库=>安全性=>用户=>新用户菜单或安全性=>登录=> 新建登录菜单，用于添加用户并为其分配数据库权限。您可以断言拒绝角色/用户对任何对象和操作的权限。



请注意，我建议一般使用datareader和/或数据编写器角色，尽管这些不适用于存储过程等，因此您必须手动管理对它们的访问或创建角色。我永远不会给一个Web访问帐户SA权限。我可能会将dbowner应用到一个帐户来调试权限问题，但直到我找到了缺少的权限。如果我想排除某些对象，我还会应用特定的拒绝权限。如果我只想访问许多对象，我不会使用内置角色，尽管我可能会使用自定义数据库角色或AD组。



您还可以编写数据库权限脚本，但GUI更快速，更轻松。脚本通常保留用于remore部署。



请注意，如果您使用集成（Windows AD）安全性，则用户必须位于相同或受信任的域中数据库服务器。

You can either go to the Database => Security => Users => New User menu or Security => Logins => New Login menu to add users and assign them permissions on the database. You can assertively deny permission for roles/users on any object and operation.

Please note, I'd advise generally to use the datareader and/or datawriter roles, although these do not apply to stored procedures etc. so you'd have to manage access to them manually or create a role. I would NEVER give a web access account SA privileges. I might apply dbowner to an account to debug for permission problems, but only until I'd figured out the missing permission. If I want to exclude some object, I'd also apply a specific deny permission. If I only wanted access to a few objects from many, I'd not use the in-built roles, although I might use a custom DB role or AD group.

You can also script database permissions, but the GUI is quicker and easier. Scripting is usually reserved for remore deployments.

Please note that if you use integrated (Windows AD) security, the user must be in the same or a trusted domain as the DB server.

这篇关于sql server授予对特定数据库登录的访问权限。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！