Script to change all database object owners at one time

Hi:
Does any one know where I can find a script that will change ownership of
all database objects at once. I took over control of a large database and
changing owners using sp_changeobjectowner one at a time will take forever.
Thanks,
CharlieYou could use a cursor to retrieve the objects that you want to change
(tables, views, stored procedures, user defined functions...) and call
sp_changeobjectowner on each object.
You can find examples of cursors within Books Online.
Keith
"Charlie@.CBFC" <charle1@.comcast.net> wrote in message
news:ueFBZXyzFHA.2076@.TK2MSFTNGP14.phx.gbl...
> Hi:
> Does any one know where I can find a script that will change ownership of
> all database objects at once. I took over control of a large database and
> changing owners using sp_changeobjectowner one at a time will take
> forever.
> Thanks,
> Charlie
>|||Since this is a one time thing, look at the UNdocumented stored procedure
sp_MSforeachtable.
This will give you an idea on how to use it:
http://www.databasejournal.com/feat...cle.php/3441031
http://www.dbazine.com/sql/sql-articles/larsen5
"Charlie@.CBFC" <charle1@.comcast.net> wrote in message
news:ueFBZXyzFHA.2076@.TK2MSFTNGP14.phx.gbl...
> Hi:
> Does any one know where I can find a script that will change ownership of
> all database objects at once. I took over control of a large database and
> changing owners using sp_changeobjectowner one at a time will take
> forever.
> Thanks,
> Charlie
>|||This will generate the script for you for all user-defined views, tables,
stored procedures and functions that aren't already owned by dbo:
SELECT 'EXEC sp_changeobjectowner
'''+TABLE_SCHEMA+'.'+TABLE_NAME+''',''dbo'''
FROM INFORMATION_SCHEMA.TABLES
WHERE OBJECTPROPERTY(OBJECT_ID(TABLE_SCHEMA+'.'+TABLE_NAME),
'IsMsShipped')=0
AND TABLE_SCHEMA != 'dbo'
SELECT 'EXEC sp_changeobjectowner
'''+ROUTINE_SCHEMA+'.'+ROUTINE_NAME+''',''dbo'''
FROM INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_SCHEMA+
'.'+ROUTINE_NAME),
'IsMsShipped')=0
AND ROUTINE_SCHEMA != 'dbo'
You can copy the results to the top pane and execute. The only potential
issue is if you have a situation like this:
userA.tableFoo
dbo.tableFoo
Or
userA.tableFoo
userB.tableFoo
Because it will crap out when you try to force dbo to own two objects with
the same name...
"Charlie@.CBFC" <charle1@.comcast.net> wrote in message
news:ueFBZXyzFHA.2076@.TK2MSFTNGP14.phx.gbl...
> Hi:
> Does any one know where I can find a script that will change ownership of
> all database objects at once. I took over control of a large database and
> changing owners using sp_changeobjectowner one at a time will take
> forever.
> Thanks,
> Charlie
>|||Thanks!
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uTuvD0yzFHA.904@.tk2msftngp13.phx.gbl...
> This will generate the script for you for all user-defined views, tables,
> stored procedures and functions that aren't already owned by dbo:
> SELECT 'EXEC sp_changeobjectowner
> '''+TABLE_SCHEMA+'.'+TABLE_NAME+''',''dbo'''
> FROM INFORMATION_SCHEMA.TABLES
> WHERE OBJECTPROPERTY(OBJECT_ID(TABLE_SCHEMA+'.'+TABLE_NAME),
> 'IsMsShipped')=0
> AND TABLE_SCHEMA != 'dbo'
> SELECT 'EXEC sp_changeobjectowner
> '''+ROUTINE_SCHEMA+'.'+ROUTINE_NAME+''',''dbo'''
> FROM INFORMATION_SCHEMA.ROUTINES
> WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_SCHEMA+
'.'+ROUTINE_NAME),
> 'IsMsShipped')=0
> AND ROUTINE_SCHEMA != 'dbo'
> You can copy the results to the top pane and execute. The only potential
> issue is if you have a situation like this:
> userA.tableFoo
> dbo.tableFoo
> Or
> userA.tableFoo
> userB.tableFoo
> Because it will crap out when you try to force dbo to own two objects with
> the same name...
>
>
> "Charlie@.CBFC" <charle1@.comcast.net> wrote in message
> news:ueFBZXyzFHA.2076@.TK2MSFTNGP14.phx.gbl...
of
and
>sql

Script that "overwirtes" an object?

If I specify "ScriptDrops" I only get the drops. If I leave this option out then I only get "create" in the script. If I want to overwrite (drop then create new) I am not sure what option(s) to specify. Any ideas?

Thank you.

Kevin

Kevin,

You need to set option IncludeIfNotExists:

so.IncludeIfNotExists = true;

This will generate script with "IF NOT EXISTS ... DROP" -- in other words, check object for existence, then drop it before recreating it.

|||

Artur laksberg MSFT wrote:

Kevin,

You need to set option IncludeIfNotExists:

so.IncludeIfNotExists = true;

This will generate script with "IF NOT EXISTS ... DROP" -- in other words, check object for existence, then drop it before recreating it.

If I include 'IncludeIfNotExists" and drop then is all I get is a conditional drop and the create is ignored. In order for me to overwrite an object I need to drop it and recreate it. Including 'IncludeIfNotExits" and "ScriptDrops" only conditionally produces a drop statement for me. Is that not what you see?

Thank you.

Kevin Burton

|||

Kevin,

We don't have a scripting option for "recreate" but you can achieve the same result by combining Drop and Create script. Here is a simple example I put together:

Code Snippet

static StringCollection RecreateObject( IScriptable scriptableObject )
{
// Generate Drop script
ScriptingOptions so = new ScriptingOptions();
so.ScriptDrops = true;
so.IncludeIfNotExists = true;

StringCollection sc = scriptableObject.Script(so);

// Add batch separator
sc.Add("GO");

// Now generate Crate script and add it to the resulting ScriptCollection
so.ScriptDrops = false;
so.IncludeIfNotExists = false;

foreach( string stmt in scriptableObject.Script(so) )
{
sc.Add(stmt);
}

return sc;
}

public static void Main()
{
Server srv = new Server("your_server_name");
Database db = srv.Databases["pubs"];

foreach( string stmt in RecreateObject(db))
{
Console.WriteLine(stmt);
}

}

For database pubs, function RecreateObject will generate script that looks like this:

Code Snippet

IF EXISTS (SELECT name FROM sys.databases WHERE name = N'pubs')
DROP DATABASE [pubs]
GO
CREATE DATABASE [pubs] ON PRIMARY

...

Note that if you are processing multiple objects, you don't need to drop nested objects. For instance, when processing database and its tables, you don't need to drop tables, since drop of database makes it unnecessary.

Hope that helps.

Script Task Error --Object reference not set to an instance of an object

I am trying to execute this code feom Script task while excuting its giving me error that "Object reference not set to an instance of an object." The assemblies Iam referening in this code are there in GAC. Any idea abt this.

Thanks,

PublicSub Main()

Dim remoteUri AsString

Dim fireAgain AsBoolean

Dim uriVarName AsString

Dim fileVarName AsString

Dim httpConnection As Microsoft.SqlServer.Dts.Runtime.HttpClientConnection

Dim emptyBytes(0) AsByte

Dim SessionID AsString

Dim CusAuth As CustomAuth

Try

' Determine the correct variables to read for URI and filename

uriVarName = "vsReportUri"

fileVarName = "vsReportDownloadFilename"

' create SessionID for use with HD Custom authentication

CusAuth = New CustomAuth(ASCIIEncoding.ASCII.GetBytes(Dts.Variables("in_vsBatchKey").Value.ToString()))

Dts.Variables(uriVarName).Value = Dts.Variables(uriVarName).Value.ToString() + "&" + _

"BeginDate=" + Dts.Variables("in_vsBeginDate").Value.ToString() + "&" + _

"EndDate=" + Dts.Variables("in_vsEndDate").Value.ToString()

Dim request As HttpWebRequest = CType(WebRequest.Create(Dts.Variables(uriVarName).Value.ToString()), HttpWebRequest)

'Set credentials based on the credentials found in the variables

request.Credentials = New NetworkCredential(Dts.Variables("in_vsReportUsername").Value.ToString(), _

Dts.Variables("in_vsReportPassword").Value.ToString(), _

Dts.Variables("in_vsReportDomain").Value.ToString())

'Place the custom authentication session ID in a cookie called BatchSession

request.CookieContainer.Add(New Cookie("BatchSession", CusAuth.GenerateSession("EmailAlertingSSIS"), "/", Dts.Variables("in_vsReportDomain").Value.ToString()))

' Set some reasonable limits on resources used by this request

request.MaximumAutomaticRedirections = 4

request.MaximumResponseHeadersLength = 4

' Prepare to download, write messages indicating download start

Dts.Events.FireInformation(0, String.Empty, String.Format("Downloading '{0}' from '{1}'", _

Dts.Variables(fileVarName).Value.ToString(), Dts.Variables(uriVarName).Value.ToString()), String.Empty, 0, fireAgain)

Dts.Log(String.Format("Downloading '{0}' from '{1}'", Dts.Variables(fileVarName).Value.ToString(), Dts.Variables(uriVarName).Value.ToString()), 0, emptyBytes)

' Download data

Dim response As HttpWebResponse = CType(request.GetResponse(), HttpWebResponse)

' Get the stream associated with the response.

Dim receiveStream As Stream = response.GetResponseStream()

' Pipes the stream to a higher level stream reader with the required encoding format.

Dim readStream AsNew StreamReader(receiveStream, Encoding.UTF8)

Dim fileStream AsNew StreamWriter(Dts.Variables(fileVarName).Value.ToString())

fileStream.Write(readStream.ReadToEnd())

fileStream.Flush()

fileStream.Close()

readStream.Close()

fileStream.Dispose()

readStream.Dispose()

'Download the file and report success

Dts.TaskResult = Dts.Results.Success

Catch ex As Exception

' post the error message we got back.

Dts.Events.FireError(0, String.Empty, ex.Message, String.Empty, 0)

Dts.TaskResult = Dts.Results.Failure

EndTry

EndSub

It isn't clear what is failing, a line number or similar would help. Since you mention external assemblies, have you read this -

Referencing Other Assemblies in Scripting Solutions
(http://msdn2.microsoft.com/en-us/library/9b655bcd-19f6-43d8-9f89-1b4d299c6380.aspx)

Script task Error

I have a script task that is supposed to perform some task but it fails and throws this exception

"Unable to cast COM object of type System._OComObject to class System.Data.Odbc.odbcConnection". Instances of types that represent com components cannot be cast to the types that represent COM components; they can be caste to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the inteface".

Here is the code that throws this

Dim sqlString As String

Dim conn As Odbc.OdbcConnection

Dim da As Odbc.OdbcDataAdapter

Dim ds As Data.DataSet = New Data.DataSet()

sqlString = " SELECT count(*)FROM tmpDAY INNER JOIN tblData ON tmpDAY.CusID = tblData.datFKCusID WHERE(tmpDAY.Serial = tblData.datSerial)"

Dim connName As String = Dts.Connections(0).Name

Try

conn = CType(Dts.Connections(0).AcquireConnection(Nothing), Odbc.OdbcConnection)

da = New Odbc.OdbcDataAdapter(sqlString, conn)

Catch ex As Exception

MsgBox(ex.Message.ToString())

End Try

Any suggestion will be greatly appreciated?

you could try replacing your CType with a normal ODBC connection constructed from a string
i.e. replace
conn = CType(Dts.Connections(0).AcquireConnection(Nothing), Odbc.OdbcConnection)

with

Dim connString As String = Dts.Connections(0).ConnectionString

conn = New Odbc.OdbcConnection(connString)|||

replace:

conn = CType(Dts.Connections(0).AcquireConnection(Nothing), Odbc.OdbcConnection)

with:

conn = New Odbc.OdbcConnection(connectionString)

conn.Open()

Script Task

Hi,

I am very new in Integration Services and I am using the Script task object. From what I know, it is used when you want to have a vb 2005 programming which is for accomplishing tasks that are not available in SSIS. I pasted my codes there from my vb 2005 code area. However, I get this error:

Reference required to assembly 'system.xml, version = 2.0.0.0, Culture=neutral, PublicKEyToken=b77a5c561934e089' containing the implemented interface 'System.Xml.Serialization.IXmlSerializable', Add one to your Project.

How is that resolved? How can I add it to my project?

Thanks so much!

Cherrie

from the vsa script environment select: project > add reference. then, choose "system.xml.dll". then, click the "add" button. then, click the "ok" button.

|||thanks douglas!sql

script in 2005 cannot be run on sql server 2000

for e.g.
in 2005
SELECT * FROM sys.foreign_keys....(no error)
in sql server 2000
SELECT * FROM sys.foreign_keys....(Invalid object name 'sys.foreign_keys'.)
SELECT * FROM sys.indexes WHERE object_id = .......
(no problem in sql server 2005, but have problem in sql server 2000)
so how to handle these case?
Is it possible to write script which can be run on both sql server 2005 and
sql server 2000?Yes, it is possible to write those scripts to they can run on both SQL
Server 2000 and SQL Server 2005. But you need to look for names like
sysindexes and sysforeignkeys. In the documentation (BOL) they are called
system tables in SQL Server 2000 and compatibility views on SQL Server 2005.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"kei" wrote:
> for e.g.
> in 2005
> SELECT * FROM sys.foreign_keys....(no error)
> in sql server 2000
> SELECT * FROM sys.foreign_keys....(Invalid object name 'sys.foreign_keys'.)
> SELECT * FROM sys.indexes WHERE object_id = .......
> (no problem in sql server 2005, but have problem in sql server 2000)
> so how to handle these case?
> Is it possible to write script which can be run on both sql server 2005 and
> sql server 2000?|||so what should I do now to convert the already written sql 2005 script to run
on sql 2000 server?
"Ben Nevarez" wrote:
> Yes, it is possible to write those scripts to they can run on both SQL
> Server 2000 and SQL Server 2005. But you need to look for names like
> sysindexes and sysforeignkeys. In the documentation (BOL) they are called
> system tables in SQL Server 2000 and compatibility views on SQL Server 2005.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "kei" wrote:
> > for e.g.
> > in 2005
> > SELECT * FROM sys.foreign_keys....(no error)
> > in sql server 2000
> > SELECT * FROM sys.foreign_keys....(Invalid object name 'sys.foreign_keys'.)
> >
> > SELECT * FROM sys.indexes WHERE object_id = .......
> > (no problem in sql server 2005, but have problem in sql server 2000)
> >
> > so how to handle these case?
> > Is it possible to write script which can be run on both sql server 2005 and
> > sql server 2000?|||This sounds like going backwards, that is, moving from SQL Server 2005 to
SQL Server 2000. For SQL Server 2005, Microsoft recommends to use the new
catalog views (like sys.indexes) and the compatibility views (like
sysindexes) are for backward compatibility only.
So, what I would do is to leave the existing scripts for SQL Server 2005
unchanged and just create similar ones just for SQL Server 2000.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"kei" wrote:
> so what should I do now to convert the already written sql 2005 script to run
> on sql 2000 server?
> "Ben Nevarez" wrote:
> >
> > Yes, it is possible to write those scripts to they can run on both SQL
> > Server 2000 and SQL Server 2005. But you need to look for names like
> > sysindexes and sysforeignkeys. In the documentation (BOL) they are called
> > system tables in SQL Server 2000 and compatibility views on SQL Server 2005.
> >
> > Hope this helps,
> >
> > Ben Nevarez
> > Senior Database Administrator
> > AIG SunAmerica
> >
> >
> >
> > "kei" wrote:
> >
> > > for e.g.
> > > in 2005
> > > SELECT * FROM sys.foreign_keys....(no error)
> > > in sql server 2000
> > > SELECT * FROM sys.foreign_keys....(Invalid object name 'sys.foreign_keys'.)
> > >
> > > SELECT * FROM sys.indexes WHERE object_id = .......
> > > (no problem in sql server 2005, but have problem in sql server 2000)
> > >
> > > so how to handle these case?
> > > Is it possible to write script which can be run on both sql server 2005 and
> > > sql server 2000?|||NO, I really want to write a script that can be run on both sql server 2000
and 2005, not 1 script for each version, so how can I change the sql server
2005 script to script that can be run on sql server 2000 and sql server 2005?
SELECT * FROM sys.foreign_keys WHERE object_id = ......
SELECT * FROM sys.indexes WHERE object_id =........
how to change the above statement? any concrete example of how to change?
thx!!
"Ben Nevarez" wrote:
> This sounds like going backwards, that is, moving from SQL Server 2005 to
> SQL Server 2000. For SQL Server 2005, Microsoft recommends to use the new
> catalog views (like sys.indexes) and the compatibility views (like
> sysindexes) are for backward compatibility only.
> So, what I would do is to leave the existing scripts for SQL Server 2005
> unchanged and just create similar ones just for SQL Server 2000.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "kei" wrote:
> > so what should I do now to convert the already written sql 2005 script to run
> > on sql 2000 server?
> >
> > "Ben Nevarez" wrote:
> >
> > >
> > > Yes, it is possible to write those scripts to they can run on both SQL
> > > Server 2000 and SQL Server 2005. But you need to look for names like
> > > sysindexes and sysforeignkeys. In the documentation (BOL) they are called
> > > system tables in SQL Server 2000 and compatibility views on SQL Server 2005.
> > >
> > > Hope this helps,
> > >
> > > Ben Nevarez
> > > Senior Database Administrator
> > > AIG SunAmerica
> > >
> > >
> > >
> > > "kei" wrote:
> > >
> > > > for e.g.
> > > > in 2005
> > > > SELECT * FROM sys.foreign_keys....(no error)
> > > > in sql server 2000
> > > > SELECT * FROM sys.foreign_keys....(Invalid object name 'sys.foreign_keys'.)
> > > >
> > > > SELECT * FROM sys.indexes WHERE object_id = .......
> > > > (no problem in sql server 2005, but have problem in sql server 2000)
> > > >
> > > > so how to handle these case?
> > > > Is it possible to write script which can be run on both sql server 2005 and
> > > > sql server 2000?|||I am affraid that there is no automatic way to convert the scripts. Check on
the SQL Server documentation (Books Online) for the description of both the
sysindexes and sysforeignkeys system tables/compatibility views.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"kei" wrote:
> NO, I really want to write a script that can be run on both sql server 2000
> and 2005, not 1 script for each version, so how can I change the sql server
> 2005 script to script that can be run on sql server 2000 and sql server 2005?
> SELECT * FROM sys.foreign_keys WHERE object_id = ......
> SELECT * FROM sys.indexes WHERE object_id =........
> how to change the above statement? any concrete example of how to change?
> thx!!
> "Ben Nevarez" wrote:
> >
> > This sounds like going backwards, that is, moving from SQL Server 2005 to
> > SQL Server 2000. For SQL Server 2005, Microsoft recommends to use the new
> > catalog views (like sys.indexes) and the compatibility views (like
> > sysindexes) are for backward compatibility only.
> >
> > So, what I would do is to leave the existing scripts for SQL Server 2005
> > unchanged and just create similar ones just for SQL Server 2000.
> >
> > Hope this helps,
> >
> > Ben Nevarez
> > Senior Database Administrator
> > AIG SunAmerica
> >
> >
> >
> > "kei" wrote:
> >
> > > so what should I do now to convert the already written sql 2005 script to run
> > > on sql 2000 server?
> > >
> > > "Ben Nevarez" wrote:
> > >
> > > >
> > > > Yes, it is possible to write those scripts to they can run on both SQL
> > > > Server 2000 and SQL Server 2005. But you need to look for names like
> > > > sysindexes and sysforeignkeys. In the documentation (BOL) they are called
> > > > system tables in SQL Server 2000 and compatibility views on SQL Server 2005.
> > > >
> > > > Hope this helps,
> > > >
> > > > Ben Nevarez
> > > > Senior Database Administrator
> > > > AIG SunAmerica
> > > >
> > > >
> > > >
> > > > "kei" wrote:
> > > >
> > > > > for e.g.
> > > > > in 2005
> > > > > SELECT * FROM sys.foreign_keys....(no error)
> > > > > in sql server 2000
> > > > > SELECT * FROM sys.foreign_keys....(Invalid object name 'sys.foreign_keys'.)
> > > > >
> > > > > SELECT * FROM sys.indexes WHERE object_id = .......
> > > > > (no problem in sql server 2005, but have problem in sql server 2000)
> > > > >
> > > > > so how to handle these case?
> > > > > Is it possible to write script which can be run on both sql server 2005 and
> > > > > sql server 2000?|||I am willing to modify it manually, but don't know how to change manually, do
you have any idea?
"Ben Nevarez" wrote:
> I am affraid that there is no automatic way to convert the scripts. Check on
> the SQL Server documentation (Books Online) for the description of both the
> sysindexes and sysforeignkeys system tables/compatibility views.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "kei" wrote:
> > NO, I really want to write a script that can be run on both sql server 2000
> > and 2005, not 1 script for each version, so how can I change the sql server
> > 2005 script to script that can be run on sql server 2000 and sql server 2005?
> > SELECT * FROM sys.foreign_keys WHERE object_id = ......
> > SELECT * FROM sys.indexes WHERE object_id =........
> > how to change the above statement? any concrete example of how to change?
> > thx!!
> >
> > "Ben Nevarez" wrote:
> >
> > >
> > > This sounds like going backwards, that is, moving from SQL Server 2005 to
> > > SQL Server 2000. For SQL Server 2005, Microsoft recommends to use the new
> > > catalog views (like sys.indexes) and the compatibility views (like
> > > sysindexes) are for backward compatibility only.
> > >
> > > So, what I would do is to leave the existing scripts for SQL Server 2005
> > > unchanged and just create similar ones just for SQL Server 2000.
> > >
> > > Hope this helps,
> > >
> > > Ben Nevarez
> > > Senior Database Administrator
> > > AIG SunAmerica
> > >
> > >
> > >
> > > "kei" wrote:
> > >
> > > > so what should I do now to convert the already written sql 2005 script to run
> > > > on sql 2000 server?
> > > >
> > > > "Ben Nevarez" wrote:
> > > >
> > > > >
> > > > > Yes, it is possible to write those scripts to they can run on both SQL
> > > > > Server 2000 and SQL Server 2005. But you need to look for names like
> > > > > sysindexes and sysforeignkeys. In the documentation (BOL) they are called
> > > > > system tables in SQL Server 2000 and compatibility views on SQL Server 2005.
> > > > >
> > > > > Hope this helps,
> > > > >
> > > > > Ben Nevarez
> > > > > Senior Database Administrator
> > > > > AIG SunAmerica
> > > > >
> > > > >
> > > > >
> > > > > "kei" wrote:
> > > > >
> > > > > > for e.g.
> > > > > > in 2005
> > > > > > SELECT * FROM sys.foreign_keys....(no error)
> > > > > > in sql server 2000
> > > > > > SELECT * FROM sys.foreign_keys....(Invalid object name 'sys.foreign_keys'.)
> > > > > >
> > > > > > SELECT * FROM sys.indexes WHERE object_id = .......
> > > > > > (no problem in sql server 2005, but have problem in sql server 2000)
> > > > > >
> > > > > > so how to handle these case?
> > > > > > Is it possible to write script which can be run on both sql server 2005 and
> > > > > > sql server 2000?|||I believe Ben's trying to say that you should go to BOL and look for SQL
Server 2000 commands that is equal to the ones you used in your SQL Server
2005 scripts and go that way.
You can check if it's a SQL Server 2000 or 2005 before starting other
commands in your script and then you could use 2000 or 2005 commands
according to the version of the SQL Server that you run your script.
--
Ekrem Ã?nsoy
"kei" <kei@.discussions.microsoft.com> wrote in message
news:6942143B-33F0-476F-A030-3BE4DF2AC220@.microsoft.com...
>I am willing to modify it manually, but don't know how to change manually,
>do
> you have any idea?
> "Ben Nevarez" wrote:
>> I am affraid that there is no automatic way to convert the scripts. Check
>> on
>> the SQL Server documentation (Books Online) for the description of both
>> the
>> sysindexes and sysforeignkeys system tables/compatibility views.
>> Hope this helps,
>> Ben Nevarez
>> Senior Database Administrator
>> AIG SunAmerica
>>
>> "kei" wrote:
>> > NO, I really want to write a script that can be run on both sql server
>> > 2000
>> > and 2005, not 1 script for each version, so how can I change the sql
>> > server
>> > 2005 script to script that can be run on sql server 2000 and sql server
>> > 2005?
>> > SELECT * FROM sys.foreign_keys WHERE object_id = ......
>> > SELECT * FROM sys.indexes WHERE object_id =........
>> > how to change the above statement? any concrete example of how to
>> > change?
>> > thx!!
>> >
>> > "Ben Nevarez" wrote:
>> >
>> > >
>> > > This sounds like going backwards, that is, moving from SQL Server
>> > > 2005 to
>> > > SQL Server 2000. For SQL Server 2005, Microsoft recommends to use the
>> > > new
>> > > catalog views (like sys.indexes) and the compatibility views (like
>> > > sysindexes) are for backward compatibility only.
>> > >
>> > > So, what I would do is to leave the existing scripts for SQL Server
>> > > 2005
>> > > unchanged and just create similar ones just for SQL Server 2000.
>> > >
>> > > Hope this helps,
>> > >
>> > > Ben Nevarez
>> > > Senior Database Administrator
>> > > AIG SunAmerica
>> > >
>> > >
>> > >
>> > > "kei" wrote:
>> > >
>> > > > so what should I do now to convert the already written sql 2005
>> > > > script to run
>> > > > on sql 2000 server?
>> > > >
>> > > > "Ben Nevarez" wrote:
>> > > >
>> > > > >
>> > > > > Yes, it is possible to write those scripts to they can run on
>> > > > > both SQL
>> > > > > Server 2000 and SQL Server 2005. But you need to look for names
>> > > > > like
>> > > > > sysindexes and sysforeignkeys. In the documentation (BOL) they
>> > > > > are called
>> > > > > system tables in SQL Server 2000 and compatibility views on SQL
>> > > > > Server 2005.
>> > > > >
>> > > > > Hope this helps,
>> > > > >
>> > > > > Ben Nevarez
>> > > > > Senior Database Administrator
>> > > > > AIG SunAmerica
>> > > > >
>> > > > >
>> > > > >
>> > > > > "kei" wrote:
>> > > > >
>> > > > > > for e.g.
>> > > > > > in 2005
>> > > > > > SELECT * FROM sys.foreign_keys....(no error)
>> > > > > > in sql server 2000
>> > > > > > SELECT * FROM sys.foreign_keys....(Invalid object name
>> > > > > > 'sys.foreign_keys'.)
>> > > > > >
>> > > > > > SELECT * FROM sys.indexes WHERE object_id = .......
>> > > > > > (no problem in sql server 2005, but have problem in sql server
>> > > > > > 2000)
>> > > > > >
>> > > > > > so how to handle these case?
>> > > > > > Is it possible to write script which can be run on both sql
>> > > > > > server 2005 and
>> > > > > > sql server 2000?

Script generation for Objects - Management Studio

How do I generate a single script for each object - table, view, index, trigger, sp, function etc.... in SQL Server. The script that is generated from SQL Server wizards is in a single file.

I want to have separate file for each of the objects.

In EM, you right-click on the object, select 'all tasks', then 'Generate SQL Script'. I do not have access to Management Studio right now, so this may or may not be much help.

Do you have access to Enterprise Manager? You can run SQL Server 2000 and 2005 side by side, so it may be worthwhile installing if you cannot do this in MS.

Clarity Consulting (http://www.claritycon.com)

|||

Well we have the same feature in SQL 2005 but I need one individual file for each object. I do not want the script of say all tables in file. I want this in different files with file name as table name that is getting scripted.

It applies to other objects like procs, funcs, triggers, constraints, FKs etc

|||You might look at Scriptio. I use it to create .sql files off of my db schema. On the 2nd tab I select ONE FILE PER OBJECT. www.sqlteam.com. The only problem I have found is that existing sql files (we re-exporting) can't be read-only.|||Currently, the Generate Script Wizard can only create a single file. We plan to implement file-per-object functionality in SP2.

Script generation for Objects - Management Studio

How do I generate a single script for each object - table, view, index, trigger, sp, function etc.... in SQL Server. The script that is generated from SQL Server wizards is in a single file.

I want to have separate file for each of the objects.

In EM, you right-click on the object, select 'all tasks', then 'Generate SQL Script'. I do not have access to Management Studio right now, so this may or may not be much help.

Do you have access to Enterprise Manager? You can run SQL Server 2000 and 2005 side by side, so it may be worthwhile installing if you cannot do this in MS.

Clarity Consulting (http://www.claritycon.com)

|||

Well we have the same feature in SQL 2005 but I need one individual file for each object. I do not want the script of say all tables in file. I want this in different files with file name as table name that is getting scripted.

It applies to other objects like procs, funcs, triggers, constraints, FKs etc

|||You might look at Scriptio. I use it to create .sql files off of my db schema. On the 2nd tab I select ONE FILE PER OBJECT. www.sqlteam.com. The only problem I have found is that existing sql files (we re-exporting) can't be read-only.|||Currently, the Generate Script Wizard can only create a single file. We plan to implement file-per-object functionality in SP2.

script db object permissions

I'm looking into processes to move to using database specific roles and migrating specifically assigned user permissions to these roles. I have made progress on listing permissions in the public role and assigning these same permissions to the new role that I have created for the given database. However, when I inspect permissions on tables and other objects, permissions have also been specifically granted for application users in the db. My ojective is to script out the permissions against the database objects (mainly user tables) and then use the output to assign the same permissions to the appropriate role instead. However, I'm having difficulty finding which system table stores the table and column level permissions.

If I do select * from sysobjects, I can see the user tables. However, I can't find the related tables that store user object permissions. For example, sysprotects doesn't store this info and, if I do an inner join on 'id' between these two tables, I don't see any user tables. Where do I look? Are there any useful 'already done' scripts/procs around for these purposes?

Regards,

CliveTry this one..
sp_helprotect
sskris|||well
i dont have an sql server with me now, but you could create a query between the sysobjects, sysusers, and sysprotects table and you could interpret the action and the protecttype columns
[Books Online] sysusers
[Books Online] sysobjects
[Books Online] sysprotects

if you want a quick way to view system tables in a graphical format you should download systbl.chm (http://download.microsoft.com/download/SQLSVR2000/sysmap/2000/WIN98MeXP/EN-US/systbl.chm) which is also available in your sql server 2000 resource kit.|||/*
This script creates a view to display users and objects that they have permissions for
and the permissions that are set
*/

use master
go
Create View VUserRights
as
SELECT top 100 percent
U.[Name] as UName
,O.Name as OName
,case xtype
when 'S' then 'System Table'
when 'P' then 'Stored Procedure'
when 'C' then 'Check Constraint'
when 'D' then 'Default'
when 'F' then 'Foreign Key'
when 'L' then 'Log'
when 'FN' then 'Scalar Function'
when 'IF' then 'Inlined Table-Function'
when 'PK' then 'PRIMARY KEY'
when 'RF' then 'Replication Filter Stored Procedure'
when 'S' then 'System Table'
when 'TF' then 'Table Function'
when 'TR' then 'Trigger'
when 'U' then 'User Table'
when 'UQ' then 'UNIQUE Constraint'
when 'V' then 'View'
when 'X' then 'Extended Stored Procedure'
else cast(xtype as varchar(30))
end as XType

,Case p.[action]
When 26 then 'REFERENCES'
When 178 then 'CREATE FUNCTION'
When 193 then 'SELECT'
When 195 then 'INSERT'
When 196 then 'DELETE'
When 197 then 'UPDATE'
When 198 then 'CREATE TABLE'
When 203 then 'CREATE DATABASE'
When 207 then 'CREATE VIEW'
When 222 then 'CREATE PROCEDURE'
When 224 then 'EXECUTE'
When 228 then 'BACKUP DATABASE'
When 233 then 'CREATE DEFAULT'
When 235 then 'BACKUP LOG'
When 236 then 'CREATE RULE'
Else cast([Action] as varchar(20))
End as 'Action'

,Case p.protecttype
When 204 Then 'GRANT_W_GRANT'
When 205 Then 'GRANT'
When 206 Then 'REVOKE'
Else cast(protecttype as varchar(20))
end as ProtectType

FROM sysusers U join sysprotects P
on u.uid = P.uid
Join sysobjects O
on P.id = O.id
where xtype <>'s'
order by U.uid ASC, O.xtype Desc

/*
Here are some calling statements
--2 is an oracle trick that i learned to
create a permissions assignment statement from exisiting metadata
*/

GO
--1
select * from vuserrights
Go
--2
select Protecttype + ' ' +
Action + ' ON ' +
Oname
-- +'('+ Xtype+')'
+ ' TO ' + Uname from vuserRights|||Thank you for the info. Nice View by the way.

I ended up writing a script that used sp_helprotect. It dumps out the permissions for a given user to a temp table and then I cursor through the temp table to 'grant' the permissions to a new role. eg. give me the Public permissions that have been granted against objects in this db and copy them to a new db role. Also, I can revoke the permsissions on Public as an option. So, I simply do:-

set @.GrantSQL ='Grant ' + @.action + ' on [' + @.Obj + '] to ' + @.Role
exec(@.GrantSQL)

However, although the above works fine on the db's I'm working on, I suspect that it wouldn't work with regard to database that have column level permssions. I tried setting a column level permission to see how my script would handle it. sp_helprotect reports the column level permssion but I haven't got around to modifiying my script to deal with it properly - currently it migrates the column level permssion in the source user/role to the target role as a table level permisson. In the loop that cursors through the temp table containing the sp_helprotect output, I presumably need to detect a column level permssion and branch to execute a grant statement that will apply a column level permission? Haven't had time to research this yet.

Thanks again,

Clive|||If you are comfortable with VB or VC, then SQL-DMO (http://msdn.microsoft.com/library/en-us/sqldmo/dmoref_con01_2yi7.asp) offers exactly what you want.

-PatP|||Pat,

I am comfortable with VB but haven't doen anything with dmo yet. Got any examples?

Cheers,

Clive|||Just follow the link. There are zillions of examples scattered on the appropriate pages.

-PatP|||Zillions ?
it's more like a Quintillion.|||Zillions ?
it's more like a Quintillion.Ok, ok, ok... So what's a few zeros here and there between friends, eh? ;)

-PatP

Script DB Object Level Security with SQL2005

We regularly need to take production DB backups and restore them onto our
development SQL Server boxes for testing (SQL2005 SP2). Since DB level
security is much different between production and stage, we need to script
stage rights (server and object level) and refresh those rights onto the
restored production DB. I was able to do that with EM in SQL 2000, but am
unable to do it with 2005 (SP2). I have tried (I think) every possible
option in Management Studio under Tasks / Generate Scripts for the DB and
none generate SQL for object level security. Is this a "bug" with SQL2005
SP2? I believe we were able to do it with SP1, but I don't recall which
options we chose.
--
KevinKevinL (KevinL@.discussions.microsoft.com) writes:
> We regularly need to take production DB backups and restore them onto our
> development SQL Server boxes for testing (SQL2005 SP2). Since DB level
> security is much different between production and stage, we need to script
> stage rights (server and object level) and refresh those rights onto the
> restored production DB. I was able to do that with EM in SQL 2000, but am
> unable to do it with 2005 (SP2). I have tried (I think) every possible
> option in Management Studio under Tasks / Generate Scripts for the DB and
> none generate SQL for object level security. Is this a "bug" with SQL2005
> SP2? I believe we were able to do it with SP1, but I don't recall which
> options we chose.
There is a scripting option "Script Object-level Permissions". that you
can use when you use the Generate Scripts task.
Under Tools->Options there is a new page "Scripting" where you can set
options for when you script individual objects from Object Explorer.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks for the reply.
That appears to have resolved the problem (bug?) with scripting object level
permission, but role membership is still not scripted. Any idea why that
might be?|||KevinL (KevinL@.discussions.microsoft.com) writes:
> That appears to have resolved the problem (bug?) with scripting object
> level permission, but role membership is still not scripted. Any idea
> why that might be?
How do you script? When I script a database by right-clicking it, selecting
Tasks/Generate Scripts and include both Database Roles and Users I do get
role membership scripted at the end.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I see no option to script roles. This is what I did:
Open Management Studio, expand databases, right click the DB, click Tasks,
then Generate Script. The DB I right clicked is highlighted, I click next.
I verified the Script Options to make sure Script Object-Level Permissions i
s
True (there is no Option that mentions Roles) and click next. On Object
Types window choices are Schema, Stored Procedures, Tables, User-defined dat
a
types, User-defined functions and Users.
No matter which option(s) I choose, and I've tried every combination I can
think of, Role level permissions are not scripted.
--
Kevin
"Erland Sommarskog" wrote:

> KevinL (KevinL@.discussions.microsoft.com) writes:
> How do you script? When I script a database by right-clicking it, selectin
g
> Tasks/Generate Scripts and include both Database Roles and Users I do get
> role membership scripted at the end.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||KevinL (KevinL@.discussions.microsoft.com) writes:
> I see no option to script roles. This is what I did:
> Open Management Studio, expand databases, right click the DB, click
> Tasks, then Generate Script. The DB I right clicked is highlighted, I
> click next. I verified the Script Options to make sure Script
> Object-Level Permissions is True (there is no Option that mentions
> Roles) and click next. On Object Types window choices are Schema,
> Stored Procedures, Tables, User-defined data types, User-defined
> functions and Users.
> No matter which option(s) I choose, and I've tried every combination I can
> think of, Role level permissions are not scripted.
If "Database roles" are not listed, this would indicate that you don't
have any user-defined roles in the database, only the pre-defined roles,
db_owner and the like. Indeed, it appears that membership in these roles
are not scripted.
You can script all role membership with this SELECT:
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'
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Script

Hi All,

When I run the script below I get the error "Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name '#table'".

CREATE TABLE #table (Check_Log VARCHAR(1000), Log_Time datetime default GetDate())

INSERT #table(Check_Log)
EXEC master..xp_cmdshell 'osql -S server -U user -P password -d db -Q"DBCC CHECKDB"'

EXEC master..xp_cmdshell 'bcp dbname.user.#table out Z:\Test\CheckDBRes.txt -S server -U user -P password'

SELECT * FROM #table

IF EXISTS (SELECT * FROM #table
WHERE Check_Log = 'CHECKDB found 0 allocation errors and 0 consistency errors in database')

PRINT 'No errors'

ELSE

RETURN

DROP TABLE #table

Can you please tell what I am doing wrong?

ThanksDidn't we do this laready?

All you had to do was curt and paste my origina;|||This is a little bit different. Your script doesn't put the results of checkdb into the text file. But this is one of the requirements.|||You will need to make this a permanent table

Also, make sure you put the batch column back in|||What is the purpose of the batch column?|||So that multiple processes can use trhe samme table, you can retain all of the logs, and then only ftp out 1 "batch" dbcc process at a time.

Trust me it will make life a lot easier

http://www.dbforums.com/showthread.php?t=1612711|||Thanks for your help.