AD accounts and deny backup problems

Question

Post reply

AD accounts and deny backup problems

stobe

SSC Eights!

Points: 846
More actions
December 1, 2014 at 4:27 am

#292976

I'm hoping someone can help me to diagnose and fix a strange issue I am having when adding Windows users to databases on one of my domains. In this domain, if I add either a Windows user or group to a database and go into the database 'properties -> permissions' it shows that 'Backup database' and 'Backup log' are explicitly denied. This happens in any database role, even db_owner. This behaviour does not apply to SQL Server logins and does not apply on other domains I am administering, but it is exhibiting itself in all versions from SQL2008 and above.
Does anyone have any thoughts on how I can find out why this is happening or stop it?
Many thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 1

Run these querie:

SELECT * FROM sys.database_permissions WHERE state_desc = 'DENY'

SELECT * FROM sys.server_permissions WHERE state_desc = 'DENY'

And then dig further from the appropriate DENY that you find.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

stobe SSC Eights! Points: 846 More actions · Answer 2

Thanks for your suggestion Erland. The first query on sys.database_permissions confirms that Backup database and log are denied:

classclass_descmajor_idminor_idgrantee_principal_idgrantor_principal_idtypepermission_namestatestate_desc

0DATABASE0061BADBBACKUP DATABASEDDENY

0DATABASE0061BALOBACKUP LOGDDENY

The second query on sys.server_permissions yields no results and so doesn't give me anything to investigate.

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 3

Now you only need to check in sys.database_principals who principal_id 6 is. Presumably it is a Windows group.

This this appears to occud in many databases, also chcek model, since it may be inherited from this databases.

I asked you to check both place, since I was not sure whether this is a server-level or database-level permission.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Perry Whittle SSC Guru Points: 234013 More actions · Answer 4

Erland Sommarskog (12/1/2014)
I asked you to check both place, since I was not sure whether this is a server-level or database-level permission.

BACKUP DATABASE and BACKUP LOG are database level securables. CONTROL SERVER merely automatically inherits these permissions at each database level.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

stobe SSC Eights! Points: 846 More actions · Answer 5

Yes, principal ID 6 is the Windows group I assigned to the database. I already know this and what the group is though, so this doesn't help me understand why the group has deny applied on these items when the group was added with the db_owner role only.

I have checked and this isn't being inherited from the model database. It occurs for new databases and databases copied from multiple other servers that do not have this issue.

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 6

DDL trigger on server level? Or for that matter a DDL trigger inherited from model?

The DENY does not come out of nowhere (but they take precedence over any GRANT, and apparently db_owner membership). So something somewhere is executing these DENY statements.

If nothing else set up a DDL trigger for the DENY_DATABASE event that logs or prevents the action from occurring.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

stobe SSC Eights! Points: 846 More actions · Answer 7

Many thanks for your help Erland, it seems it was a database trigger that one of the developers has been adding. I am very relieved to have found the cause with your assistance!