automate 'generate scripts' using sqldmo

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

automate 'generate scripts' using sqldmo

Author

Topic

blah123
Starting Member

3 Posts

Posted - 2008-11-24 : 10:33:28

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?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-24 : 10:40:14

GRANT VIEW?

You mean GRANT SELECT? Or GRANT EXECUTE on {view name here}?

E 12°55'05.63"
N 56°04'39.26"

blah123
Starting Member

3 Posts

Posted - 2008-11-24 : 15:21:09

no..
GRANT VIEW DEFINITION ON spNames TO asdf
-- gives read permission on the sp

sry for the confusion -
this doesn't have anything to do with 'views'(pseudo tables)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources