Post reply

Database users permissions in SQL Server 2005

  • July 5, 2011 at 7:27 am

    #253825

    Hi,

    I have to do database refresh on development environment. How can I retain permissions and rights on development server. On production server I have many users who have column level permissions so if I restore backup of production on development, how can i retain all the permissions and rights on development server.

    Thanks in advance.

  • July 5, 2011 at 7:42 am

    #1348278

    sp_change_users_login

  • July 5, 2011 at 9:12 am

    #1348347

    This stored procedure will fix the orphaned users, if i am not wrong but what about the permissions and rights of those database users which were on production and now on development after restoring the backup. Will they retain all permissions and rights as it is, as they were on production.

  • July 5, 2011 at 2:07 pm

    #1348565

    beejug1983 (7/5/2011)

    This stored procedure will fix the orphaned users, if i am not wrong but what about the permissions and rights of those database users which were on production and now on development after restoring the backup. Will they retain all permissions and rights as it is, as they were on production.

    Yes. Permissions are authorization data and are part of each database's metadata. If you link the database users to the SQL Server logins on the development instance after the restore they'll assume the permissions and you'll be fine.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 4 posts - 1 through 3 (of 3 total)

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