CREATE SCHEMA

CREATE SCHEMA AUTHORIZATION ross
GRANT SELECT on v1 TO public
CREATE VIEW v1(c1) AS SELECT c1 from t1
CREATE TABLE t1(c1 int)
According docs, "ross" must be a valid security account in the database,
however I can type in any random string and SQL Server 2005 accepts it nicely
reporting "Command(s) completed successfully."
When scripting the database the text "ross" appears nowhere.
What is the purpose of the above Create statement?Hans,
I'm not really clear what you're trying, but the syntax you posted
doesn't make any sense.
In the CREATE SCHEMA statement there's no schema name and then you try
to grant select permissions on a view before you actually create the
view. Unfortunately executing a CREATE SCHEMA statement without a
schema name doesn't cause an error, but simply doesn't do anything.
About "Ross" , it's the database user who becomes owner of the schema.
If you try to execute the following staement in a database with no user
Markus you will receive an error.
CREATE SCHEMA mySchema AUTHORIZATION markus
Markus|||Hi Markus,
thanx for the quick reply.
CREATE SCHEMA AUTHORIZATION ross
GRANT SELECT on v1 TO public
CREATE VIEW v1(c1) AS SELECT c1 from t1
CREATE TABLE t1(c1 int)
Above statements come right from BOL. The index shows two entries for CREATE
SCHEMA, one WITH a schema name as you mentioned, and the one above. It didn't
make sense to me either, but since SQL2K5 mentioned successful completion I
was curious what was actually happening or that I missed something. If
"ross", or any random string for that matter, is not a user in the database
the create schema statement succeeds nonetheless.
Guess it has put me on the wrong leg...
"MarkusB" wrote:
> Hans,
> I'm not really clear what you're trying, but the syntax you posted
> doesn't make any sense.
> In the CREATE SCHEMA statement there's no schema name and then you try
> to grant select permissions on a view before you actually create the
> view. Unfortunately executing a CREATE SCHEMA statement without a
> schema name doesn't cause an error, but simply doesn't do anything.
> About "Ross" , it's the database user who becomes owner of the schema.
> If you try to execute the following staement in a database with no user
> Markus you will receive an error.
> CREATE SCHEMA mySchema AUTHORIZATION markus
> Markus
>|||Solved my own problem ;-)
In BOL, if you don't define a filter (i.e. "unfiltered"), chances are you
are directed to entries other than SQL2K5 docs. "CREATE SCHEMA" pointed me to
SQL2K, and "CREATE SCHEMA statement" to SQL2K5.
"Hans" wrote:
> Hi Markus,
> thanx for the quick reply.
> CREATE SCHEMA AUTHORIZATION ross
> GRANT SELECT on v1 TO public
> CREATE VIEW v1(c1) AS SELECT c1 from t1
> CREATE TABLE t1(c1 int)
> Above statements come right from BOL. The index shows two entries for CREATE
> SCHEMA, one WITH a schema name as you mentioned, and the one above. It didn't
> make sense to me either, but since SQL2K5 mentioned successful completion I
> was curious what was actually happening or that I missed something. If
> "ross", or any random string for that matter, is not a user in the database
> the create schema statement succeeds nonetheless.
> Guess it has put me on the wrong leg...
> "MarkusB" wrote:
> > Hans,
> >
> > I'm not really clear what you're trying, but the syntax you posted
> > doesn't make any sense.
> > In the CREATE SCHEMA statement there's no schema name and then you try
> > to grant select permissions on a view before you actually create the
> > view. Unfortunately executing a CREATE SCHEMA statement without a
> > schema name doesn't cause an error, but simply doesn't do anything.
> >
> > About "Ross" , it's the database user who becomes owner of the schema.
> > If you try to execute the following staement in a database with no user
> > Markus you will receive an error.
> > CREATE SCHEMA mySchema AUTHORIZATION markus
> >
> > Markus
> >
> >

create dymanic dts

i can writing dynamic dts package . but not working.

code is here :

Public goPackageOld As New Package
Public goPackage As Package2

Public Sub RunDTS()
Dim goPackage As Package2
goPackage = CType(goPackageOld, Package2)
goPackage.Name = "DTS3"
goPackage.Description = "DTS package description"
goPackage.WriteCompletionStatusToNTEventLog = False
goPackage.FailOnError = False
goPackage.PackagePriorityClass = CType(2, DTSPackagePriorityClass)
goPackage.MaxConcurrentSteps = 4
goPackage.LineageOptions = 0
goPackage.UseTransaction = True
goPackage.TransactionIsolationLevel = CType(4096, DTSIsolationLevel)
goPackage.AutoCommitTransaction = True
goPackage.RepositoryMetadataOptions = 0
goPackage.UseOLEDBServiceComponents = True
goPackage.LogToSQLServer = False
goPackage.LogServerFlags = 0
goPackage.FailPackageOnLogFailure = False
goPackage.ExplicitGlobalVariables = False
goPackage.PackageType = 0

Dim oConnProperty As OleDBProperty
'

' create package connection information

'

Dim oConnection As Connection2
'- a new connection defined below.

oConnection = CType(goPackage.Connections.New("DTSFlatFile"), Connection2)

oConnection.ConnectionProperties.Item("Data Source").Value = "C:\hede\50.txt"
oConnection.ConnectionProperties.Item("Mode").Value = 1
oConnection.ConnectionProperties.Item("Row Delimiter").Value = "||##"
oConnection.ConnectionProperties.Item("File Format").Value = 1
oConnection.ConnectionProperties.Item("Column Delimiter").Value = "|#$,"
oConnection.ConnectionProperties.Item("File Type").Value = 1
oConnection.ConnectionProperties.Item("Skip Rows").Value = 0
oConnection.ConnectionProperties.Item("First Row Column Name").Value() = True
oConnection.ConnectionProperties.Item("Max characters per delimited column").Value = 8000
oConnection.Name = "Connection 1"
oConnection.ID = 1
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = "C:\hede\50.txt"
oConnection.ConnectionTimeout = 60
oConnection.UseTrustedConnection = False
oConnection.UseDSL = False
goPackage.Connections.Add(CType(oConnection, Connection))

oConnection = CType(goPackage.Connections.New("SQLOLEDB"), Connection2)
oConnection.ConnectionProperties.Item("Integrated Security").Value = "SSPI"
oConnection.ConnectionProperties.Item("Persist Security Info").Value() = True
oConnection.ConnectionProperties.Item("Initial Catalog").Value = "**"
oConnection.ConnectionProperties.Item("Data Source").Value = "(local)"
oConnection.ConnectionProperties.Item("Application Name").Value = "DTS Import/Export Wizard"
oConnection.Name = "Connection 2"
oConnection.ID = 2
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = "(local)"
oConnection.UserID = "**"
oConnection.Password = "**"
oConnection.ConnectionTimeout = 60
oConnection.Catalog = "**"
oConnection.UseTrustedConnection = True
oConnection.UseDSL = False
goPackage.Connections.Add(CType(oConnection, Connection))
oConnection = Nothing

'

' create package steps information

'

Dim oStep As Step2
Dim oPrecConstraint As PrecedenceConstraint

oStep = CType(goPackage.Steps.New, Step2)
oStep.Name = "Copy Data from myTextFile to [(local)].[dbo].[111] Step"
oStep.Description = "Copy Data from myTextFile to [(local)].[dbo].[111] Step"
oStep.ExecutionStatus = CType(1, DTSStepExecStatus)
oStep.TaskName = "Copy Data from myTextFile to [(local)].[dbo].[111] Task"
oStep.CommitSuccess = False
oStep.RollbackFailure = False
oStep.ScriptLanguage = "VBScript"
oStep.AddGlobalVariables = True
oStep.RelativePriority = CType(3, DTSStepRelativePriority)
oStep.CloseConnection = False
oStep.ExecuteInMainThread = False
oStep.IsPackageDSORowset = False
oStep.JoinTransactionIfPresent = False
oStep.DisableStep = False
oStep.FailPackageOnError = False
goPackage.Steps.Add(oStep)
oStep = Nothing
goPackage.SaveToSQLServer("(local)", "**", "**", DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, "", "", "")
Try
goPackage.Execute()
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub

not have error. try clause is running.but not correct result.

thx..

The Package.Execute method will only thow an exception if it cannot run the package at all, virtual impossible to get. It does not throw an exception if the package fails, as that is still a valid execution.

To capture details of any errors that happend within the package, use the package events provider.

HOW TO: Handle Data Transformation Services Package Events in Visual C# .NET
(http://support.microsoft.com/kb/319985/en-us)

|||

ok i done.

but it didnt have error.

created package in sql server.

but double click on package have error =

Error Source: Microsoft Data Transformation Services(DTS) Package
Error Description: Task 'Copy Data from C:\hede\hede.txt to [(local)].[dbo].[111] Task' was not found.

i want to here : can i do writing Transformation Task Name = Task

Create DB Role owned by public

I use the following script in order to create db role:

USE [MyDB]
GO
CREATE ROLE [myRole] AUTHORIZATION [public]
GO

It doesn't work:
Msg 15405, Level 16, State 1, Line 1
Cannot use the special principal 'public'.

However this code works fine:

USE [MyDB]
GO
CREATE ROLE [myRole] AUTHORIZATION [dbo]
GO
ALTER AUTHORIZATION ON ROLE::[myRole] TO [public]
GO

So the question is why?

Seems to me like a bug, please post it on http://connect.microsoft.com/SQLServer/Feedback

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Yes, as Jens said, it sounds like a bug; thanks a lot for reporting it.

Now my question, why would you like to make “public” the owner of the role? It is the equivalent of saying “everyone is the owner”, therefore anyone can do whatever they want on the role (add, drop member, drop it or even take complete ownership of the role).

I would strongly recommend against granting such an elevated privilege (CONTROL or own any object) to public.

Please, let us know what is the scenario you are trying to solve and we will be glad to give some alternative suggestions.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thanks for answer,

Actually I'm creating an application for managing SQL Server security

and as far as I see now the best solution is not to give users ability to choose 'public' as owner for db roles at all, right?

|||

Correct. That’s why we think it is a bug that ALTER AUTHORIZATION allows it.

Once again thanks a lot for the feedback and for reporting the bug.

-Raul Garcia

SDE/T

SQL Server Engine

Create DB Role owned by public

I use the following script in order to create db role:

USE [MyDB]
GO
CREATE ROLE [myRole] AUTHORIZATION [public]
GO

It doesn't work:
Msg 15405, Level 16, State 1, Line 1
Cannot use the special principal 'public'.

However this code works fine:

USE [MyDB]
GO
CREATE ROLE [myRole] AUTHORIZATION [dbo]
GO
ALTER AUTHORIZATION ON ROLE::[myRole] TO [public]
GO

So the question is why?

Seems to me like a bug, please post it on http://connect.microsoft.com/SQLServer/Feedback

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Yes, as Jens said, it sounds like a bug; thanks a lot for reporting it.

Now my question, why would you like to make “public” the owner of the role? It is the equivalent of saying “everyone is the owner”, therefore anyone can do whatever they want on the role (add, drop member, drop it or even take complete ownership of the role).

I would strongly recommend against granting such an elevated privilege (CONTROL or own any object) to public.

Please, let us know what is the scenario you are trying to solve and we will be glad to give some alternative suggestions.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thanks for answer,

Actually I'm creating an application for managing SQL Server security

and as far as I see now the best solution is not to give users ability to choose 'public' as owner for db roles at all, right?

|||

Correct. That’s why we think it is a bug that ALTER AUTHORIZATION allows it.

Once again thanks a lot for the feedback and for reporting the bug.

-Raul Garcia

SDE/T

SQL Server Engine