How to restore Windows AD account grants and mapping when restoring database into MS SQLEXPRS

Copper Contributor

We are running MS SQL EXPRESS server with one database REPORTER_DB. We run a nightly powershell script (db_restore.ps1) to restore this database, transactions and logs from a third party vendor. This script works fine.

 

This database has a owner that also gets restored at the end of the database restore via powershell script above.

 

However, we have a windows AD account joeskripter that we assign or map the db_datareader role to that database so that joeskripter can query this database and email the report to the users.

If I manually login to this database instance via SQL management studio and map the joeskripter permissions, the joeskripter can run the query and complete successfully (Via scheduled run_report.ps1 script).

However the issue becomes when this database is restored via nightly powershell script (db_restore.ps1), the permission we gave  joeskripter get - "unmapped" or "removed" - I'm not sure what is the terminology.

So, I tried to run this script below after the database restore (db_restore.ps1) to try to map the permissions in a script called restore_db_permissions.sql which is invoked from a restore_db_permissions.ps1.

 

restore_db_permissions.ps1

Invoke-Sqlcmd -ServerInstance .\SQLEXPRESS -Database REPORTER_DB -InputFile "c:\scripts\restore_db_permissions.sql"

 

restore_db_permissions.sql:

-- Check the current user name - you must be running sqlcmd under the service account credentials
select suser_sname()
go

use REPORTER_DB
go

-- Add your login to the 'db_datareader' SQL database role in this example to grant read access to the data
exec sp_addrolemember 'db_datareader', 'yippy-domain\joeskripter'
go

 

This run without errors, but it does not remap the permissions and joeskripter script fails with error that is does not permission to open REPORTER_DB schema.

Any ideas if I'm missing something?

 

 

0 Replies