April 26, 2016 at 11:38 am
That DENY didn't show up in the results of the query I posted earlier?
It should have, so I'm curious why it didn't.
Cheers!
April 26, 2016 at 11:39 am
wow, deny to public. That would have been low, low on my list.
Good catch, Brandie.
I hate anyone that adds any permissions to public. Please, please, don't do that ever. Add a role.
April 26, 2016 at 11:40 am
Jacob Wilkins (4/26/2016)
That DENY didn't show up in the results of the query I posted earlier?It should have, so I'm curious why it didn't.
Cheers!
No, it did not, actually. Weird thing is, neither did the lone GRANT show up. I basically got a NULL result set.
April 26, 2016 at 11:42 am
Hmmm...and the database context was definitely changed to the database in question?
If I deny backup database to public on my test box, it shows up when I run that query.
Strange indeed.
At any rate, glad you got it resolved! 🙂
Cheers!
EDIT: I wouldn't expect a GRANT to show up, since it was filtering for DENY, unless you changed it.
April 26, 2016 at 11:42 am
Modified previous code to include object information.
select
dbprin.name PrincipalName,
dbprin.type_desc PrincipalTypeDesc,
dbperm.class_desc,
dbperm.permission_name,
dbperm.state_desc,
ao.name ObjectName,
ao.schema_id,
ao.type_desc ObjectTypeDesc
from
sys.database_principals dbprin
inner join sys.database_permissions dbperm
on dbprin.principal_id = dbperm.grantee_principal_id
left outer join sys.all_objects ao
on dbperm.major_id = ao.object_id;
April 26, 2016 at 11:42 am
Steve Jones - SSC Editor (4/26/2016)
wow, deny to public. That would have been low, low on my list.Good catch, Brandie.
I hate anyone that adds any permissions to public. Please, please, don't do that ever. Add a role.
I thought this was a Microsoft thing, but I started checking other databases and see different perms for public. So I think it is a vendor-specific item which they didn't notice because all their in-house accounts are sysadmin.
April 26, 2016 at 11:46 am
Jacob Wilkins (4/26/2016)
That DENY didn't show up in the results of the query I posted earlier?It should have, so I'm curious why it didn't.
I just remembered. There was so many results, I filtered on the account in question. When I filtered, I didn't get any results. I didn't bother looking at Public at the time.
The other script was only looking for BACKUP DATABASE permissions (DENY, GRANT, REVOKE) so that's why it pulled up Public and that's what got my attention.
April 26, 2016 at 11:50 am
Brandie Tarvin (4/26/2016)
Jacob Wilkins (4/26/2016)
That DENY didn't show up in the results of the query I posted earlier?It should have, so I'm curious why it didn't.
I just remembered. There was so many results, I filtered on the account in question. When I filtered, I didn't get any results. I didn't bother looking at Public at the time.
The other script was only looking for BACKUP DATABASE permissions (DENY, GRANT, REVOKE) so that's why it pulled up Public and that's what got my attention.
Ah, ok, now that all makes sense.
I intentionally didn't put a filter on the principal for that reason (to catch any role that might have a DENY). All's well that ends well (or so they say) 🙂
Cheers!
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply