?
Solved

SQL 2012, Login and user mapping

Posted on 2014-11-10
4
Medium Priority
?
330 Views
Last Modified: 2014-11-10
I'm trying to figure how SQL Logins and user mapping work. I watched a couple videos have basic concepts of how SQL login and security works.

Step 1.
I created two AD accounts, admin1 and admin2.
I gave both accounts with 'public' and 'sys_admin' under Security/User properties/Server Role.

Step 2.
Now, I logged in as admin1 to the server, then created a database 'test1'
I logged in as admin2 to the server, then created a database 'test2'

Step3.
Now I checked User Mapping of both users under Security/Logins of the server, each database's dbo is mapped to the database creator( admin1 for test 1's dbo and admin2 for test2's dbo). That's fine.

Step4.
Now I login as admin2 to SQL mgmt studio and was able to do anything on both test1 and test2 databases. Why? I don't give any permission on admin2 to database test1, but what's the logic admin2 can do anything?
0
Comment
Question by:crcsupport
  • 2
3 Comments
 
LVL 53

Accepted Solution

by:
Vitor Montalvão earned 2000 total points
ID: 40432955
I gave both accounts with 'public' and 'sys_admin' under Security/User properties/Server Role.
SysAdmin role it's the maximum an user can achieve in SQL Server. Means that he's the administrator and can do anything so there's no need to explicitly give him permission since it already have all permissions he can have.
0
 
LVL 1

Author Comment

by:crcsupport
ID: 40432959
So, if I give sys_admin role to any account, user mapping and securables seem not useful to touch?
0
 
LVL 53

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 2000 total points
ID: 40433005
Yes. Usually only a DBA has SysAdmin role.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question