Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Oct 2003
    Posts
    93

    Unanswered: Tool to help migrate SQL Logins and User Permissions

    I know that there is Microsoft KB to help migrate SQL Logins but it doesnt take care of Login Server level permissions or User level permissions.

    Idera used to have a Free tool SQLPermisions.exe but it works only on Windows XP/Vista not on Windows 7.

    Can somebody recommend a third party tool (free or paid) which can help in migrating SQL Logins and User permissions ?

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Why do you need a tool for a trivial thing like this? You can start with this query, and then modify it to form GRANT/DENY statements off of it:
    Code:
    select
     PermissionState=prmssn.[state]
    ,Grantor=grantor_principal.name
    ,Grantee=grantee_principal.name
    ,PermissionTypeCode=prmssn.[type]
    ,PermissionName=prmssn.permission_name
    ,SecurableClass=prmssn.class
    ,SecurableClassName=prmssn.class_desc
    from sys.server_permissions prmssn
    inner join sys.server_principals grantor_principal
    on grantor_principal.principal_id = prmssn.grantor_principal_id
    inner join sys.server_principals grantee_principal
    on grantee_principal.principal_id = prmssn.grantee_principal_id
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Oct 2003
    Posts
    93
    The code doesnt work. It doesn't pick up any server level roles like sysadmin,bulk_insert etc.
    Neither does it do anything for User permissions.

    If you would have used a tool like SQLPermissions.exe, you would understand the how convenient it is to migrate logins and user permissions using that tool.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Taking mgupta's quest for simplicity to the extreme you could just create a server with no password on the sa account, then have everyone use sa. No permissions issues (everyone can do everything), no auditing (it isn't possible), the simplest of all security models!

    It is possible to acheive what you want and move the SQL Authenticated logins from one machine to another, recreate the Windows Authenticated logins, then re-apply the server level permissions. The problem with this process is that there are many issues that you need to consider, and it is far safer to do it manually until you understand the ramifications of this action... Depending on the value of your data, this can put you (and your organization) at serious risk if you don't think the process through.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Oct 2003
    Posts
    93
    Pat,
    Thanks for your reply.
    Last edited by mgupta; 08-02-12 at 13:50.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You're welcome. Good advice is often hard to understand at first, and it can seem stupid until you learn enough to grasp the problem.

    If you genuinely think that my suggestion is stupid, please explain why you think that. If you don't like my answer or don't understand my reasoning and your dislike makes it "stupid" to you, you don't need to carry this discussion any further... I'm Ok with that.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Oct 2003
    Posts
    93
    Pat,
    I have a PROD Database with 150 Users/Logins. For Simplicity, Lets assume the same 150 Users are there in QA too but they have different access than PROD at the
    Server level and Database Level.

    When I do a restore of the DB from PROD to QA, how do I maintain the QA Users access ?

    How is your suggestion helping me in this scenario ?

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Let me summarize to make sure I understand your scenario.

    You have 150 database users, which implies that you have 150 SQL Server logins. The permission levels are expected to be different in the destination (QA) database than the permissions are in the source (Production) database. The server permissions for the associated logins are also expected to be different.

    First and foremost, using a copy of production for QA is a common practice in many enterprises, but it is definitely not an accepted best practice in the industry. This has nothing to do with the problem that you've expressed, but it is a larger problem from the audit perspective if any of your data is governed by HIPPA, SOX, etc. If you want to pursue this issue, we really ought to create a separate thread for it.

    Because you want to create QA logins that have new permissions, you should probably just do that instead of trying to copy the production logins. Creating new logins, using new names, and assigning new permissions will ensure that your QA database has exactly the logins and users that your QA team needs, that these logins and users have exactly the permissions that the QA team needs to test, and that no testing artifacts are induced by accidental reuse of production security/permissions.

    Maybe I'm missing something, but this sounds like a "poster child" example of why no one that I know advocates copying security except when upgrading a server in place for example when you are replacing an old production server with a new one. Creating new logins and users seems like the best solution available for the problem as you've stated it.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Oct 2003
    Posts
    93
    Pat,
    Now that you have understood the scenario , I dont want to copy the production logins (at lease most of time that is the case).
    I need to have script to recreate all the permissions for a given Database in QA Env at the User Level/Server Level which can run after restore.
    Thats where I use the idera tool "SQLPermissions". I connect the tool to QA Server and it creates a script for all the logins and user level permissions for the Database to be Restored.
    After the restore, I delete all the users from the Database and run the script to recreate with reqd permissions.

    Since this tool doesn't work with Windows 7, I am trying to find some other third party tool or script to help with this scenario ?

  10. #10
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Hey gupta, I said "to start with". But now, since I understand what kind of personality you have, - you write your own script! If I were with a little bit of a power on this forum, - I would have banned you for responses like this. So I'm kind of glad I am not
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  11. #11
    Join Date
    Oct 2003
    Posts
    93

    Red face

    Quote Originally Posted by rdjabarov View Post
    Hey gupta, I said "to start with". But now, since I understand what kind of personality you have, - you write your own script! If I were with a little bit of a power on this forum, - I would have banned you for responses like this. So I'm kind of glad I am not
    All I wrote that the code you mentioned doesn't work. If I knew how to write the script fully, I would have not requested for the help. That's why I use Idera's Tool.
    If you can't help, its fine, but don't try to trivialize my request . Infact you don't even understand the requirements completely.
    I don't understand what bugged you so bad ?

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Uff-da! Now I'm more confused than when I started.

    SQL Server has logins. Logins control access to the SQL Server itself, and can have server level permissions (like administrator, create database, etc).

    Databases have users. Users and logins can be connected, which means that a given login then has access to the database where the matching user exists.

    Database and object permissions are granted to users. These permissions describe a relationship between a user and an object (database, table, view, procedure, etc), they don't exist in any abstract form.

    The Idera SQL Permissions tool would create a script that described every login, user, and granted permission for a given machine. The purpose was to allow you to "clone" the security structure for a Windows Server 2003 (or earlier) and SQL Server 2000. This worked reasonably well for the environment in which it was designed, but as security within Windows and SQL has become more complex this kind of tool rapidly became a "route to ruin" so often that Idera has not upgraded their tool.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  13. #13
    Join Date
    Oct 2003
    Posts
    93
    Pat,
    Thanks for all the details.
    Idera tool will let you choose 'ALL Databases" or "Any no of Databases" and it works perfectly for creating a script to be run after Restore.

  14. #14
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Cheeseburger
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  15. #15
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    It almost sounds like you are dropping the login for each restore, and you would only have to deal with the user in the database, if the SIDs really are different.

    I can not think of any case where a user database restore overwrites or changes a login's permissions at a server level. That being the case, why don't you create your logins with the same SID in each environment, so the user mappings remain the same when a prod database is restored on the QA server?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •