December 11, 2013 at 12:50 pm
I would like to set a policy for backup process that database backups( any type, FULL/LOG/DIFFERENTIAL) should only be triggered by DBA group and not by any application logins/users.
I did tried to do with facet Login/User using below query to validate login name against the lilst of logins that should not run backup process.
ExecuteSql('string', 'select es.login_name from sys.dm_exec_requests er JOIN sys.dm_exec_sessions es ON er.session_id = es.session_id where er.command LIKE ''%backup%'' AND es.login_name NOT IN (''domain\dbalogin'',''domain\sqlserivceacount''
)')
but not able to set the DESIRED target in policy, it just evaluates the policy against the login available on sql server not the login running sql process. ANY help would be appreciated.
I have bunch of user databases with application logins having db_owner privilege. application triggers backup using these application logins
I knew this could be done using DEBY BACKUP permission to specific logins, am looking at PBM.
December 11, 2013 at 3:22 pm
Typo at last corrected.
I would like to set a policy for backup process that database backups( any type, FULL/LOG/DIFFERENTIAL) should only be triggered by DBA group and not by any application logins/users.
I did tried to do with facet Login/User using below query to validate login name against the lilst of logins that should not run backup process.
ExecuteSql('string', 'select es.login_name from sys.dm_exec_requests er JOIN sys.dm_exec_sessions es ON er.session_id = es.session_id where er.command LIKE ''%backup%'' AND es.login_name NOT IN ( ''domain\dbalogin'',''domain\sqlserivceacount''
)')
but not able to set the DESIRED target in policy, it just evaluates the policy against the login available on sql server not the login running sql process. ANY help would be appreciated.
I have bunch of user databases with application logins having db_owner privilege. application triggers backup using these application logins
I knew this could be done using DENY BACKUP permission to specific logins, am looking at PBM.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply