SQL Server-如何授予对登录的所有数据库的读取访问权限? [英] SQL Server - How to Grant Read Access to ALL databases to a Login?

问题描述

我需要为服务器上的所有300个数据库赋予新的登录读取访问权限.如何在不选中用户映射区域中的300个复选框的情况下完成此操作?

I need to give a new login read access to all 300 databases on a server. How can I accomplish this without checking 300 checkboxes in the user mapping area?

推荐答案

一种方法是在SSMS的查询菜单上设置结果为文本"，然后执行以下操作.

One way would be to Set "Results to Text" on the query menu in SSMS then execute the below.

它实际上并没有进行更改，而是生成了一个脚本供您查看和执行.

It doesn't actually make the change but generates a script for you to review and execute.

SET NOCOUNT ON;

DECLARE @user_name    SYSNAME
        , @login_name SYSNAME;

SELECT @user_name = 'user_name',
       @login_name = 'login_name'

SELECT '
    USE ' + QUOTENAME(NAME) + ';

    CREATE USER ' + QUOTENAME(@user_name)
       + ' FOR LOGIN ' + QUOTENAME(@login_name)
       + ' WITH DEFAULT_SCHEMA=[dbo];

    EXEC sys.sp_addrolemember
      ''db_datareader'',
      ''' + QUOTENAME(@user_name) + ''';

    EXEC sys.sp_addrolemember
      ''db_denydatawriter'',
      '''
       + QUOTENAME(@user_name) + '''; 

GO
'
FROM   sys.databases
WHERE  database_id > 4
       AND state_desc = 'ONLINE' 

或者您可以查看sys.sp_MSforeachdb

Or you could look at sys.sp_MSforeachdb as here or Aaron Bertrand's improved version here

如果在运行此命令时未看到所有字符，请打开文本"查询选项，然后检查每列中显示的最大字符数"设置.确保将其设置为足够大的值以显示所有字符.

If you are not seeing all of the characters when you run this, open the Query Options for Text and check the setting for 'Maximum number of characters displayed in each column'. Make sure this is set to a value large enough to display all characters.

这篇关于SQL Server-如何授予对登录的所有数据库的读取访问权限?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！