locked
SQL Server Security > Logins vs. Databases > Security > Users RRS feed

  • Question

  • Why isn't all security needs done just in logins?  When migrating databases, the Logins are not brought over when restoring a backup.  It does not make sense.  Could it not be done all just in the Security > Logins area or why not?  That COULD be designed to track users and rights on various databases rather than a thing owned by each database....

    Thanks for your time.

    Gib
    Wednesday, February 24, 2010 5:36 PM

All replies

  • Hi Gib,

    When you are moving the databases , only the users get moved along with the database and the login doesn't.
    In SQL server if an user is existing without any login for them they are specified as mismatch or orphan users . To overcome this problem , you should use sp_change_users_login and sp_help_revlogin to check the mismatch users and move SQL server logins from instance to instance respectively. The moving of logins should be done manually , though it sounds like an extra step when a db is restored it is how SQL server is designed.
    Now coming to your actual question of why not just use only logins , there are several reasons like allocating access to only certain databases for a login (hence he becomes user on that db) - if all logins are allowed to all databases then i would see no secured approach and it will be like having excessive privilege to data.
    Thanks, Leks
    Wednesday, February 24, 2010 5:54 PM
  • Thanks for your reply Leks, that I all knew, but thanks anyway.

    You said " if all logins are allowed to all databases then i would see no secured approach and it will be like having excessive privilege to data."

    No that's not true, or at least doesn't have to be true. I was saying that SQL Server *should* be designed(and it could - if not why) so that the logins do all the functionality. After all, when you create a new login, you map to databases you want theat login to have access to and then also check what roles withing each individual database they are to have.

    I was not advocating giving all login access to all databaes. 

    In SQL Server Management Studio, when I drill into Security > Logins > right click on a user > select properties and then select User Mappings you can control it all from there and choose what database each login has access to.  That part is great.  You can even control what they are authorized to do in each database.  But after creating a user and assigning mappings and access levels to each db, once you create it, it also creates a User on the database with the mappings and authorizations.  This is really a duplicate and not needed if SQL Server were better desined to just use the Security Logins and not have a seperate object spawned that is put in the Users folder on each db.  Then we would not have to fix logins when migrating to another server.  Also the restore should check and change the domain of to be that of the new server.

    Again, thanks for your reply but I am looking to hear back from one of the developers/designers of SQL Server as to why this is the way it is and if they tried to make it that way before when coding it.  I was very curious since this seems to me like a very bad design but that's my perspective.  I am sure they had a reason, just curious what issues they ran into if they did try it the way I described.

    Hopefully I explained it thouroughly.

    Thanks!

    Gib
    Wednesday, February 24, 2010 6:13 PM
  • Gib,

    "In SQL Server Management Studio, when I drill into Security > Logins > right click on a user > select properties and then select User Mappings you can control it all from there and choose what database each login has access to.  That part is great.  You can even control what they are authorized to do in each database. 
    But after creating a user and assigning mappings and access levels to each db, once you create it, it also creates a User on the database with the mappings and authorizations. 
    This is really a duplicate and not needed if SQL Server were better desined to just use the Security Logins and not have a seperate object spawned that is put in the Users folder on each db.  Then we would not have to fix logins when migrating to another server.  Also the restore should check and change the domain of to be that of the new server.
    "

    The basic of SQL Server Security model is;

  • A login is used for user authentication
  • A database user account is used for database access and permissions validation.

    Believe you are aware of that, if no user account exists in a database for a specific login, the user that is using that login cannot access the database even though the user may be able to connect to SQL Server. The single exception to this situation is when the database contains the "guest" user account. A login that does not have an associated user account is mapped to the guest user. Conversely, if a database user exists but there is no login associated, the user is not able to log into SQL Server server.

     It is not a dublicate, rather it is designed to administrate the SQL Server at server instance level & database level efficiently.

    •  It gives much finer level control on administrating the SQL Server.

    When we look at the Security administration on SQL Server is a bigger part. It have the scope of

    1. SQL Server Instance Level
    2. Database Level

    A login with 'securityadmin' priviliges can manage logins at SQL Server instance level. This 'securityadmin' privileges is at higher level does not required to manage the security only to a specific database.

     

    In a big environment with 100 of SQL Server Instances, there would be  DBA with "security administrator" to create and manage the logins at the SQL Server instance level.

    When a database login is created and granted with database access within the application perspective managing the user become role of the user with "db_securityadmin"

     

    securityadmin

    Members of the securityadmin fixed server role manage logins and their properties. They can GRANT, DENY, and REVOKE server-level permissions. They can also GRANT, DENY, and REVOKE database-level permissions. Additionally, they can reset passwords for SQL Server logins

    db_securityadmin

    Members of the db_securityadmin fixed database role can modify role membership and manage permissions. Adding principals to this role could enable unintended privilege escalation.


  • Sivaprasad S http://sivasql.blogspot.com Please click the Mark as Answer button if a post solves your problem!
Sunday, September 12, 2010 12:06 AM
  • Please understand the below points first

    Login are being used to access the server (Server level)

    Database users are being used to access the database.(Database level)

    Why isn't all security needs done just in logins?

    Just Imagine the below things:

    1. I want give the permission to user to take backup for specific databases in this case we will give databasedb_backupoperator role. Is this possible with server role to take backup for specific databases
    2. I want give the permission to user to run the BULK INSERT statement on databases in this case we will give bulkadmin server role. Is this possible with database role to run the BULK INSERT statement on databases 

    Thanks-Vanchan

    Please click the Mark as Answer button if a post solves your problem!


    • Edited by Vanchan Tuesday, April 15, 2014 2:53 PM re
    Tuesday, April 15, 2014 11:15 AM
  • Understand the below points first

    Login are being used to access the server (Server level)

    Database users are being used to access the database.(Database level)

    Why isn't all security needs done just in logins?

    Just Imagine the below things:

    1. I want give the permission to user to take backup for specific databases in this case we will give databasedb_backupoperator role. Is this possible with server role to take backup for specific databases
    2. I want give the permission to user to run the BULK INSERT statement on databases in this case we will give bulkadmin server role. Is this possible with database role to run the BULK INSERT statement on databases 

    Thanks-Vanchan

    Please click the Mark as Answer button if a post solves your problem!

    Tuesday, April 15, 2014 12:05 PM
  • Why isn't all security needs done just in logins?  When migrating databases, the Logins are not brought over when restoring a backup.  It does not make sense.  Could it not be done all just in the Security > Logins area or why not?  That COULD be designed to track users and rights on various databases rather than a thing owned by each database....

    Much of this ugliness is due to the legacy Sybase architecture where logins are used for instance-level authentication and users for database-level authorization.  This establishes a logical foreign key relationship between logins and users that can break due to a restore.

    You can create a partially contained database, where authentication is done at the database level.  That will allow you to restore without worrying about the login/user mapping.  See http://technet.microsoft.com/en-us/library/ff929071.aspx


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Tuesday, April 15, 2014 12:18 PM