September 25, 2007 at 8:38 am
Hi,
I am relatively new to SQL server 2005. Right now i have a situation where in I created a schema 'xyx' and granted the authorization using my dbo account. Created all the tables in the new schema. Created a role called dbo_developer and added the users (windows authentication) to the role. But unfortunately the users are not able to access the tables, infact they dont see the tables listed when they log on..
The owner of the schema is dbo, the owner of the role is dbo and the default schema for the user is xyz. Am i missing anything
Please advice
September 25, 2007 at 10:42 am
You still need to give the user privileges to the database or schema. If you want to limit the users to just read privileges on a specific schema then you need to give that user SELECT rights to that schema. Otherwise you can add the user to the db_datareader role.
An even better method would be to create a new database role with SELECT rights to the schema then add the users to this role.
David
September 25, 2007 at 12:34 pm
Hi David,
Thanks for the reply. I have the the role created and then I selected the securables tab, clicked on the Add button, when the Add objects window popped up I selected the third option All objects belonging to this schema and then selected the schema from the drop down. Under the Effective permissions I granted the SELECT, DELETE, EXECUTE, INSERT, REFERENCES, UPDATE and VIEW DEFINITION and I denied the TAKE OWNERSHIP, ALTER and CONTROL. I clicked on OK and when I went to the Schema and looked for the effective permision for the user added to group, it only states Connect and hence the user doesnt see any objects listed under the database.
Is there any particular order in which this has to be done. Like
1) Create Schema
2) Create Users
2) Create Role
3) Define the Securables for the Role
4) Add the Users to the Role created
Vinu
September 25, 2007 at 1:15 pm
When you select the permissions tab on the Schema you should only see the database role not any users. If you do, remove any specific user permissions on the Schema... the user should only be defined through the database role.
The problem is the deny on the Control permission. This permission would give complete access to the database and you do not want to grant this but you also do not want to deny it. By denying you have overridden the other permissions and not allowed the user to access the database. You do not need to deny any permissions unless you want to override previously granted permissions.
As for sequence, what you have listed should work fine.
David
September 25, 2007 at 3:41 pm
Hi David,
That really was a very helpful advice. I removed the DENY on the CONTROL and the user can access all the tables. I added another user to the role but his effective permissions is still been shown as CONNECT alone. So I clicked on the view database permission link on the Select Permission tab in the Schema properties and the added the role their and explicitly defined the permissions there. Now the second person has also got the same amount of permissions. Is this the correct way of doing things. The reason why i ask you is that when i click on the view database permissions I see all the users as well as the role. The users has the Grant connect option alone where as the role has all the specific permissions
Vinu
September 26, 2007 at 9:27 am
The only reason you would need to do anything to the second user after adding him to the database role is if has deny privileges somewhere. Once you add a user to the role, it will have the permissions assigned to that role unless a deny privileges is set somewhere else that will override the role privileges. You should not need to edit the schema or role privileges any longer. The advantage of using roles is that you only need to define the security once.
When done, the only permissions you should see in the schema is the role, no users... unless you need to override some specific privileges but this should be an exemption. Even then I would recommend that you still use roles and not assign specific users to a schema.
In the end you can do whatever you want/need to do in order to get the job done, these are only recommendations to help simplify the process.
Hope this helps 🙂
David
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply