jeudi 29 janvier 2015

Can user defined roles cause problems with colliding permissions?


I recently ran into a problem with an application crashing accessing a SQL Server 2008 database.


The tables are owned by one of two schemas (sde and gis). There are two database users (sde and gis), each owning the corresponding schemas.


There are a dozen or so user defined database roles granting the gis user read/write permissions on specific tables in the gis schema.


The gis user is also a member of the db_owner role.


Using SQL Profiler, it appeared to me the crashing application did so trying to execute a stored procedure owned by the sde schema.


Though needlessly redundant, I didn't expect there to be permissions problems since the gis user was a member of the db_owner role.


I removed membership of all roles except db_owner, and the problem went away.


Can anyone confirm that all those user defined roles were mucking up permissions?





Aucun commentaire:

Enregistrer un commentaire