SQL2005 - how to restore users, roles, permissions

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

tom d - 30 Dec 2008 23:56 GMT

Hi,
I have a task to restore a prod db to DEV and I want to keep all users,
roles along with all the permission in DEV like they were before the restore.
Does anyone know what would be the best way to handle this? Is there a way to
script out all users, roles, permissions that I currently have on DEV db
before the restore so that I can re-apply after the restore?

Thanks,
TD

Reply to this Message

Uri Dimant - 31 Dec 2008 09:20 GMT

Tom
Take a look at RedGate tools

SELECT
dp.Class,
dps1.Name As Grantee,
dps2.Name As Grantor,
so.Name,
so.Type,
dp.Permission_Name,
dp.State_Desc
FROM sys.database_permissions AS dp
JOIN Sys.Database_Principals dps1
ON dp.grantee_Principal_ID = dps1.Principal_ID
JOIN Sys.Database_Principals dps2
ON dp.grantor_Principal_ID = dps2.Principal_ID
JOIN sys.objects AS so
ON dp.major_id = so.object_id

WHERE....

> Hi,
> I have a task to restore a prod db to DEV and I want to keep all users,
[quoted text clipped - 7 lines]
> Thanks,
> TD

Reply to this Message

Erland Sommarskog - 31 Dec 2008 13:57 GMT

> I have a task to restore a prod db to DEV and I want to keep all users,
> roles along with all the permission in DEV like they were before the
> restore. Does anyone know what would be the best way to handle this? Is
> there a way to script out all users, roles, permissions that I currently
> have on DEV db before the restore so that I can re-apply after the
> restore?

BACKUP-RESTORE will retain all users, permissions etc. Everything that is
contained within the database.

What it will not retain is the mapping between users and server logins, at
least not for SQL Server logins. (And obviously neither for windows logins,
that are not available on the development server.)

Signature

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Reply to this Message

Nigel Ainscoe - 31 Dec 2008 17:53 GMT

Two up for Redgate Tools. You could do it with the free trial of SQL Compare
and about a one day learning experience. I did a job like this once using
the free trial and I was so impressed that I bought the full version the
next week. It has saved me a fortune.

I think Erland missed that you wanted to be able to re-apply the Dev roles
and permissions after the restore rather than have the roles from prod
transfer to the dev machine.

Nigel Ainscoe

> Hi,
> I have a task to restore a prod db to DEV and I want to keep all users,
[quoted text clipped - 7 lines]
> Thanks,
> TD

Reply to this Message

Erland Sommarskog - 31 Dec 2008 23:47 GMT

> Two up for Redgate Tools. You could do it with the free trial of SQL
> Compare and about a one day learning experience. I did a job like this
[quoted text clipped - 4 lines]
> and permissions after the restore rather than have the roles from prod
> transfer to the dev machine.

Sorry about that. I was misled by "keep". If you restore a database
over another database, there is nothing you can keep from the old, as
it is a complete replacement. You can however recreate things like users
and permissions etc.

There was a similar post a few weeks back to which I replied:

I can't think of any convenient and quick way to script all
permissions. You could script the entire database, the one you are
about to replace, by right-clicking the database and selecting
Tasks->Generate Scripts. Opt to script all objects, and make sure that
you have "Script object-level permissions". You would then have to
extract the GRANT statements from the file. You should definitely take
some samples, to make sure that it includes all sorts of permission you
know that you have used.

Nigel's suggestion to try SQLCompare is probably a lot better one.
It's probably even better if you rename the current dev database before
you restore, so you can compare both databases. But if a shortage on
disk space, you can script the database to disk with SQL Compare, and
use that as your comparison base.

Signature

Reply to this Message