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

SCOPE_IDENTITY vs @@IDENTITY

Would I be correct in understanding that SCOPE_IDENTITY( ) gives a finer
level of isolation than @.@.IDENTITY? That is, is it ever possible that between
inserting a row into a table and subsequently retrieving the @.@.IDENTITY value
that another row could have been inserted into the same table by another user
(even if I retrieve @.@.IDENTITY immediately after my INSERT)? It would seem to
me that as SCOPE_IDENTITY( ) is scope specific that its less likely to be
affected by the above scenario.@.@.IDENTITY and SCOPE_IDENTITY are session specific, so you will never see
identity values generated by other concurrent users. But the difference
between the two is explained in SQL Server Books Online documentation of
SCOPE_IDENTITY
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"phil" <phil@.discussions.microsoft.com> wrote in message
news:ED79C78A-155C-419A-9862-CAEE2164ECCD@.microsoft.com...
Would I be correct in understanding that SCOPE_IDENTITY( ) gives a finer
level of isolation than @.@.IDENTITY? That is, is it ever possible that
between
inserting a row into a table and subsequently retrieving the @.@.IDENTITY
value
that another row could have been inserted into the same table by another
user
(even if I retrieve @.@.IDENTITY immediately after my INSERT)? It would seem
to
me that as SCOPE_IDENTITY( ) is scope specific that its less likely to be
affected by the above scenario.|||If I might add a note... There is a time when @.@.identity can give you the
incorrect information, and that is when there are triggers on the table...
So you are correct in your thinking about scope_identity - it is the one you
should probably be using...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"phil" <phil@.discussions.microsoft.com> wrote in message
news:ED79C78A-155C-419A-9862-CAEE2164ECCD@.microsoft.com...
> Would I be correct in understanding that SCOPE_IDENTITY( ) gives a finer
> level of isolation than @.@.IDENTITY? That is, is it ever possible that
> between
> inserting a row into a table and subsequently retrieving the @.@.IDENTITY
> value
> that another row could have been inserted into the same table by another
> user
> (even if I retrieve @.@.IDENTITY immediately after my INSERT)? It would seem
> to
> me that as SCOPE_IDENTITY( ) is scope specific that its less likely to be
> affected by the above scenario.|||If u perform INSERTING, and u have TRIGGER that also perform some INSERT
so @.@.IDENTITY will be of table that TRIGGER inserts
--
Message posted via http://www.sqlmonster.com