blocks|key|1280898|text|想法：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1280899|1280900|DENY+CONNECT+user或Windows+(sp_revokedbaccess|ordered-list-item|offset|length|1280901|You不能将ALTER_LOGIN与DISABLE参数一起使用来拒绝对DENY+CONNECT+SQL+TO+login组的访问(从链接中引用)|1280902|将所有权限分配给数据库角色，然后撤消用户的权限。在需要时将用户添加到角色。直接给用户分配对象权限是不好的。|1280903|如果用户有系统管理员权限，你根本不能拒绝他们任何东西(可能是CONNECT+SQL？)|1280904|如果用户有db_owner权限(假设在DB中设置)，你不能在数据库中拒绝他们除了CONNECT|1280905|之外的任何权限|1280906|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/ms177518.aspx|1|http://msdn.microsoft.com/en-us/library/ms188909(SQL.90%2529.aspx)%2520is%2520deprecated|2|http://msdn.microsoft.com/en-us/library/ms189828(SQL.90).aspx|3|http://msdn.microsoft.com/en-us/library/ms182763.aspx^0|0|0|0|H|0|R|H|1|0|6|B|2|Z|P|3|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|15|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|16|8|@]|9|@]|A|$]]|$1|C|3|D|5|E|7|17|8|@]|9|@$F|18|G|19|1|1A]|$F|1B|G|1C|1|1D]]|A|$]]|$1|H|3|I|5|E|7|1E|8|@]|9|@$F|1F|G|1G|1|1H]|$F|1I|G|1J|1|1K]]|A|$]]|$1|J|3|K|5|E|7|1L|8|@]|9|@]|A|$]]|$1|L|3|M|5|E|7|1M|8|@]|9|@]|A|$]]|$1|N|3|O|5|E|7|1N|8|@]|9|@]|A|$]]|$1|P|3|Q|5|6|7|1O|8|@]|9|@]|A|$]]|$1|R|3|-4|5|6|7|1P|8|@]|9|@]|A|$]]]|S|$T|$5|U|V|W|A|$X|Y]]|Z|$5|U|V|W|A|$X|10]]|11|$5|U|V|W|A|$X|12]]|13|$5|U|V|W|A|$X|14]]]]

Thoughts:

<ol>
<li><a href="http://msdn.microsoft.com/en-us/library/ms177518.aspx" rel="nofollow noreferrer">DENY CONNECT user</a> or <a href="http://msdn.microsoft.com/en-us/library/ms182763.aspx" rel="nofollow noreferrer">DENY CONNECT SQL TO login</a> (<a href="http://msdn.microsoft.com/en-us/library/ms188909(SQL.90).aspx" rel="nofollow noreferrer">sp_revokedbaccess</a> is deprecated)</li>
<li>You cannot use <a href="http://msdn.microsoft.com/en-us/library/ms189828(SQL.90).aspx" rel="nofollow noreferrer">ALTER_LOGIN</a> with the DISABLE argument to deny access to a Windows group (quoted from link)</li>
<li>Assign all rights to a database role, then revoke rights from user. Add user to role when needed. It's a bad idea to assign object rights to users directly anyway.</li>
<li>If user has sysadmin rights, you can't deny them anything at all (perhaps CONNECT SQL?)</li>
<li>If user has db_owner rights (assumes set up in DB), you can't deny them anything in the database except CONNECT</li>
</ol>

blocks|key|204360|text|我看到的最简单的方法是禁用登录。您可以在状态页下的SSMS中的登录属性上执行此操作。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|204361|您可以使用以下T-SQL实现相同的功能：|204362|假设您的登录名是bob+sql登录名，但也可以是windows登录名。|204363|ALTER+LOGIN+bob+DISABLE|code-block|syntax|javascript|204364|然后，要启用登录|204365|ALTER+LOGIN+bob+enable|204366|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|S|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|T|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|U|8|@]|9|@]|A|$]]|$1|M|3|N|5|H|7|V|8|@]|9|@]|A|$I|J]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

The simplest way i see it is to disable the login. You can do this on the login properties in SSMS under the status page.

You can achieve the same thing with the following T-SQL:

Assuming your login is bob a sql login, but can also be a windows login

<pre><code>ALTER LOGIN bob DISABLE
</code></pre>

Then, to enable the login

<pre><code>ALTER LOGIN bob enable
</code></pre>

blocks|key|1280734|text|您应该能够显式地拒绝他的权限，然后撤销拒绝。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1280735|您也可以使用ALTER+LOGIN+...+DISABLE禁用登录，但将在服务器级别而不是数据库级别阻止登录。|offset|length|style|CODE|1280736|黑客的解决方案是将用户映射到不同的登录，然后将他映射回(ALTER+USER+..+LOGIN+=+...)，但这是一种黑客，我甚至不确定是否能正确工作。|1280737|entityMap^0|0|6|N|0|S|P|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|M|8|@$D|N|E|O|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|P|8|@$D|Q|E|R|F|G]]|9|@]|A|$]]|$1|J|3|-4|5|6|7|S|8|@]|9|@]|A|$]]]|K|$]]

You should be able to explicitly DENY him a permission, then revoke the deny.

You can also disable the login with <code>ALTER LOGIN ... DISABLE</code>, but will block at server level, not database level.

A hack solution is to map the user to a different login, then map him back (<code>ALTER USER .. LOGIN = ...</code>), but is a hack and I'm not sure even works correctly.

blocks|key|1505562|text|用户如何访问数据库？如果通过应用程序，则只需注销用户并要求用户重新进行身份验证。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1505563|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

How is the user accessing the database? If via an application, then just log the user out and require the user to re-authenticate.

blocks|key|1503869|text|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1503870|我们希望在维护期间删除访问权限，但在完成后将他带回来。|blockquote|1503871|1503872|1503873|让维护进程在它正在处理的任何表上获取排它锁。这会将所有人锁定，直到处理完成。|1503874|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|-4|4|5|6|J|7|@]|8|@]|9|$]]|$1|A|3|B|4|C|6|K|7|@]|8|@]|9|$]]|$1|D|3|-4|4|5|6|L|7|@]|8|@]|9|$]]|$1|E|3|-4|4|5|6|M|7|@]|8|@]|9|$]]|$1|F|3|G|4|5|6|N|7|@]|8|@]|9|$]]|$1|H|3|-4|4|5|6|O|7|@]|8|@]|9|$]]]|I|$]]

<blockquote>
 we want to remove access during maintenance but bring him back after it is done. 
</blockquote>

Have the maintenance process acquire exclusive locks on whatever tables it is processing. This locks everyone out until the processing is complete.

blocks|key|204328|text|能否将数据库切换到单用户模式？http://msdn.microsoft.com/en-us/library/ms345598.aspx|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|204329|或者在删除权限之前编写脚本：http://www.sql-server-performance.com/articles/dba/object_permission_scripts_p1.aspx。我知道这是“备份权限和恢复权限”-但是这个脚本让这个过程变得不那么麻烦了。|204330|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/ms345598.aspx|1|http://www.sql-server-performance.com/articles/dba/object_permission_scripts_p1.aspx^0|F|1H|0|0|E|2C|1|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@$A|Q|B|R|1|S]]|C|$]]|$1|D|3|E|5|6|7|T|8|@]|9|@$A|U|B|V|1|W]]|C|$]]|$1|F|3|-4|5|6|7|X|8|@]|9|@]|C|$]]]|G|$H|$5|I|J|K|C|$L|M]]|N|$5|I|J|K|C|$L|O]]]]

Can you switch the database to single user mode? <a href="http://msdn.microsoft.com/en-us/library/ms345598.aspx" rel="nofollow noreferrer">http://msdn.microsoft.com/en-us/library/ms345598.aspx</a>

Or script up the permissions before you remove them: <a href="http://www.sql-server-performance.com/articles/dba/object_permission_scripts_p1.aspx" rel="nofollow noreferrer">http://www.sql-server-performance.com/articles/dba/object_permission_scripts_p1.aspx</a>. I know this is "backup permissions and restore permissions" - but this script makes the process a lot less cumbersome.

blocks|key|1503939|text|如果知道当前密码，请在维护窗口期间将密码更改为临时值。将密码重新设置为其初始值可实现此目的，而无需对帐户进行任何实质性更改。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1503940|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

If the current password is known, change the password to a temporary value during the maintenance window. Re-setting the password to its initial value serves this purpose without making any material changes to the account.

blocks|key|1503959|text|如果您使用的是Windows身份验证，并且可以对域中的所有内容锁定此用户，则可以设置允许该用户登录Active+Directory的时间。我不能提供详细的说明，但现在应该有一些关于ServerFault的东西。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1503960|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

If you are using Windows authentication and you can lock out this user from everything on the domain, you can set the times the user is allowed to log on in Active Directory. I can't provide detailed instructions but there should be something on ServerFault by now.

How can I stop a particular user from accessing the database for a period of time but at the same time not lose the permissions the user has on db objects. 

Basically when he comes back (ie when access is given back) he should have all the permissions he had before his access was cut off. If I use sp_revokedbaccess 'myuser' the user myuser loses all permissions he had on db objects. 

Is there a way to preserve myuser's permissions without resorting to cumbersome backup permissions and restore permissions kind of workarounds?

Preserving SQLServer permissions

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

如何在一段时间内阻止特定用户访问数据库，同时又不会丢失该用户对db对象的权限。基本上，当他回来时(即当访问权限恢复时)，他应该拥有他在访问被切断之前的所有权限。如果我使用sp_revokedbaccess ' myuser‘，用户myuser将失去他对db对象的所有权限。有没有一种方法可以保留myuser的权限，而不求助于繁琐的备份权限和恢复权限之类的变通方法？

问保留SQLServer权限
EN

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问保留SQLServer权限EN

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问保留SQLServer权限
EN