October 26, 2007 at 4:23 pm
could anyone explain the diff btween them ...and their relationship as i am new to sql server i had no idea abt all that in detail so would you please ....i jus want to know what should be adde in schema and what should be added in logins ..i jus know that users can be added in role can we even make a separate roles ...
October 26, 2007 at 5:07 pm
Schema in SQL Server is ownership of objects. More than one user can have a table with the same name in their own schema. You might use this for scratch pad tables or other reasons but the common practice is all objects owned under dbo, which makes the object schema dbo.
The difference between a user and a role is that a user has the right to logon in addition to rights granted on objects.
Roles cannot be used to logon but are a collection of users. They strictly contain rights to specific objects for common puposes which by putting a user in the collection inherits the rights from the role. A user in a role doesn't have to have any rights in the system but get the rights of the role. If a user has rights from another role or themselves that exclude using an object and a role they are assigned to has using rights on the same object the most restrictive are applied, meaning they are excluded from being able to use the object (table, view, stored procedure). A role allows you to create common types of rights to objects for simplification of administration. In addition roles are specific to the database and users to the server, so another benefit is portable of backups to other servers. If a user doesn't exist on the new sever you might possibly loose rights but the roles rights always stay intact unless you change making it easier to setup user permissions.
Hope was just nutshell enough.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply