Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / DB Engine / SQL Server / August 2008

Tip: Looking for answers? Try searching our database.

SSMS 2005 Object Permissions Scripting

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Daniel Jameson - 29 Aug 2008 21:04 GMT
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.

Signature

Thank you,

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

Russell Fields - 30 Aug 2008 15:34 GMT
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_permissions_in_sql_server_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

> Hi,
>
[quoted text clipped - 7 lines]
> In SQL Server 2008 Management Studio, the "Tools|Options..." dialog box
> has is as a global option.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.