Keith Tate (10/7/2013)
Do you use a scheduled job to refresh the test database? If so, then add a step to run your script. If you don't use a scheduled job how do you automate the process of the restore?
Thanks Keith for the response. Yes, we use a scheduled job to refresh and yes we can add a step but this script just outputs the users,permissions which we again need to execute after the refresh is complete.
Script output ( example for one user ) :
IF NOT EXISTS (SELECT name FROM sys.database_principals WHERE name = 'Test') CREATE USER [Test] FOR LOGIN [Test] WITH DEFAULT_SCHEMA =dbo;
EXEC sp_AddRoleMember @rolename = 'fairparamwriter', @membername = 'Test';
EXEC sp_AddRoleMember @rolename = 'fairparamwriter', @membername = 'Test';
GRANT EXECUTE TO [Test];
GRANT SELECT ON [dbo].[dbatesttable] TO [Test];