Hi,

In SQL Server 2005 Management Studio how do I include object permissions
when scripting out objects?
When I script out a role or user, all I get is the sp_addrole or
sp_grantdbaccess statement with no associated GRANT statements.

In SQL Server 2000 Enterprise Manager, the script-out dialog box has it as a
script-time option.
In SQL Server 2008 Management Studio, the "Tools|Options..." dialog box has
is as a global option.

--
Thank you,

Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org

Daniel,

(Yes, SQL Server 2000 scripting was better in several ways.) But...

In SQL Server 2005 Management Studio, you can control the behavior of how
objects and object permissions are scripted through: Right-click on
Database, choose Tasks / Generate Scipts...

You can also set your default behavior by selecting the menu Tools /
Options, then choose Scripting.

If you only script out roles and users, however, you will not get the
permissions. If you script out the objects, the permissions granted against
those objects will be scripted out. If you only want to get the permissions
without the object scripts I don't believe that this tool will do it for
you. But here is a script:
http://vyaskn.tripod.com/scripting_p...erver_2005.htm

Role membership is not scripted at all, but here is a little script that
will create a script for adding role members:

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'

RLF

"Daniel Jameson" <no_djameson_spam@childrensoncologygroup.org> wrote in
message news:eblOYJhCJHA.5196@TK2MSFTNGP04.phx.gbl...
> Hi,
>
> In SQL Server 2005 Management Studio how do I include object permissions
> when scripting out objects?
> When I script out a role or user, all I get is the sp_addrole or
> sp_grantdbaccess statement with no associated GRANT statements.
>
> In SQL Server 2000 Enterprise Manager, the script-out dialog box has it as
> a script-time option.
> In SQL Server 2008 Management Studio, the "Tools|Options..." dialog box
> has is as a global option.
>
> --
> Thank you,
>
> Daniel Jameson
> SQL Server DBA
> Children's Oncology Group
> www.childrensoncologygroup.org
>
>