read and set object roles programatically

[aguy1306]

I have a large DB product running under MS SQL 2005 on windows server 2003 SP2.

I have problems with the uninformed changing permission on objects.

I need to build a SP that correctly sets the permissions of objects.
I need to iterate over objects ( SP and DB tables ), read off the roles for those objects and compare them with a list I have of what roles should be attached to that object .

While I can add and delete roles with no problem I cannot find the method to read the current roles of an object based on object ID .

Can anyone help me out ?

[ck9663]

Objects (like tables and databases) does not have roles. The users have roles. What I would suggest is to list all the users and create a matrix on which objects they have access to and reset all rights and use the matrix instead.

-- CK

[aguy1306]

If I understand correctly you are suggesting that I need to reverse my direction of inquiry.

currently i use a line like this one :

select * from sys.database_permissions join sys.database_principals on grantee_principal_id = principal_id where major_id='1611152785'

to get the roles and users associated with an object

Instead of trying to read the roles of a particular object using the object ID, I should instead read the user and then read which permissions that user has for that object ?

Can i also use the same line of logic for roles ?

The interface in MS SQL SERVER management studio does not make clear the object permission are are children of the user rather then the object . It seems to imply the reverse . Am i reading wrong ?

[ck9663]

Depending on your environment, you can list all the users that has access on your system. Then list all objects that they have rights to. You can then define roles that you can just add these users to. This way it's a clean slate. However, this will cause a downtime on your operation. But you'll have a more controlled environment.

I'm just suggesting this step since you're doing a some sort of clean-up anyway.

-- CK