"Tim Peterson" <Tim Peter***@discussions.microsoft.com> wrote in message
news:5363E45C-9EF4-4F34-8743-F172052AD2DD@microsoft.com...
>
> I have a SQL Server 2005 production database that has approximately 75
> users.
> I would like to restore the SQL Server 2005 production database into my
> development database.
>
> What the best way to script out the development users permissions to be
> applied to the development database after the restore from production
> database?
>
> Please help put together a script to complete this task.
>
> Thank You,

"Jonathan Kehayias" wrote:

> The permissions inside the database for the database users will be intact as
> soon as you restore the database.  However, if the Sql Server Logins that
> the Database Users map to don't exist in the target server, or they were
> created manually, you will have a SID mismatch, and they won't be able to
> actually access the database until you remap the users to the correct login
> SID.  You can do this using the sp_change_users_login stored procedure.
>
> Alternatively, you can script the logins on the production server using the
> scripter provided by Microsoft for the correct versions:
> http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=TransferLogins
>
> You can also use a SSIS Transfer Logins task to move the logins to the new
> server, and maintain the same SID which will match the SID of the Database
> Users and you won't have any problems when you restore the database.
>
> --
> Jonathan Kehayias
> SQL Server MVP
> http://jmkehayias.blogspot.com
> http://www.sqlclr.net
>
>
> "Tim Peterson" <Tim Peter***@discussions.microsoft.com> wrote in message
> news:5363E45C-9EF4-4F34-8743-F172052AD2DD@microsoft.com...
> >
> > I have a SQL Server 2005 production database that has approximately 75
> > users.
> > I would like to restore the SQL Server 2005 production database into my
> > development database.
> >
> > What the best way to script out the development users permissions to be
> > applied to the development database after the restore from production
> > database?
> >
> > Please help put together a script to complete this task.
> >
> > Thank You,
>
>
>

"Tim Peterson" <Tim Peter***@discussions.microsoft.com> wrote in message
news:5DCB676A-BD26-4000-B836-F0795EBB13E7@microsoft.com...
>
> So, if I restore production database into development database the
> permissions will not change after the database restore?
>
> So there will be a mismatch in SID, which can be corrected by the script
> listed in this website link.
> (http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=TransferLogins)
>
> Thank You,
>
>
> "Jonathan Kehayias" wrote:
>
>> The permissions inside the database for the database users will be intact
>> as
>> soon as you restore the database.  However, if the Sql Server Logins that
>> the Database Users map to don't exist in the target server, or they were
>> created manually, you will have a SID mismatch, and they won't be able to
>> actually access the database until you remap the users to the correct
>> login
>> SID.  You can do this using the sp_change_users_login stored procedure.
>>
>> Alternatively, you can script the logins on the production server using
>> the
>> scripter provided by Microsoft for the correct versions:
>> http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=TransferLogins
>>
>> You can also use a SSIS Transfer Logins task to move the logins to the
>> new
>> server, and maintain the same SID which will match the SID of the
>> Database
>> Users and you won't have any problems when you restore the database.
>>
>> --
>> Jonathan Kehayias
>> SQL Server MVP
>> http://jmkehayias.blogspot.com
>> http://www.sqlclr.net
>>
>>
>> "Tim Peterson" <Tim Peter***@discussions.microsoft.com> wrote in message
>> news:5363E45C-9EF4-4F34-8743-F172052AD2DD@microsoft.com...
>> >
>> > I have a SQL Server 2005 production database that has approximately 75
>> > users.
>> > I would like to restore the SQL Server 2005 production database into my
>> > development database.
>> >
>> > What the best way to script out the development users permissions to be
>> > applied to the development database after the restore from production
>> > database?
>> >
>> > Please help put together a script to complete this task.
>> >
>> > Thank You,
>>
>>
>>