January 7, 2009 at 5:34 am
I need differences between Roles, Schemas, Users and Logins. Can anyone help me. Thanks in advance
January 7, 2009 at 6:18 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 7, 2009 at 6:23 am
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
[font="Verdana"]Markus Bohse[/font]
January 8, 2009 at 12:54 pm
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
March 27, 2014 at 9:08 am
MarkusB (1/7/2009)
SQL DBA (1/7/2009)
I need differences between Roles, Schemas, Users and Logins. Can anyone help me. Thanks in advanceYou 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:
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!
March 27, 2014 at 9:30 am
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 advanceYou 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:
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 27, 2014 at 9:50 am
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 advanceYou 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:
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. 😀
March 27, 2014 at 10:17 am
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 advanceYou 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:
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. 😀
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 27, 2014 at 10:33 am
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?
March 27, 2014 at 10:42 am
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?"
March 27, 2014 at 10:49 am
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?
First of all, we tend to frown on flaming anybody around here. That isn't to say it doesn't happen from time to time. I think you missed the whole reason the OP got flamed. Their login name is SQL DBA and they asked a question that demonstrates that the ego in their login name and their actual experience may vary a little bit. Don't be too cynical based on a single post that you found.
So to your question, are you asking for an explanation of what a schema is and why should they be used or when they are appropriate?
_______________________________________________________________
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 27, 2014 at 10:54 am
My question is actually more specific. I'm creating a topic in the 2008 security forum. Getting the details in right now.
March 27, 2014 at 11:08 am
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?
Here to help out when & where I can. That doesn't mean I'll do people's homework for free though.
A schema is a security layer that allows you to separate object access and object ownership in order to provide more granular control over how you set up your databases. The others I provided the basics of up above.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 27, 2014 at 11:11 am
Oh I understand that part. I liken a schema to a namespace or interface in OOP. My question is implementation-related
March 27, 2014 at 11:15 am
robin.pryor (3/27/2014)
Oh I understand that part. I liken a schema to a namespace or interface in OOP. My question is implementation-related
Sorry, I'm missing something then. I don't see a question from you on this thread.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply