SQL 2012, Login and user mapping
Posted on 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.
I created two AD accounts, admin1 and admin2.
I gave both accounts with 'public' and 'sys_admin' under Security/User properties/Server Role.
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'
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.
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?