November 18, 2008 at 10:24 am
is there a method/t-sql script to automate 'tasks->generate scripts' that we do from Object explorer in SSMS 2005?
(note: i'm not having/using VB, .NET,scptxfr.exe etc)
i used SQLDMO, but it doesn't script 'grant view definition' permissions
declare @oServer int
exec sp_OACreate 'SQLDMO.SQLServer', @oServer OUT
exec sp_OASetProperty @oServer, 'loginsecure', 'true'
exec sp_OAMethod @oServer, 'Connect', NULL, 'Abc' -- ('Abc' is server name)
exec sp_oamethod @oServer, 'Databases("pubs").storedprocedures("spNames").Script(103,"\\vss\dbo.spNames.storedprocedure.sql")'
exec sp_OADestroy @oServer
- '103' above is the options for drop/create/permissions/file ( http://msdn.microsoft.com/en-us/library/ms135191.aspx )
- got this script from http://www.dbazine.com/sql/sql-articles/larsen4
but if 'spNames' has these permissions
'exec to asdf' &
'grant view definition to asdf'
it only scripts 'grant exec on spNames to asdf', not 'grant view ...' - why?
November 25, 2008 at 3:31 pm
SQLDMO doesn't seem to script 'grant view definition' permissions - any idea why ?
looks like MS isn't supporting DMO (COM), but SMO (.NET)
But is there a non .NET/VB/scptxfr.exe (i.e only t-sql) way to 'generate scripts' for objects?
November 25, 2008 at 4:52 pm
DMO was written for SQL Server 2000 and lower. The security scheme was significantly changed for SQL Server 2005. I do not think that a permission named "VIEW" existed for SQL Server 2000.
So, you should be using SMO, not DMO.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply