Automatically Scripting Out Permissions for Cross Environment Restores

  • NJ-DBA

    SSChampion

    Points: 13832

    Comments posted to this topic are about the item Automatically Scripting Out Permissions for Cross Environment Restores

  • crazy4sql

    SSCoach

    Points: 19590

    Script is good if we have all the required logins in place else we still need to create the logins first.

    Also sp_change_users_login, report only about the orphaned sql login not the windows login.

    ----------
    Ashish

  • george sibbald

    SSC Guru

    Points: 104200

    Thanks for sharing Warren. If this is to be used on SQL2005 and above you really need to be switching to 'create user' syntax rather than sp_grantdbaccess, else the user will be probably be created with the incorrect default schema.

    ---------------------------------------------------------------------

  • NJ-DBA

    SSChampion

    Points: 13832

    crazy4sql (12/5/2011)


    Script is good if we have all the required logins in place else we still need to create the logins first.

    Also sp_change_users_login, report only about the orphaned sql login not the windows login.

    Yes- we are not creating logins. This is a cross envrionment database restore- if there are server scope objects required, then those need to be scripted out seprately.

    As for windows users- not sure what you are referring to. Windows users have the same SID and therefore do not become orphaned.

  • crazy4sql

    SSCoach

    Points: 19590

    As for windows users- not sure what you are referring to. Windows users have the same SID and therefore do not become orphaned.

    Consider this scenarios, If employee 'a' leave the job and in future join back. At the time when 'a' left job, the wondows account was just disabled at AD but not permanently deleted. Now when 'a' join back, the new id was created instead of enabling old id. In this case account name is same but SID is different.

    ----------
    Ashish

  • NJ-DBA

    SSChampion

    Points: 13832

    george sibbald (12/5/2011)


    Thanks for sharing Warren. If this is to be used on SQL2005 and above you really need to be switching to 'create user' syntax rather than sp_grantdbaccess, else the user will be probably be created with the incorrect default schema.

    That's a good point- your right. It does create the user with the user's name as the default schema instead of the more common 'dbo'. Pretty straightforward to correct for that- not sure how I didnt do that already 🙂

  • Fabrizio Faleni

    Ten Centuries

    Points: 1249

    Thanks for sharing your script.

    I would add another possible critical point: as this script's use could also be to be run against all databases for Disaster Recovery purposes, you may find problems if one or more Database collations are different from Server collation. I would advise to use the COLLATE clause in the query.

    MCITP Database administrator 2008
    MCTS SQL Server 2008 Implementation and maintenance
    MCTS Sharepoint configuration
    MCP Designing Deploying and Managing a Network Solution for the Small and Medium-sized Business
    ITIL V3 Foundation
  • Sean Elliott (UK)

    SSCommitted

    Points: 1626

    There is a free GUI tool you can get from Idera for doing this too. We use it at my site all the time. It is very good. I don't have any problems with it.

    http://www.idera.com/Products/Free-Tools/SQL-permissions/

  • Fabrizio Faleni

    Ten Centuries

    Points: 1249

    Yes, the Idera Tools are neat but they need to install components (SQL-DMO and SQL 2005 backwards compatibility) that might not be allowed in production servers: a script is a script and may be launched by a Job, executed from a SQLCMD batch, etc.

    MCITP Database administrator 2008
    MCTS SQL Server 2008 Implementation and maintenance
    MCTS Sharepoint configuration
    MCP Designing Deploying and Managing a Network Solution for the Small and Medium-sized Business
    ITIL V3 Foundation
  • Sean Elliott (UK)

    SSCommitted

    Points: 1626

    Agreed. At the time I wanted this capability there was no comprehensive script available. This capability should be SSMS already since it was in Enterprise Manager for SQL 2000. Amazing how these crucial features are dropped in favour of exotic GUI features that you don't need. 😛

    I'm too locked in to using Idera now. One day it will be in SSMS properly?

  • crazy4sql

    SSCoach

    Points: 19590

  • george sibbald

    SSC Guru

    Points: 104200

    I didn't want to hijack warrens script but since we seem to be going down the route of providing alternatives and this thread could become a good source for people looking for ways to extract this type of information here's another one:

    http://www.sqlservercentral.com/scripts/Permissions/76450/

    ---------------------------------------------------------------------

  • Aashini Shah

    Ten Centuries

    Points: 1129

    We have nightly restores to DEV environments, weekly to Test and Integration and on demand for Regression testing. There are crazy amounts of restore jobs running every day pulling from last night's backups.

    We have DBAdmin database created with some necessary scripts and work tables. We run the security scripting script as a step before the actual restore and save the data in work table. After the rstore step runs, we rerun teh security from step1. Production always have tighter security so only thing we do is to grant additional access that is not there in prod ( like db_owner to developers or read/write to QA).

    I was revieing Warren's script and there are few good features that we will be adopting from his scripts.

    Idera SQL Permission is ok when you have one or two instance and you don't mind doing lot of manual work. This kind of scripting is necessary for Enterprise environment when you are dealing with 100s and 1000s of instances and have no time for manual work.

    Thanks for the good script.

Viewing 13 posts - 1 through 13 (of 13 total)

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