SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Automatically Scripting Out Permissions for Cross Environment Restores


Automatically Scripting Out Permissions for Cross Environment Restores

Author
Message
NJ-DBA
NJ-DBA
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2319 Visits: 1546
Comments posted to this topic are about the item Automatically Scripting Out Permissions for Cross Environment Restores
crazy4sql
crazy4sql
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2747 Visits: 4514
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
george sibbald
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16904 Visits: 13695
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
NJ-DBA
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2319 Visits: 1546
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
crazy4sql
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2747 Visits: 4514


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
NJ-DBA
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2319 Visits: 1546
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 Smile
Fabrizio Faleni
Fabrizio Faleni
SSC-Enthusiastic
SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)

Group: General Forum Members
Points: 155 Visits: 456
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)
Sean Elliott (UK)
Old Hand
Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)

Group: General Forum Members
Points: 328 Visits: 181
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
Fabrizio Faleni
SSC-Enthusiastic
SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)

Group: General Forum Members
Points: 155 Visits: 456
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)
Sean Elliott (UK)
Old Hand
Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)

Group: General Forum Members
Points: 328 Visits: 181
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. :-P

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


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search