April 1, 2016 at 1:57 pm
Alright, this is driving me nuts. I've got a SQL Agent job that restores a prod database over to a dev server. After the restore, I drop all of the users that came across with the restore and then add the users I need for dev. The script then attempts to add user to roles. The script runs out to completion w/o error, but the users do not get added to the roles. I can take the script and run it in SSMS and it works perfect (I'm SA).
Example of script code (names changed)
CREATE USER [myUser] FOR LOGIN [myLogin]
GO
ALTER ROLE [db_owner] ADD MEMBER [myUser]
GO
I've granted the account that runs SQL Agent db_owner and db_securityadmin on the new database. Every time I run the job step the users come out fine, but no role memberships. Any help would be appreciated.
April 2, 2016 at 6:03 am
I have a whole multitude of automated jobs which do exactly as you state.
use MyDatabase;
--
CREATE USER [MySQLUser] WITH DEFAULT_SCHEMA = dbo;-- SQL User
CREATE USER [ThisDomain\Developers] FOR LOGIN [ThisDomian\Developers];-- AD group/user
--
--
EXEC dbo.sp_addrolemember N'db_owner', N'ThisDomain\Developers';
--
exec.dbo.sp_addrolemember [Users],[MySQLUser];
--
I do remove all the users from the database first before I put them back ( mainly because they are different in dev/test/uat )
hope this helps
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
April 2, 2016 at 6:07 am
sorry should have said only the dba team have sa rights and local admin to the dev sql boxes - the agent service runs as a domain service account which is in the local administrators group ( yes I know it's not best practice but it makes thing much more simple ) The service account is added as a sysadmin of the server.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
April 4, 2016 at 8:47 am
Thanks Colin. I may have to try elevating my service account's permissions to sa, but I'd rather not as I'm pretty sure my hand will get slapped. Besides permissions, we're doing the same thing. Any idea why my job doesn't fail? It says it runs to completion, but the role memberships aren't there. You'd think if it were a permission issue, the ALTER statement would throw an error.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply