2

The task is to automate restoring a group of databases, fixing orphaned users, and granting SELECT/INSERT/UPDATE/DELETE permissions.

use master
go

ALTER DATABASE [myDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    RESTORE DATABASE [myDatabase] FROM  DISK = N'D:\MSSQL\Backups\myDatabase.bak' WITH  FILE = 1
    ,  MOVE N'myDatabase' TO N'D:\MSSQL\Data\myDatabase.mdf'
    ,  MOVE N'myDatabase_log' TO N'D:\MSSQL\Logs\myDatabase.ldf'
    ,  NOUNLOAD
    ,  REPLACE
    ,  STATS = 5
ALTER DATABASE [myDatabase] SET MULTI_USER
go

use myDatabase
go

sp_adduser 'userSelectOnly'
go

exec sp_change_users_login 'Auto_Fix', 'userSelectOnly'
go

grant select to userSelectOnly
go

Like this, in SSMS, it works great. Since there are several users and several databases, I'd like to put the database names and users into tables and loop through each, e.g.

    select @sql = 'ALTER DATABASE [' + @dbname + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE'

    + 'RESTORE DATABASE [' + @dbname + '] FROM  DISK = N''D:\MSSQL\Backups\' + @dbname + '.bak'' WITH  FILE = 1
        ,  MOVE N''' + @dbname + ''' TO N''D:\MSSQL\Data\' + @dbname + '.mdf''
        ,  MOVE N''' + @dbname + '_log'' TO N''D:\MSSQL\Logs\' + @dbname + '.ldf''
        ,  NOUNLOAD
        ,  REPLACE
        ,  STATS = 5'

    + 'ALTER DATABASE [' + @dbname + '] SET MULTI_USER'' -- extra tick to fix the SE code highlighter

    exec sp_executeSQL @sql

    @sql = 'use ' + @dbname
    exec sp_executeSQL @sql

    select @sql = 'grant select to ' + @current_loop_user
    exec sp_executeSQL @sql

Followed by all the login/user lines. I've tried it, and it neither do the job nor throw obvious errors, am I going to just have to type it all out longhand?

2
  • Execute use dbname and grant select to in one command, I think it helps. Sep 19 '19 at 6:10
  • Side note, use ADD USER instead of the deprecated sp_adduser to add database users.
    – Dan Guzman
    Sep 19 '19 at 11:18
0

Your dynamic text is not valid:

you should add spaces between IMMEDIATE and RESTORE, STATS = 5 and ALTER, you should remove additional unclosed quotation mark here: SET MULTI_USER'' --> SET MULTI_USER'

Your

@sql = 'use ' + @dbname
    exec sp_executeSQL @sql

is executed in it's own context so you changed db context not for your session but only within your dynamic code execution so your next piece of dynamic code that grants is still executed in the context of current database.

You should always print your dynamic code before try to execute it and you'll see where the errors are.

But the best solution for your situation is to write once a plain(not dynamic) code for all databases, save it and run every time you need it.

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.