7

I have 980 DBs on 1 server and I am supposed to grant readonly access to a couple of users.

I certainly don't want to run alter DB add user... statement since it is definitely time consuming.

I don't know if exec sp_addrolemember 'db_datareader', 'user' works?

Can someone let me know if something else can be done?

9

Here's how I would do it in Powershell:

$instance = 'localhost'
$dbs = dir SQLSERVER:\SQL\$instance\DEFAULT\Databases
$sql = 'CREATE USER [foo] FROM LOGIN [foo]; ALTER ROLE db_datareader ADD MEMBER [foo];'
$dbs | ForEach-Object {Invoke-Sqlcmd -ServerInstance $instance -Database $_.Name -Query $sql}

This assumes the login foo has already been created. It also assumes running on the localhost instance.

5

sp_addrolemember will add the user to the role, it will not automatically create the user in the database.

You could use dynamic SQL to run this presuming you can programmatically identify the databases where the user needs to be added.

The following will print code to add a single user to every non-system database in the instance:

DECLARE @UserName SYSNAME;
DECLARE @cmd NVARCHAR(MAX);
SET @cmd = '';

SET @UserName = 'SomeUser';

DECLARE cur CURSOR LOCAL FORWARD_ONLY
FOR 
SELECT 'USE ' + d.name + ';
GO
IF NOT EXISTS 
(
     SELECT 1 
     FROM sys.database_principals dp 
     WHERE dp.name = ''' + @UserName + '''
)
BEGIN
    CREATE USER ' + QUOTENAME(@UserName) + ' 
    FROM LOGIN ' + QUOTENAME(@UserName) + '
        WITH DEFAULT_SCHEMA = dbo;
END
EXEC sp_addrolemember @RoleName = ''db_datareader''
    , @MemberName = ''' + @UserName + ''';
GO

'
FROM sys.databases d
WHERE d.database_id > 4 /* exclude system databases.  Other criteria could
    be used here */

OPEN cur;
FETCH NEXT FROM cur 
INTO @cmd;

WHILE @@FETCH_STATUS = 0
BEGIN

    PRINT (@cmd);

    FETCH NEXT FROM cur 
    INTO @cmd;
END

CLOSE cur;
DEALLOCATE cur;

I've used a cursor here to individually PRINT the statement for each database. You will need to manually create the login for SomeUser prior to running this code.

2

I know there's a lot of better answers, but I like simple things.

 SELECT 
      'USE [' + name + N']' + CHAR(13) + CHAR(10)
      + 'create user [USER] for login [LOGIN]' + CHAR(13) + CHAR(10)
      + 'EXEC sp_addrolemember ''db_owner'', ''USER'''

FROM sys.databases

WHERE database_id > 4;
  • 1
    Note that - sp_addrolemember --> This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER ROLE instead. – Kin Shah Nov 10 '15 at 18:16
  • Also, better to use quotename rather than manually putting [ and ] brackets. – Kin Shah Nov 10 '15 at 18:17
1

A non cursor approach would be as below. I am assuming that the login already exists and the schema is default to dbo.

set nocount on
declare @user_to_be_added sysname
declare @sqltext nvarchar(max) = N''

-- change below to your user to be added .. make sure that the login is already existing
set @user_to_be_added = 'foobar'


select  @sqltext += char(10)+ 'use '+quotename(name)+';'+char(10)+ 'CREATE USER '+quotename(@user_to_be_added)+' FOR LOGIN '+quotename(@user_to_be_added)+';'+char(10)+
'ALTER USER '+quotename(@user_to_be_added)+' WITH DEFAULT_SCHEMA=[dbo];'+char(10)+ 'ALTER ROLE [db_datareader] ADD MEMBER '+quotename(@user_to_be_added)+'; '+ char(10) + 'go'
from sys.databases
where database_id > 4 -- exclude system databases

SELECT @sqltext
--- once you are happy with the output uncomment below line
-- EXEC sp_executesql @sqltext;
  • Correct me if I'm wrong, but don't you run the risk of not seeing all the output when concatenating such a large amount of text, then outputting it with PRINT? By large amount of text, I mean when doing this on 900+ databases. – Max Vernon Nov 10 '15 at 17:33
  • 1
    @MaxVernon yeh thats PRINT limitation, better to use a SELECT and save it to output file as well. – Kin Shah Nov 10 '15 at 17:35
-1

using an undocumented procedure you could use the SP_msforeachdb procedure.

For example, the execution would be something along the lines of

exec sp_msforeachdb '
use ?
if (dbid(''?'') > 4 )
BEGIN
    create user [user] from login [login];

    alter role db_datareader add member [user];'
END

This isn't the most elegant solution but if your simply looking to add a login to every user database this would work.

A quick note, if your using sql server 2008 R2 or older you would want to replace the alter role with exec sp_addrolemember as the alter role syntax was only added as of SQL Server 2012 and above.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.