Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Aug 24, 1999

Automated Database Schema Scripting (DMO) - Page 5

By Bill Wunder








DECLARE @procedure varchar(255),                                                                    
         @objectName varchar(30),                                                                    
         @file varchar(255),                                                                         
         @user varchar(30)                                                                           
 SET NOCOUNT ON                                                                                      
  DECLARE object_cursor CURSOR
  FOR                                                                                                
  SELECT DISTINCT o.name, c.text, u.name                                                             
  FROM master..sysobjects o, master..syscomments c,                                                  
       master..sysprotects p, master..sysusers u                                                     
  WHERE c.id = o.id                                                                                  
  AND p.id = o.id                                                                                    
  AND p.uid = u.uid                                                                                  
  AND o.type = "X"                                                                                   
  AND o.category = 0                                                                                 
 OPEN object_cursor                                                                                  
   FETCH NEXT FROM object_cursor INTO @objectName, @file, @user                                      
     WHILE (@@fetch_status <> -1)                                                                    
       BEGIN                                                                                         
         SELECT @procedure = "EXECUTE sp_addextendedproc " + CHAR(39) +                              
                             @objectName + CHAR(39) + ", " + CHAR(39) +                              
                             @file + CHAR(39)                                                        
         PRINT @procedure                                                                            
         PRINT "GO"                                                                                  
         SELECT @procedure = "GRANT EXECUTE  ON master.." +                                          
                             @objectName + " TO " + @user                                            
         PRINT @procedure                                                                            
         PRINT "GO"                                                                                  
         PRINT ""                                                                                    
     	 FETCH NEXT FROM object_cursor INTO @objectname, @file, @user                                  
      END                                                                                            
 DEALLOCATE object_cursor



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date