Here's my situation - nightly we are downloading a backup of our accounting system database and restoring it on a local MS SQL Express 2012 server. I'm running batch jobs using the SqlCmd functions and everything is working great!
Except one little thing. After the database is restored I need to add the datareader permission to the SQL Login account that the end users share: SQL_RDR . I'm running the script below and it runs, does not return any errors, but also does not
update the user login permissions to grant them datareader access to the newly restored database.
SqlCmd -e -s SQLExpress -Q "USE [Accounting_live]"
SqlCmd -e -s SQLExpress -Q "CREATE USER [SQL_RDR] FOR LOGIN [SQL_RDR]"
SqlCmd -e -s SQLExpress -Q "USE [Accounting_live]"
SqlCmd -e -s SQLExpress -Q "ALTER ROLE [db_datareader] ADD MEMBER [SQL_RDR]"
When I check the user permission after the script is run t ...
Go to the complete details ...