February 5, 2013 at 2:14 am
In SQL server management studio there are two security folders.One under the server and another one under each database. I have seen that the security folder under the server has server roles,user mapping etc. The one which is under database also has permissions of each login. So generally when i see permission for a database which one i should i see?One under server(in user mapping) or under the particular database in security folder?
What is the difference between permission present for each db in user mapping under server and permission in the security folder of each database?
February 5, 2013 at 3:43 am
The individual logins under the server folder are all about access to the server itself. Then, under the database are the individual database users. These can be mapped back to logins on the server. So which one you go to depends on what you're trying to address. If someone tells me that they're getting an error connecting to the server, I'll first go to the server folder to validate that they are there, or that an Active Directory group that that belong to is there. This means they have access to the server. Then, I'll ask which application/database they're trying to connect to. I'll go there and validate that they are mapped to a database user and that that user belongs to a role in the database (I always use roles for my security, makes it easier to manage, I map all settings to the role, then I just map users to that role, no longer worry about mapping security to users).
Does that help?
"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
February 5, 2013 at 4:05 am
Thank you
My actual doubt now is what is difference between user mapping page(which shows permission like datareader,dbowner etc) and one under database->security->any user.Right click user and click properties.Now u can see database role membership.
what is difference between the two here?
February 5, 2013 at 4:09 am
Under the server folder you see logins and the only permissions they have are server-wide permissions (sysadmin, security operator, backup operator, etc)
Under the database folder you see database users and the only permissions they have are database-specific permissions (db_owner, db_datareader, etc)
The user mapping page is where you map logins to their database users, so which login is associated with which database user.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 5, 2013 at 4:11 am
Referring back to you other post on this topic a while back
http://www.sqlservercentral.com/Forums/Topic1413043-391-1.aspx
You can assign permissions at individual object level inside the DB, so that is why you have security at 2 levels, server and Database.
So you need to check both server and database level permissions to get the full picture of what a user can or cannot do.
February 5, 2013 at 4:17 am
winmansoft (2/5/2013)
Thank youMy actual doubt now is what is difference between user mapping page(which shows permission like datareader,dbowner etc) and one under database->security->any user.Right click user and click properties.Now u can see database role membership.
what is difference between the two here?
To a very large degree, the question is the answer. One is at the server level and the other is at the database level. The security of the server is different from the security of the database. And the security of the database is different from the security of the server AND all other databases.
At the server level, I can make it so that you can view server state, meaning look at values within certain server level views, but I can do this without giving you access to any of the user datatabases (I'd have to give you access to the Master database). That's a server-level only permission. But you can't do anything to databases. Clear?
Then, inside one database I can make you the database owner, so you can do just about anything within that database, but, I've given you no permissions to other databases. So while you're all powerful within the database I gave you dbo in, you can't even connect to the others. Clear?
It's all about being able to decide precisely where, when, and how you want to expose abilities to the people accessing the server and the databases on it.
"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 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply