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
anddb_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)