March 17, 2016 at 2:40 pm
I have about 1917 databases in the server. I have already created the login for users. All users need is read only access to all databases. Does anyone have any script I can run which can grant read only access to users for all the DBs? I don't mind doing it manually but it will be time consuming. USE [File]
GO
CREATE USER [innem.sa] FOR LOGIN [innem.sa]
GO
USE [File]
GO
ALTER ROLE [db_datareader] ADD MEMBER [innem.sa]
GO
March 17, 2016 at 3:04 pm
Getting close
drop table #test
create table #test (command varchar (500))
insert into #test SELECT 'USE [' + name + N'] '
+ 'CREATE USER [innem.sa] for login [innem.sa]'
FROM sys.sysdatabases
select * from #test
March 17, 2016 at 3:07 pm
Off the top of my head and totally untested (that'll be your task :D):
SELECT 'USE [' + name + '];
CREATE USER [innem.sa] FOR LOGIN [innem.sa];
ALTER ROLE [db_datareader] ADD MEMBER [innem.sa];
'
FROM sys.databases;
Execute this in a query window with the option Output to Text (instead of the default grid). Inspect the result and if it's correct, copy/paste it into a new window and execute.
Perhaps you should add a WHERE clause to exclude system databases and perhaps a few other databases as well.
March 17, 2016 at 3:08 pm
<oops this is a duplicate of someone else's post>
March 20, 2016 at 6:40 am
The only users who automatically have access to all databases are members of the SYSADMIN role. For non-sysadmin users, you must create their account in a database. To faciliate the process of adding a user to all databases, you can leverage the undocumented procedure sp_MSforeachdb for this. Basically the way it works is that you pass an input parameter containing a batch of T-SQL. The procedure will execute the batch sequentially for each database, each pass replacing the question mark (?) character for a database name.
EXECUTE master.sys.sp_MSforeachdb
'
print ''?''
use [?];
if ''?'' not in (''master'',''tempdb'',''model'',''msdb'')
begin;
if not exists (select * from sys.sysusers where name = ''MYDOMAIN\MYUSER'')
create user [<MYDOMAIN\MYUSER>];
alter role db_datareader add member [<MYDOMAIN\MYUSER>];
end;
';
Here is some documentation for the procedure and further examples of usage:
One issue is that for any new databases created going forward, users will not automatically have access, you would need to run this for each user, which you could do perhaps by enumerating sys.syslogins.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 20, 2016 at 9:50 am
Eric M Russell (3/20/2016)
The only users who automatically have access to all databases are members of the SYSADMIN role. For non-sysadmin users, you must create their account in a database. To faciliate the process of adding a user to all databases, you can leverage the undocumented procedure sp_MSforeachdb for this. Basically the way it works is that you pass an input parameter containing a batch of T-SQL. The procedure will execute the batch sequentially for each database, each pass replacing the question mark (?) character for a database name.
EXECUTE master.sys.sp_MSforeachdb
'
print ''?''
use [?];
if ''?'' not in (''master'',''tempdb'',''model'',''msdb'')
begin;
if not exists (select * from sys.sysusers where name = ''MYDOMAIN\MYUSER'')
create user [<MYDOMAIN\MYUSER>];
alter role db_datareader add member [<MYDOMAIN\MYUSER>];
end;
';
Here is some documentation for the procedure and further examples of usage:
One issue is that for any new databases created going forward, users will not automatically have access, you would need to run this for each user, which you could do perhaps by enumerating sys.syslogins.
Your post was not up to your usual standards, Eric...
A couple comments:
1. sys.sysusers is there for backward compatibility. Use sys.database_principals instead. Background
2. sp_MSforeachdb is riddled with problems. Background.
For the original question, a cursor would be appropriate to use in this case. Here is sample code that can get you started. Once you have it working to your liking you can simply throw it into a stored procedure in your favorite utility database and schedule it to run on a semi-regular basis so it handles new databases being added.
DECLARE @db_name sysname,
@sql NVARCHAR(MAX);
-- set
DECLARE @login_name sysname = N'someone',
@user_name sysname = N'someone';
DECLARE db_cursor CURSOR FAST_FORWARD READ_ONLY
FOR
SELECT name
FROM sys.databases
WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb');
OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @db_name;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = N'USE ' + QUOTENAME(@db_name) + ';';
SET @sql += N'IF NOT EXISTS ( SELECT * FROM sys.database_principals WHERE name = N''' + @user_name + ''' )';
SET @sql += N' CREATE USER ' + QUOTENAME(@user_name) + ' FROM LOGIN ' + QUOTENAME(@login_name) + ';';
SET @sql += N'ALTER ROLE db_datareader ADD MEMBER ' + QUOTENAME(@user_name) + ';';
EXEC sys.sp_executesql
@sql;
FETCH NEXT FROM db_cursor INTO @db_name;
END;
CLOSE db_cursor;
DEALLOCATE db_cursor;
GO
edit: forgot the if not exists...
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply