How to grant connect and read/write permission to ALL databases?

Posted on

Question :

I want a SQL Server login that can READ, WRITE and View definition on all databases.

After doing:

USE master;
GRANT VIEW ANY Definition TO [a];

It looks like I have to GRANT CONNECT and read/write to each and every single database.

Isn’t there an easier way?

Answer :

You indeed have to add the user to the db_datareader and db_datawriter roles for each of your existing databases.

Such a query can be used for existing (and ONLINE) user databases:

DECLARE @user sysname = 'userTest1';
DECLARE @login sysname = 'userTest1';
DECLARE @SQL nvarchar(max) = '';

SELECT @SQL = @SQL + '
    USE ' + QUOTENAME(NAME) + ';

    IF NOT EXISTS(SELECT 1 FROM sys.database_principals WHERE name = ''' + @user + ''')
    BEGIN
        CREATE USER ' + QUOTENAME(@user)
           + ' FOR LOGIN ' + QUOTENAME(@login)
           + ' WITH DEFAULT_SCHEMA=[dbo];
    END
    ELSE
    BEGIN
        EXEC sp_change_users_login ''Update_One'', ''' + @user + ''', ''' + @login + ''';
    END

    EXEC sys.sp_addrolemember ''db_datareader'', ''' + @user + ''';

    EXEC sys.sp_addrolemember ''db_datawriter'', '''+ @user + '''; 
'
FROM sys.databases
WHERE database_id > 4 AND state = 0;

--EXEC sp_executesql @sql;
print @sql
  • This will print the first 4000 chararacters from @sql. Comment out the EXEC line to execute it.
  • The user is created if it does not exist or the link to the login is updated if the user is already there
  • It then add the user to the db_datareader and db_datawriter roles
  • Any other right or role needed can be added to the query

New databases are created from model and will inherit its setting.
The User can be added to the db_datareader and db_datawriter roles on model:

use model
go
CREATE USER [userTest1] FOR LOGIN [userTest1] WITH DEFAULT_SCHEMA=[dbo];
EXEC sys.sp_addrolemember 'db_datareader', 'userTest1';
EXEC sys.sp_addrolemember 'db_datawriter', 'userTest1'; 

The first query can take care of both model and user database using this WHERE clause:

WHERE (database_id > 4 or name = 'model') AND state = 0;

Note (From Kenneth Fisher): With SQL Server 2014, there are some new permissions CONNECT ANY DATABASE and SELECT ALL USER SECURABLES that would cover the connect and read but unfortunately there is nothing similar yet for write permissions. (MSDN)

Leave a Reply

Your email address will not be published. Required fields are marked *