Script DB Object Level Security with SQL2005

We regularly need to take production DB backups and restore them onto our
development SQL Server boxes for testing (SQL2005 SP2). Since DB level
security is much different between production and stage, we need to script
stage rights (server and object level) and refresh those rights onto the
restored production DB. I was able to do that with EM in SQL 2000, but am
unable to do it with 2005 (SP2). I have tried (I think) every possible
option in Management Studio under Tasks / Generate Scripts for the DB and
none generate SQL for object level security. Is this a "bug" with SQL2005
SP2? I believe we were able to do it with SP1, but I don't recall which
options we chose.
--
KevinKevinL (KevinL@.discussions.microsoft.com) writes:
> We regularly need to take production DB backups and restore them onto our
> development SQL Server boxes for testing (SQL2005 SP2). Since DB level
> security is much different between production and stage, we need to script
> stage rights (server and object level) and refresh those rights onto the
> restored production DB. I was able to do that with EM in SQL 2000, but am
> unable to do it with 2005 (SP2). I have tried (I think) every possible
> option in Management Studio under Tasks / Generate Scripts for the DB and
> none generate SQL for object level security. Is this a "bug" with SQL2005
> SP2? I believe we were able to do it with SP1, but I don't recall which
> options we chose.
There is a scripting option "Script Object-level Permissions". that you
can use when you use the Generate Scripts task.
Under Tools->Options there is a new page "Scripting" where you can set
options for when you script individual objects from Object Explorer.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks for the reply.
That appears to have resolved the problem (bug?) with scripting object level
permission, but role membership is still not scripted. Any idea why that
might be?|||KevinL (KevinL@.discussions.microsoft.com) writes:
> That appears to have resolved the problem (bug?) with scripting object
> level permission, but role membership is still not scripted. Any idea
> why that might be?
How do you script? When I script a database by right-clicking it, selecting
Tasks/Generate Scripts and include both Database Roles and Users I do get
role membership scripted at the end.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I see no option to script roles. This is what I did:
Open Management Studio, expand databases, right click the DB, click Tasks,
then Generate Script. The DB I right clicked is highlighted, I click next.
I verified the Script Options to make sure Script Object-Level Permissions i
s
True (there is no Option that mentions Roles) and click next. On Object
Types window choices are Schema, Stored Procedures, Tables, User-defined dat
a
types, User-defined functions and Users.
No matter which option(s) I choose, and I've tried every combination I can
think of, Role level permissions are not scripted.
--
Kevin
"Erland Sommarskog" wrote:

> KevinL (KevinL@.discussions.microsoft.com) writes:
> How do you script? When I script a database by right-clicking it, selectin
g
> Tasks/Generate Scripts and include both Database Roles and Users I do get
> role membership scripted at the end.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||KevinL (KevinL@.discussions.microsoft.com) writes:
> I see no option to script roles. This is what I did:
> Open Management Studio, expand databases, right click the DB, click
> Tasks, then Generate Script. The DB I right clicked is highlighted, I
> click next. I verified the Script Options to make sure Script
> Object-Level Permissions is True (there is no Option that mentions
> Roles) and click next. On Object Types window choices are Schema,
> Stored Procedures, Tables, User-defined data types, User-defined
> functions and Users.
> No matter which option(s) I choose, and I've tried every combination I can
> think of, Role level permissions are not scripted.
If "Database roles" are not listed, this would indicate that you don't
have any user-defined roles in the database, only the pre-defined roles,
db_owner and the like. Indeed, it appears that membership in these roles
are not scripted.
You can script all role membership with this SELECT:
SELECT 'EXEC sp_addrolemember ''' + r.name + ''', ''' + u.name + ''''
FROM sys.database_role_members rm
JOIN sys.database_principals u
ON rm.member_principal_id = u.principal_id
JOIN sys.database_principals r
ON rm.role_principal_id = r.principal_id
WHERE u.name <> 'dbo'
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx