By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,266 Members | 1,297 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,266 IT Pros & Developers. It's quick & easy.

read and set object roles programatically

P: 3
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 ?
Oct 7 '08 #1
Share this Question
Share on Google+
3 Replies

Expert 2.5K+
P: 2,878
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
Oct 7 '08 #2

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

currently i use a line like this one :

Expand|Select|Wrap|Line Numbers
  1. 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 ?
Oct 7 '08 #3

Expert 2.5K+
P: 2,878
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
Oct 7 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.