SQL Security Best Practices - Dealing With User Name Changes

  • Are there any Best Practices from Microsoft or anyone else on how best to deal with SQL Logins & DB Users when you have bname changes with your users.

    For example, if SQL Logins and DB Users are created as firstname.lastname and the user Mary Jones (setup as mary.jones) gets married and changes to Mary Smith is it considered best to change the existing SQL Login & DB User from mary.jones to mary.smith or is it better to create a new SQL Login & DB User called mary.smith and then disable the mary.jones login & DB User?

    BTW - I don't know if this kind of naming convention for logins & DB Users is considered bad practice but we do it because it then matches how users are setup within our primary application and the DB it uses and this allwsus to do things that would otherwise not be possibel (by matching Logins/DBUser with the app user).

    Thanks

    Kindest Regards,

    Just say No to Facebook!
  • If that are windows logins, why granting them individually? You could create windows (AD) groups, and sql login for that group.

    Managing rights will be much easier on the group level, and name changes won't affect you.

    If you want some individuals to have different rights, create another group and put them in there.

    If there would be as many groups as users (very, very unlikely), you are out of luck. You have to manage them as individual logins and in that case I would change the name of the login. That will not break internal dependencies and rights since they are based upon internal ID's (SID, sort of GUID), not the login name.

    _____________________________________________________
    Microsoft Certified Master: SQL Server 2008
    XDetails Addin - for SQL Developers
    blog.sqlxdetails.com - Transaction log myths
  • Vedran is right. If you manage security via AD Groups, all you need to do is remove the old login from the group - typically server team does this, and have the new login added to the domain group.

    SQL Logins are typically meant for application logins. People generally are required to use their domain logins to connect to SQL.

    I have also seen some people using same old domain login even after name change.

  • AD Groups as SQL Logins and Database Users work great unless you need the User to have a default schema. In SQL 2012 the loop has been closed, but this is a 2008 forum.

    @yslguru, I would look to rename them so they match the person's new name. Last name changes are pretty common and I rename Database Users and Server Logins all the time to keep up. Are you storing the Database User name in any audit tables? If the Database User name is used to maintain an audit trail then you may have a bit more to consider.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • The person's login id is often some combination of last name and first name, so you need to change the name there.

    However, internally you should assign each person/user a unique id, and use that id in all tables that are not extremely short term (data is used for less than one day then discarded).

    IOW, "mary.jones" may be part of the person's login, but should not be stored in internal tables such as usage history, etc.. Instead "mary.jones" would be, say, id#1875431, and that id would be used in all internal and permanent tables. Looking up id#1875431 yields the name "mary jones" ... until she changes her name, when it yields some other name, but the name change does not require any changes to any historical table data.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • Vedran Kesegic (1/11/2013)


    If that are windows logins, why granting them individually? You could create windows (AD) groups, and sql login for that group.

    Managing rights will be much easier on the group level, and name changes won't affect you.

    If you want some individuals to have different rights, create another group and put them in there.

    If there would be as many groups as users (very, very unlikely), you are out of luck. You have to manage them as individual logins and in that case I would change the name of the login. That will not break internal dependencies and rights since they are based upon internal ID's (SID, sort of GUID), not the login name.

    We are working within the bounds of the application (our primary accounting software which uses a SQL Server DB for data storage) and so our choices of authentication type are limited to SQL Security only. NT Authentication will not work with the apps own proprietary security. As is common the app developers created their own security scheme instead of using what was already provided in SQL Server.

    Within the aps security mechanism each user does have a unique numeric ID that is never seen (at least not from within the app) and so changes there do not matter. Where the name change is an issue comes into play when we are trying to sync into the apps processes (via T-SQL) and capture information that the app should but does not such as data change auditing. By assigning users with logins that match their name (which is how the application admins set up users within the app) we are able to do things that even the aps maker had previously said was not realistic. Thanks to native SQL items like ORIGINAL_LOGIN we can link up process to users that the app does not naturally provide.

    If we had our users setup per the software vendors specs then every user would be connecting in as the login that owns the DB. That means that 50 users connections all come in as the same ID and it becomes impossible to determine who is doing what unless the apps native security functionality provides a way to do this and it doesn't. So while the use of names to logins via standard SQL Security is not the best choice under ideal circumstances its what we have to work with for now.

    Thanks for posting

    Kindest Regards,

    Just say No to Facebook!
  • ScottPletcher (1/13/2013)


    The person's login id is often some combination of last name and first name, so you need to change the name there.

    However, internally you should assign each person/user a unique id, and use that id in all tables that are not extremely short term (data is used for less than one day then discarded).

    IOW, "mary.jones" may be part of the person's login, but should not be stored in internal tables such as usage history, etc.. Instead "mary.jones" would be, say, id#1875431, and that id would be used in all internal and permanent tables. Looking up id#1875431 yields the name "mary jones" ... until she changes her name, when it yields some other name, but the name change does not require any changes to any historical table data.

    The tables within the DB the application uses to store user info does assign unique numeric value to each user. The name consisting of first & last is for front end uses/purpose.

    Kindest Regards,

    Just say No to Facebook!
  • Yes, I would change the specific SQL user login to match the person's name, if all/part of the name itself is being used as part of the login.

    Use roles, where possible, to assign permissions to logins, as that will simplify management of permissions.

    Have a script that "converts" the old login to the new login.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • opc.three (1/12/2013)


    AD Groups as SQL Logins and Database Users work great unless you need the User to have a default schema. In SQL 2012 the loop has been closed, but this is a 2008 forum.

    @yslguru, I would look to rename them so they match the person's new name. Last name changes are pretty common and I rename Database Users and Server Logins all the time to keep up. Are you storing the Database User name in any audit tables? If the Database User name is used to maintain an audit trail then you may have a bit more to consider.

    The schema that the app assumes for everything is 'dbo'. As far as auditing storage goes we are storing the name (as text) when auditing something that the app does not manage itself. For example, when someone makes a configuration change to an Account Number in our apps chart of accounts we capture the data before the change along with the date/time and name/ID of the user making said change. We don't store the SQL Login/DB User (as setup within SQL Server), just the user ID info the app has. We use the SQL Login & DB Users to identofy what SPID belongs to whom when monitoring. Else every SPID would be listed as dbo and we'd have no idea who is doing what.

    Thanks

    Kindest Regards,

    Just say No to Facebook!
  • YSLGuru (1/14/2013)


    opc.three (1/12/2013)


    AD Groups as SQL Logins and Database Users work great unless you need the User to have a default schema. In SQL 2012 the loop has been closed, but this is a 2008 forum.

    @yslguru, I would look to rename them so they match the person's new name. Last name changes are pretty common and I rename Database Users and Server Logins all the time to keep up. Are you storing the Database User name in any audit tables? If the Database User name is used to maintain an audit trail then you may have a bit more to consider.

    The schema that the app assumes for everything is 'dbo'.

    If by 'assume' you meant that the app does not schema-qualify all object references and instead relies heavily on the default schema setup for each Database User then moving to AD Groups will not work for you until you move to SQL 2012.

    As far as auditing storage goes we are storing the name (as text) when auditing something that the app does not manage itself. For example, when someone makes a configuration change to an Account Number in our apps chart of accounts we capture the data before the change along with the date/time and name/ID of the user making said change. We don't store the SQL Login/DB User (as setup within SQL Server), just the user ID info the app has. We use the SQL Login & DB Users to identofy what SPID belongs to whom when monitoring. Else every SPID would be listed as dbo and we'd have no idea who is doing what.

    Thanks

    In light of this info I would say there is no reason why you should not rename the Database Users and Server Logins to match the person's real, new, name.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • opc.three (1/14/2013)


    YSLGuru (1/14/2013)


    opc.three (1/12/2013)


    AD Groups as SQL Logins and Database Users work great unless you need the User to have a default schema. In SQL 2012 the loop has been closed, but this is a 2008 forum.

    @yslguru, I would look to rename them so they match the person's new name. Last name changes are pretty common and I rename Database Users and Server Logins all the time to keep up. Are you storing the Database User name in any audit tables? If the Database User name is used to maintain an audit trail then you may have a bit more to consider.

    The schema that the app assumes for everything is 'dbo'.

    If by 'assume' you meant that the app does not schema-qualify all object references and instead relies heavily on the default schema setup for each Database User then moving to AD Groups will not work for you until you move to SQL 2012.

    I don't think that's true. While you cannot assign a default schema to the login, you can to the user. You just have to set it to "dbo" for each db for that group instead of at the login level.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • ScottPletcher (1/14/2013)


    opc.three (1/14/2013)


    YSLGuru (1/14/2013)


    opc.three (1/12/2013)


    AD Groups as SQL Logins and Database Users work great unless you need the User to have a default schema. In SQL 2012 the loop has been closed, but this is a 2008 forum.

    @yslguru, I would look to rename them so they match the person's new name. Last name changes are pretty common and I rename Database Users and Server Logins all the time to keep up. Are you storing the Database User name in any audit tables? If the Database User name is used to maintain an audit trail then you may have a bit more to consider.

    The schema that the app assumes for everything is 'dbo'.

    If by 'assume' you meant that the app does not schema-qualify all object references and instead relies heavily on the default schema setup for each Database User then moving to AD Groups will not work for you until you move to SQL 2012.

    I don't think that's true. While you cannot assign a default schema to the login, you can to the user. You just have to set it to "dbo" for each db for that group instead of at the login level.

    From 2008 R2 BOL:

    DEFAULT_SCHEMA cannot be specified when you are creating a user mapped to a Windows group, a certificate, or an asymmetric key.

    Try it out though. You get an error when trying to set a default schema for a Database User based on a Login created from a Windows Group on 2008 R2 or below.

    USE test

    CREATE USER [DOMAIN\MyGroup] FROM LOGIN [DOMAIN\MyGroup] WITH DEFAULT_SCHEMA = [dbo];

    Msg 15259, Level 16, State 1, Line 2

    The DEFAULT_SCHEMA clause cannot be used with a Windows group or with principals mapped to certificates or asymmetric keys.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply