Thursday, February 26, 2015

Adding a User to Multiple Databases

Everyone seems to have their own version. Mine is as simple as I could make it. Make sure you create the LOGIN first, and set the @User variable in the code below.

Assumes you have already created a login named @User . 
set nocount on

declare @User sysname
set @User = 'trespasserswill'                  -- Change this!! 

declare @Sql nvarchar(4000)

set @Sql =
'
    use [?]

    if db_id() > 4
    begin
        print ''?''
        create user ' + quotename(@User) + ' for login ' + quotename(@User) + '
        exec sp_addrolemember ''db_datareader'', ''' + @user + '''
        exec sp_addrolemember ''db_datawriter'', ''' + @user + '''
    end
'

exec sp_MSForEachDB @Sql

0 comments:

Post a Comment