snippetsqlMinor
How to grant connect and read/write permission to ALL databases?
Viewed 0 times
connectdatabasesreadallpermissiongrantwritehowand
Problem
I want a SQL Server login that can READ, WRITE and View definition on all databases.
After doing:
It looks like I have to GRANT CONNECT and read/write to each and every single database.
Isn't there an easier way?
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?
Solution
You indeed have to add the user to the
Such a query can be used for existing (and ONLINE) user databases:
New databases are created from
The User can be added to the
The first query can take care of both
Note (From Kenneth Fisher): With SQL Server 2014, there are some new permissions
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_datareaderanddb_datawriterroles
- 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)Code Snippets
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 @sqluse 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';WHERE (database_id > 4 or name = 'model') AND state = 0;Context
StackExchange Database Administrators Q#130895, answer score: 8
Revisions (0)
No revisions yet.