467,134 Members | 948 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,134 developers. It's quick & easy.

read and set object roles programatically

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
  • viewed: 1296
3 Replies
Expert 2GB
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
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 2GB
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.

Similar topics

3 posts views Thread by Noloader | last post: by
6 posts views Thread by david | last post: by
8 posts views Thread by Raterus | last post: by
23 posts views Thread by digitalorganics@gmail.com | last post: by
3 posts views Thread by Froefel | last post: by
9 posts views Thread by =?Utf-8?B?TWlrZQ==?= | last post: by
1 post views Thread by Doogie | last post: by
5 posts views Thread by =?Utf-8?B?bXBhaW5l?= | last post: by
2 posts views Thread by Anthony Smith | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.