locked
Duplicate security in database role RRS feed

  • Question

  • I have a role in production that has been granted Execute, Select, Insert, Update and Delete on a schema.  It also has been granted some of the same permission on object within the same schema.  Other than being redundant will this cause any problems?  Will it be slower to evaluate the security when accessing the objects?  Should I making cleaning it up a prioroty?

    Thanks

    Friday, January 14, 2011 8:13 PM

Answers

  • Generally this isn't a problem. You are correct that it is redundant. It's a little confusing, so in the future when someone revokes one of the permission grants they might think you do not have access, when you actually continue to receive access from the other source. So redundant permissions can be a problem if they cause confusion.

    There is the minor drawback of making the permissions table a bit larger, so it could take a fracton of a second longer to establish someone's permission, but my guess is that it's not a measurable speed reduction unless you cook up an extreme example. It's probably in memory anyway.

    So I wouldn't recommend duplicating the access grants, but I wouldn't lose any sleep over it either.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    Friday, January 14, 2011 9:08 PM
  • Well, every thing that must be read will add a little overhead, but it could be very little. I doubt that this double granting on some objects will add much overhead and I would not spend time to clean it up as a performance issue.

    However, if you are trying to clean up your security settings for cleaner maintenance and management of security settings, then that would be a worthwhile effort.  Grants that are no longer wanted or needed may at some future date cause unexpected security results.  (Not undefined, just not what the administrator might have expected.)

    FWIW,
    RLF

    Friday, January 14, 2011 9:09 PM

All replies

  • Generally this isn't a problem. You are correct that it is redundant. It's a little confusing, so in the future when someone revokes one of the permission grants they might think you do not have access, when you actually continue to receive access from the other source. So redundant permissions can be a problem if they cause confusion.

    There is the minor drawback of making the permissions table a bit larger, so it could take a fracton of a second longer to establish someone's permission, but my guess is that it's not a measurable speed reduction unless you cook up an extreme example. It's probably in memory anyway.

    So I wouldn't recommend duplicating the access grants, but I wouldn't lose any sleep over it either.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    Friday, January 14, 2011 9:08 PM
  • Well, every thing that must be read will add a little overhead, but it could be very little. I doubt that this double granting on some objects will add much overhead and I would not spend time to clean it up as a performance issue.

    However, if you are trying to clean up your security settings for cleaner maintenance and management of security settings, then that would be a worthwhile effort.  Grants that are no longer wanted or needed may at some future date cause unexpected security results.  (Not undefined, just not what the administrator might have expected.)

    FWIW,
    RLF

    Friday, January 14, 2011 9:09 PM