I enjoyed this article, it brings to mind a question that I've not been able to answer regarding permissions, maybe someone out there can suggest a solution.

I'm working on an ASP.NET application that uses SQL2005 as the DBMS. We're using Integrated Security only -- SQL Security is not enabled. We're also using transactional replication to replicate data to local clients, we also retrieve and process data from these clients (not via replication). The application maps all users to a Windows user that has explicited defined permissions to the database, which do not include dbo_owner or syaadmin rights. The application is meant to include the ability to back up the database (after the user has determined that all transaction data has been retrieved from the client machines), therefore this standard user needs the ability to create a backup.

The logical solution is to add this standard user to the db_backupoperator role. However, I've found that when replication is installed, the server fires a procedure called sp_MSrepl_backup_start before running the backup. This proc. examines the permissions of the user running the backup and raises an error (18799) if the user is not a member of the sysadmin or dbcreator server roles, or db_owner on the database.

I defeated this by using a try-catch in the procedure we call to run the backup, however, I'm concerned that I'm working around a problem that is more profound than I understand. Can anyone propose a better way to solve this problem?