Post reply

Automatically Scripting Out Permissions for Cross Environment Restores

  • December 4, 2011 at 10:46 pm

    #252929

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

  • December 5, 2011 at 1:24 am

    #1416493

    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

  • December 5, 2011 at 3:41 am

    #1416540

    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.

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

  • December 5, 2011 at 5:59 am

    #1416623

    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.

  • December 5, 2011 at 6:06 am

    #1416630

    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

  • December 5, 2011 at 6:21 am

    #1416635

    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 🙂

  • December 6, 2011 at 2:29 am

    #1417161

    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

  • December 7, 2011 at 5:56 am

    #1418003

    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/

  • December 7, 2011 at 12:42 pm

    #1418337

    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

  • December 8, 2011 at 4:01 am

    #1418572

    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?

  • December 8, 2011 at 9:36 pm

    #1419042

    And a little bit more here :-

    http://www.mssqltips.com/sqlservertip/2142/retrieving-sql-server-permissions-for-disaster-recovery/?utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=2011127

    ----------
    Ashish

  • December 9, 2011 at 3:33 am

    #1419166

    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/

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

  • February 21, 2012 at 9:23 am

    #1449792

    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 12 (of 12 total)

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