SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


I need differences between Roles, Schemas, Users and Logins. Can anyone help me. Thanks in advance


I need differences between Roles, Schemas, Users and Logins. Can anyone help me. Thanks in advance

Author
Message
SQL DBA-685905
SQL DBA-685905
SSC-Enthusiastic
SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)

Group: General Forum Members
Points: 108 Visits: 288
I need differences between Roles, Schemas, Users and Logins. Can anyone help me. Thanks in advance
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)

Group: General Forum Members
Points: 97653 Visits: 33014
SQL DBA (1/7/2009)
I need differences between Roles, Schemas, Users and Logins. Can anyone help me. Thanks in advance


Have you looked them up on the Books Online? If so, what did you not understand from there?

Here's a bit to get you started:

Login = Server
User = Database
Schema = Object Owner
Role = Security aggregation.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
MarkusB
MarkusB
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10419 Visits: 4208
SQL DBA (1/7/2009)
I need differences between Roles, Schemas, Users and Logins. Can anyone help me. Thanks in advance

You call yourself SQL DBA and ask such a basic question ?
As Grant already said start reading Books Online and if you don't know where to find it it's here:
http://technet.microsoft.com/en-us/library/ms187648.aspx

Markus Bohse
jleonardi
jleonardi
SSC Journeyman
SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)

Group: General Forum Members
Points: 78 Visits: 80
Theses are concepts that are good starting points if you are new to SQL Server, and reading Books Online is a good place to start also. It is best to read about these concepts first, and then ask more specific questions, that way the more experienced DBAs will feel like you have an interest in the topic and are willing to research as appropriate.

Roles: I think of creating roles in the database to group users of like function. Roles are granted certain permissions in the database. You should become familiar with the fixed database roles since these will be utilized once you start creating users within the database. Also, once you see the type of permissions that are granted to each role, is makes more sense.

Schema: there can be several schemas in a database, which will house different types of objects such as tables, indexes, stored procedures, functions, etc. Users own schemas. Looking into the AdventureWorks database illustrates this concept, with several schemas like HR, Production, etc.

Login: Think about login as gaining access to the SQL Server instance. If a user account is not granted any permissions within the instance, you basically just were able to unlock the door and enter the room, by creating a user you then grant access to the database objects or principals, and can begin to work with them.

Users: Users own schemas, and as such will be able to manipulate the objects they own. Some of the manunipulations are very permissive, such as creating tables, indexes, stored procedures, functions, etc. These are developers and administrators.

Users are created and granted permissions for application use, which will have select, update, insert, and delete and execute permissions to a finite set of objects in the schema, for which the application will need to function properly.

In a client server database, as an example, of the structure. Roles were defined which provides the permissions to the database objects in the database, which only has one schema 'dbo'. One SQL server login was created with the same username, and dbo is the assigned default schema, and the roles assigned to that username.
In the application, each specific user is given there own "application" login which is mapped to the one defined sql server login.

Hope this helps with understanding some of these concepts.
Good luck with your studying.

Jennifer
Robin
Robin
SSC-Addicted
SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)

Group: General Forum Members
Points: 484 Visits: 319
MarkusB (1/7/2009)
SQL DBA (1/7/2009)
I need differences between Roles, Schemas, Users and Logins. Can anyone help me. Thanks in advance

You call yourself SQL DBA and ask such a basic question ?
As Grant already said start reading Books Online and if you don't know where to find it it's here:
http://technet.microsoft.com/en-us/library/ms187648.aspx


People are supposed to be able to come here for answers so they can learn. They shouldn't get belittled for not already knowing the answers. People like you are why we can't have nice things. Jerk!
Sean Lange
Sean Lange
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62025 Visits: 17954
robin.pryor (3/27/2014)
MarkusB (1/7/2009)
SQL DBA (1/7/2009)
I need differences between Roles, Schemas, Users and Logins. Can anyone help me. Thanks in advance

You call yourself SQL DBA and ask such a basic question ?
As Grant already said start reading Books Online and if you don't know where to find it it's here:
http://technet.microsoft.com/en-us/library/ms187648.aspx


People are supposed to be able to come here for answers so they can learn. They shouldn't get belittled for not already knowing the answers. People like you are why we can't have nice things. Jerk!


While I agree that the comment was a bit harsh, this thread is over 5 years old. ;-)

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Robin
Robin
SSC-Addicted
SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)

Group: General Forum Members
Points: 484 Visits: 319
Sean Lange (3/27/2014)
robin.pryor (3/27/2014)
MarkusB (1/7/2009)
SQL DBA (1/7/2009)
I need differences between Roles, Schemas, Users and Logins. Can anyone help me. Thanks in advance

You call yourself SQL DBA and ask such a basic question ?
As Grant already said start reading Books Online and if you don't know where to find it it's here:
http://technet.microsoft.com/en-us/library/ms187648.aspx


People are supposed to be able to come here for answers so they can learn. They shouldn't get belittled for not already knowing the answers. People like you are why we can't have nice things. Jerk!


While I agree that the comment was a bit harsh, this thread is over 5 years old. ;-)


But the point is valid and if it reaches even 1 person, which it did, I feel better. :-D
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)

Group: General Forum Members
Points: 97653 Visits: 33014
robin.pryor (3/27/2014)
Sean Lange (3/27/2014)
robin.pryor (3/27/2014)
MarkusB (1/7/2009)
SQL DBA (1/7/2009)
I need differences between Roles, Schemas, Users and Logins. Can anyone help me. Thanks in advance

You call yourself SQL DBA and ask such a basic question ?
As Grant already said start reading Books Online and if you don't know where to find it it's here:
http://technet.microsoft.com/en-us/library/ms187648.aspx


People are supposed to be able to come here for answers so they can learn. They shouldn't get belittled for not already knowing the answers. People like you are why we can't have nice things. Jerk!


While I agree that the comment was a bit harsh, this thread is over 5 years old. ;-)


But the point is valid and if it reaches even 1 person, which it did, I feel better. :-D


And I feel good that I was helpful 5 years ago.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Robin
Robin
SSC-Addicted
SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)

Group: General Forum Members
Points: 484 Visits: 319
Ah but the question "What landed me on this post in the 1st place?"

I'm a SQL DBA and I've never given a crap about schemas. I know what they are by definition, but have never once considered using them, because I'm probably too old-school. I think in terms of logins, groups, roles, and users. Now I'm in a circumstance where everybody and their mailman has had their hands in the cookie jar on this production server and nobody currently working here knows why things are the way they are. Should I create a new post to get flamed on "You call yourself a DBA....." or do you want to take a stab at answering me here?
Luis Cazares
Luis Cazares
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41564 Visits: 19815
robin.pryor (3/27/2014)
Ah but the question "What landed me on this post in the 1st place?"

I'm a SQL DBA and I've never given a crap about schemas. I know what they are by definition, but have never once considered using them, because I'm probably too old-school. I think in terms of logins, groups, roles, and users. Now I'm in a circumstance where everybody and their mailman has had their hands in the cookie jar on this production server and nobody currently working here knows why things are the way they are. Should I create a new post to get flamed on "You call yourself a DBA....." or do you want to take a stab at answering me here?

The problem isn't that the OP didn't know, the problem is that this is clearly explained in BOL which should be the first option to look for answers for any SQL Server DBA. A question posted as this would have gotten different responses:
I've been studying about users, logins, roles and schemas, but I'm still confused. Can you help me understand the differences? I see that the user, login and schema are all the same, but the definitions are different.


The original questions seemed as: "Could you do my homework for me?"


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search