Security folder in SSMS

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Security folder in SSMS

Security folder in SSMS

Rate Topic

Display Mode

Topic Options

Author

Message

IT researcher

Posted Tuesday, February 05, 2013 2:14 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 1:51 AM
Points: 183, Visits: 947

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?

Post #1415653

Grant Fritchey

Posted Tuesday, February 05, 2013 3:43 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 6:59 AM
Points: 13,378, Visits: 25,163

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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #1415692

IT researcher

Posted Tuesday, February 05, 2013 4:05 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 1:51 AM
Points: 183, Visits: 947

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?

Post #1415709

GilaMonster

Posted Tuesday, February 05, 2013 4:09 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:17 PM
Points: 37,713, Visits: 29,968

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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1415712

anthony.green

Posted Tuesday, February 05, 2013 4:11 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

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.

Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1415715

Grant Fritchey

Posted Tuesday, February 05, 2013 4:17 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 6:59 AM
Points: 13,378, Visits: 25,163

winmansoft (2/5/2013)

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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #1415719

« Prev Topic | Next Topic »

Permissions