SQL Server Performance Forum – Threads Archive
Denying permissions to backup, replication, jobsHi, I this is my first post here. I really like this forum! I have several databases, and each database has a user that is aliased as DBO to their db. This gives them the permissions to do just about anything within their database, without giving access to any other db. The problem is that they can do TOO MUCH in their db. I want to deny: Backup / restore
(tried running "DENY BACKUP DATABASE TO loginname") but this does not work because their login is not listed in their db, it’s simply aliased as the DBO. creating jobs
(I can do this by removing the guest account from the MSDB but this is messy because it throws error about their user having access to msdb) Changing anything on the properties page of their database
(db / log size, recovery model, etc.) replication
I have several SQL servers that do not have any Publications configured. However, users (aliased DBO’s) are configuring Transactional replication jobs to pull from these servers. References to these jobs show up under Replication -> Subscriptions. I want to disallow this from happening. DTS jobs are fine (from their own server), but not replication. I want to do this with without changing my "aliased dbo" method because I don’t want object names to change to dbowner.tablename, or whatever, and, I can’t interrupt the production databases.
I know this is a lot to ask on my first post, but any info would be very appreciated! Tommy
Since Backups, restores and Jobs use ‘msdb’ database, you can try restricting access to the relevant tables in ‘msdb’. But, i don’t think it is possible to restrict access to the properties of their database.
If they are on DBO on enlisted database then its trouble to control from the steps defined by you.
As mentioned if the login is mapped to DBO its not possible to restrict them on the database level itself. Rather try to modify their privileges at the user level and explain the scenario to the management. _________