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
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

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

Group: General Forum Members
Points: 39900 Visits: 32650
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
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5815 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
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

Group: General Forum Members
Points: 64 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-Enthusiastic
SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)

Group: General Forum Members
Points: 190 Visits: 302
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
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26121 Visits: 17539
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-Enthusiastic
SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)

Group: General Forum Members
Points: 190 Visits: 302
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-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39900 Visits: 32650
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-Enthusiastic
SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)

Group: General Forum Members
Points: 190 Visits: 302
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
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16563 Visits: 19098
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