Package with File System Task doesn't work without sensitive data with user keys

This problem is a bit weird but I'm just wondering if anybody else experienced this.

I have a package that has file system tasks (copying dtsx files actually). Basically the package copies other packages to a pre-defined destination. Thing is, it only works if one of the packages it is configured to copy has some sort of sensitive data (e.g., a connectionstring with a password), otherwise it reports a success message on execution but doesn't actually do anything. I've checked the forcedexecutionresult and it is set to None for that matter.

Just wondering if anybody else experienced this problem and of course if there's a way to solve it.

Thanks.Could the security permissions be different on your packages that are failing to be moved from those that are succeeding?|||No, they're all set to EncryptSensitiveWithUserKey.

However, all of the packages involved have their sensitive information set to obtaining them from Indirect Configuration Files. The only way around that I found so far was to put in a dummy connection object, set up an XML file package configuration for that dummy, and deploy it that way.

I'm suspecting that the user key isn't generated at all when all the packages get their configs from Indirect Configuration Files. For some reason, DontSaveSensitive doesn't work either. My original package insists on the presence of a key. Might have something to do with file read/write/modify permissions that come with the key?

I still want to do away with the dummy though.

package variable question

Hi Guys,

I'm trying to evaluate a user variable to ToLine expression property from SendMailTask.
My Question is, Is it possible to evaluate a user variable in expression(..)?

So, you have a variable in the user namespace that contains the string you want to use for hte ToLine? If so, given a variable named MyVar, the expression to use would be:

@.[User::MyVar]

Let me know if I misunderstood your question.
Mark|||

Larry Surat wrote:

Hi Guys,

I'm trying to evaluate a user variable to ToLine expression property from SendMailTask.
My Question is, Is it possible to evaluate a user variable in expression(..)?

Yes. Select your variable in the variables pane. Then, over in the properties pane, set EvaluateAsExpression=TRUE and Expression=<your expression>

-Jamie|||Hi Jamie,

I've tried setting the EvaluateAsExpression=TRUE of my variable but I still got error on my send mail task. The error is: "Error at Send Mail Task:No Recepient Specified".

Here's the situation. I'm retreiving an email address from a row and stores it to a variable varMailTo. From the Send mail task. I used expression for the ToLine Property. Initially, I supplied a value for the ToLine.
|||Hi Mark,

you've got my point...

My problem now is when i supply the said variable to the SendMailTask ToLine Property expression. I get the error. "Send Mail Task error: No recepient is specified" even if initially I supply the ToLine with an initial value. After executing the task, it removes the value I supplied.

I also tried setting the EvaluateAsExpression property of my variable to true..
|||That sounds like it should work Larry. Your process sounds correct so there's a small error in there somewhere, perhaps a syntax error or something.

Try to isolate the different parts of what you're trying to do. Good old message boxes can help here with debugging.

-Jamie|||Actually it sounds like you are not setting the variable in the correct location. Are you entering the variable name for the to line in the mail section of the editor or are you adding an expression for the to line from the expressions section of the editor? The former will not work because it takes the string as a literal. The latter is what you want because the variable's value will be used as the to line.

Setting the variable to evaluate as expression just means you have an expression in the variable that needs to be evaluated before it is applied to the property. So if your variable contained recip@.company.com you wouldn't need to evaluate as expression but if it contained "recip@." + "company.com" then you would since the variable needs to first be evaluated before being set into the property.

HTH,
Mattsql

Package Execution with Custom UI

Hi:

I need a user to pass an input to a package from a VB form, and then want to show progress/errors in the form.

I have been able to use Application. LoadFromSqlServer and I then set pkg.Variables.(MyVariable) to the required value before calling Execute.

Works fine so far.

I dont know how to show progress. The DTSExecResult just returns a Cryptic Success /Failure status message.

Is there a way to log the errors if any to my Windows form? I have seen samples on Console Apps(see below) but I would prefer to show it in my Windows form in a text Box or something, appending each error result to the text.

TIA

Kartik

Code Snippet

Class EventListener
Inherits DefaultEvents

Public Overrides Function OnError(ByVal source As Microsoft.SqlServer.Dts.Runtime.DtsObject, _
ByVal errorCode As Integer, ByVal subComponent As String, ByVal description As String, _
ByVal helpFile As String, ByVal helpContext As Integer, _
ByVal idofInterfaceWithError As String) As Boolean

' Add application�Cspecific diagnostics here.
Console.WriteLine("Error in {0}/{1} : {2}", source, subComponent, description)
Return False

End Function

End Class

That's the code you want - you need to capture the events, and write to a text box instead of the console.

Package Configuration in SQL

I have an OLEDB SQL Connection that has a connection string that is set as and Expression using a User Variable (@.[User::server_name]). I pass the server name as a command line option. The Package Configuration table is being accessed using the setting for User::server_name in the package and not using the parameter entry for it.
The command being issued is similar to:

dtexec /FILE "packagename.dtsx" /SET "\package.variables[server_name]";dbserver

I am wondering if this by design? I would think that all parameter variables would be set prior to any other configuration entries are processed.

Thanks,
MikeHi Mike,

Do you get any warnings or errors with the command line you specified? Is there more than one variable called server_name with a different namespace or at different scope? You can also add the /rep v option and see if there is any other information that might be useful to determine what is happening.

Thanks,
Matt|||Here is the error I was getting:

Started: 11:27:03 AM
Warning: 2005-12-13 11:27:06.54
Code: 0x8002F304
Source: packagename
Description: A problem occurred with the following messages: "Invalid object
name 'dbo.SSISConfigurations'.".
End Warning
Warning: 2005-12-13 11:27:06.54
Code: 0x80012059
Source: packagename
Description: Failed to load at least one of the configuration entries for the
package. Check configurations entries and previous warnings to see descriptions
of which configuration failed.
End Warning

The table dbo.SSISConfigurations does not exist on the server I had set in the package, but does on the server I am passing as a parameter. This was done specifically to test for this type of issue, where when deployed to a client that does not have the server configured in the package.

Thanks,
Mike|||It looks to me that you try to apply configuration from a server that you want to be specified at runtime.

The problem is that configuration happens at load time and before any set from the command line.

The way I see it is to have define din the package a configuration that would set the desired server name in your variable and this configuration is applied before any other configuration.

HTH,
Ovidiu Burlacu|||Thanks for the feedback. You confirmed what I suspected about the order of evaluation between Configuration and Parameters.

I have attempted to run dtexec with /ConfigFile parameter but it errors with unable to locate the configuration file. I put the full path of the configuration file and it still errors:

dtexec /FILE "C:\SSIS\Package.dtsx" /CONFIGFILE "C:\SSIS\TestConfig.dtsConfig" /MAXCONCURRENT 1 /CHECKPOINTING OFF

I am running on IA64 system, so I tried both 32bit and 64bit dtexec programs and they error the same. So not sure where to go from here.

Thanks,
Mike|||The solution you are trying will not work and the reason is the sequence of configurations:
1. Configurations defined in the package
2. Configurations specified in the command line
You need to open the package in the designer and specify the configuration for your variable in the package and make sure that this is the first configuration in the list.

HTH,
Ovidiu|||

I have tried in the designer to set the configuration file, but it has a fully qualified path to the configuration file. So when deployed, it will not match correctly on a that system.

I would think that the system should process all command line parameters prior to loading any configurations. Maybe I should submit this as a wish list item. I just don't see how to override on a deployed system. This seems a lot like the Chicken and the Egg...

Thanks,
Mike

|||Since you can have multiple configurations in a you should have one configuration (the first one) that loads the server name from something that can be setup on each computer that will run packages (this could be an environment variable or a configuration file that will always be in a fixed location). Then the second configuration would be able to work because the first one would be able to already configure the server name.

What you want is problematic because it implies interaction in loading configurations from the utility that loads the package. If this was done then anyone could easily circumvent configuration loading by not requesting the package to load up its configurations. While you could argue the same thing is happening now just by the package not being able to find the configuration it is actually different because in the first case there would be no warning informing you that the configuration was not applies, whereas in the second there is always a warning and therefore you are aware of the problem as opposed to the package just not running and you having no idea why. All this being said please feel free to submit this as a wish list item, I was just trying to give you insight as to why it is the way it is.

Thanks,
Matt

Ownership of objects

I want to drop a user by running sp_dropuser.
I get this error:
The user owns objects in the database and cannot be dropped.
How do I find the object the user owns?
Thanks,
AntoninHere's a quick way:
select * from sysobjects where uid = user_id('dbo')
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Antonin Koudelka" <Antonin.Koudelka@.fmc.sa.gov.au> wrote in message
news:e1eugC2rEHA.2580@.TK2MSFTNGP15.phx.gbl...
>I want to drop a user by running sp_dropuser.
> I get this error:
> The user owns objects in the database and cannot be dropped.
> How do I find the object the user owns?
> Thanks,
> Antonin
>|||Hi Tibor,
Thanks for your reply.
I ran
select * from sysobjects where uid = user_id('BME054')
It worked. I got view user defined functions and views, one of them
'SerialQuery'.
Then I tried
sp_changeobjectowner 'SerialQuery', 'dbo'
and got this
Object 'SerialQuery' does not exists or is not a valid object for this
platform.
Any idea, suggestion?
Thanks,
Antonin
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eEViKT4rEHA.3244@.tk2msftngp13.phx.gbl...
> Here's a quick way:
> select * from sysobjects where uid = user_id('dbo')
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Antonin Koudelka" <Antonin.Koudelka@.fmc.sa.gov.au> wrote in message
> news:e1eugC2rEHA.2580@.TK2MSFTNGP15.phx.gbl...
>>I want to drop a user by running sp_dropuser.
>> I get this error:
>> The user owns objects in the database and cannot be dropped.
>> How do I find the object the user owns?
>> Thanks,
>> Antonin
>>
>|||Hi all,
I read the books online and found out I have to do it like this:
sp_changeobjectowner 'BME054.SerialQuery', 'dbo'
It worked OK and then I could drop the user.
Antonin
"Antonin" <Antonin.Koudelka@.fmc.sa.gov.au> wrote in message
news:%23mPi$a$rEHA.868@.TK2MSFTNGP12.phx.gbl...
> Hi Tibor,
> Thanks for your reply.
> I ran
> select * from sysobjects where uid = user_id('BME054')
> It worked. I got view user defined functions and views, one of them
> 'SerialQuery'.
> Then I tried
> sp_changeobjectowner 'SerialQuery', 'dbo'
> and got this
> Object 'SerialQuery' does not exists or is not a valid object for this
> platform.
> Any idea, suggestion?
> Thanks,
> Antonin
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:eEViKT4rEHA.3244@.tk2msftngp13.phx.gbl...
>> Here's a quick way:
>> select * from sysobjects where uid = user_id('dbo')
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Antonin Koudelka" <Antonin.Koudelka@.fmc.sa.gov.au> wrote in message
>> news:e1eugC2rEHA.2580@.TK2MSFTNGP15.phx.gbl...
>>I want to drop a user by running sp_dropuser.
>> I get this error:
>> The user owns objects in the database and cannot be dropped.
>> How do I find the object the user owns?
>> Thanks,
>> Antonin
>>
>>
>

Ownership of objects

I want to drop a user by running sp_dropuser.
I get this error:
The user owns objects in the database and cannot be dropped.
How do I find the object the user owns?
Thanks,
AntoninHere's a quick way:
select * from sysobjects where uid = user_id('dbo')
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Antonin Koudelka" <Antonin.Koudelka@.fmc.sa.gov.au> wrote in message
news:e1eugC2rEHA.2580@.TK2MSFTNGP15.phx.gbl...
>I want to drop a user by running sp_dropuser.
> I get this error:
> The user owns objects in the database and cannot be dropped.
> How do I find the object the user owns?
> Thanks,
> Antonin
>|||Hi Tibor,
Thanks for your reply.
I ran
select * from sysobjects where uid = user_id('BME054')
It worked. I got view user defined functions and views, one of them
'SerialQuery'.
Then I tried
sp_changeobjectowner 'SerialQuery', 'dbo'
and got this
Object 'SerialQuery' does not exists or is not a valid object for this
platform.
Any idea, suggestion?
Thanks,
Antonin
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eEViKT4rEHA.3244@.tk2msftngp13.phx.gbl...
> Here's a quick way:
> select * from sysobjects where uid = user_id('dbo')
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Antonin Koudelka" <Antonin.Koudelka@.fmc.sa.gov.au> wrote in message
> news:e1eugC2rEHA.2580@.TK2MSFTNGP15.phx.gbl...
>|||Hi all,
I read the books online and found out I have to do it like this:
sp_changeobjectowner 'BME054.SerialQuery', 'dbo'
It worked OK and then I could drop the user.
Antonin
"Antonin" <Antonin.Koudelka@.fmc.sa.gov.au> wrote in message
news:%23mPi$a$rEHA.868@.TK2MSFTNGP12.phx.gbl...
> Hi Tibor,
> Thanks for your reply.
> I ran
> select * from sysobjects where uid = user_id('BME054')
> It worked. I got view user defined functions and views, one of them
> 'SerialQuery'.
> Then I tried
> sp_changeobjectowner 'SerialQuery', 'dbo'
> and got this
> Object 'SerialQuery' does not exists or is not a valid object for this
> platform.
> Any idea, suggestion?
> Thanks,
> Antonin
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:eEViKT4rEHA.3244@.tk2msftngp13.phx.gbl...
>

Ownership of objects

I want to drop a user by running sp_dropuser.
I get this error:
The user owns objects in the database and cannot be dropped.
How do I find the object the user owns?
Thanks,
Antonin
Here's a quick way:
select * from sysobjects where uid = user_id('dbo')
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Antonin Koudelka" <Antonin.Koudelka@.fmc.sa.gov.au> wrote in message
news:e1eugC2rEHA.2580@.TK2MSFTNGP15.phx.gbl...
>I want to drop a user by running sp_dropuser.
> I get this error:
> The user owns objects in the database and cannot be dropped.
> How do I find the object the user owns?
> Thanks,
> Antonin
>
|||Hi Tibor,
Thanks for your reply.
I ran
select * from sysobjects where uid = user_id('BME054')
It worked. I got view user defined functions and views, one of them
'SerialQuery'.
Then I tried
sp_changeobjectowner 'SerialQuery', 'dbo'
and got this
Object 'SerialQuery' does not exists or is not a valid object for this
platform.
Any idea, suggestion?
Thanks,
Antonin
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eEViKT4rEHA.3244@.tk2msftngp13.phx.gbl...
> Here's a quick way:
> select * from sysobjects where uid = user_id('dbo')
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Antonin Koudelka" <Antonin.Koudelka@.fmc.sa.gov.au> wrote in message
> news:e1eugC2rEHA.2580@.TK2MSFTNGP15.phx.gbl...
>
|||Hi all,
I read the books online and found out I have to do it like this:
sp_changeobjectowner 'BME054.SerialQuery', 'dbo'
It worked OK and then I could drop the user.
Antonin
"Antonin" <Antonin.Koudelka@.fmc.sa.gov.au> wrote in message
news:%23mPi$a$rEHA.868@.TK2MSFTNGP12.phx.gbl...
> Hi Tibor,
> Thanks for your reply.
> I ran
> select * from sysobjects where uid = user_id('BME054')
> It worked. I got view user defined functions and views, one of them
> 'SerialQuery'.
> Then I tried
> sp_changeobjectowner 'SerialQuery', 'dbo'
> and got this
> Object 'SerialQuery' does not exists or is not a valid object for this
> platform.
> Any idea, suggestion?
> Thanks,
> Antonin
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:eEViKT4rEHA.3244@.tk2msftngp13.phx.gbl...
>

ownership chains, users and views

We have a design using views to partition visibility of table rows by sql us
er. Say we have two salesmen Fred and Barney, we wanted to create 2 views:
CREATE VIEW Fred.SALES_LEADS AS SELECT * FROM dbo.ALL_SALES_LEADS WHERE SALE
S_PERSON='Fred'
and
CREATE VIEW Barney.SALES_LEADS AS SELECT * FROM dbo.ALL_SALES_LEADS WHERE SA
LES_PERSON='Barney'
Neither salesman should be able to select from ALL_SALES_LEADS and each, bei
ng amazingly SQL savvy salesmen, can log on to sql and execute SELECT * FROM
SALES_LEADS WHERE STATUS='New'.
Problem is that neither because neither Fred nor Barney owns the ALL_SALES_L
EADS table, they can't select off the view with any combination of GRANT/DEN
Y/REVOKE statements that we can find because the ownership chains are broken
.
Our best fallback is to name the views differently but leave them owned by d
bo. Not as neat and tidy. ie
CREATE VIEW dbo.Fred_SALES_LEADS AS SELECT * FROM dbo.ALL_SALES_LEADS WHERE
SALES_PERSON='Fred'
GRANT SELECT ON dbo.Fred_SALES_LEADS TO Fred
Is there any way around our original problem, SQL gurus? I think this would
be a very good use of user-owned views in a number of realistic scenarios.> Our best fallback is to name the views differently but leave them owned by
dbo.
The table and view need to have the same owner in order to use views as a
security mechanism. If each salesperson accesses the database with their
own userid, you might consider filtering using database userid instead of a
hard-coded constant. This way, you only need one view. For example:
CREATE VIEW dbo.SALES_LEADS AS
SELECT *
FROM dbo.ALL_SALES_LEADS
WHERE SALES_PERSON = CURRENT_USER
GO
GRANT SELECT ON dbo.SALES_LEADS TO SalesRole
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Dave Cattermole" <anonymous@.discussions.microsoft.com> wrote in message
news:726B0928-E93C-4233-A5FE-051E8ADA5684@.microsoft.com...
> We have a design using views to partition visibility of table rows by sql
user. Say we have two salesmen Fred and Barney, we wanted to create 2 views:
> CREATE VIEW Fred.SALES_LEADS AS SELECT * FROM dbo.ALL_SALES_LEADS WHERE
SALES_PERSON='Fred'
> and
> CREATE VIEW Barney.SALES_LEADS AS SELECT * FROM dbo.ALL_SALES_LEADS WHERE
SALES_PERSON='Barney'
> Neither salesman should be able to select from ALL_SALES_LEADS and each,
being amazingly SQL savvy salesmen, can log on to sql and execute SELECT *
FROM SALES_LEADS WHERE STATUS='New'.
> Problem is that neither because neither Fred nor Barney owns the
ALL_SALES_LEADS table, they can't select off the view with any combination
of GRANT/DENY/REVOKE statements that we can find because the ownership
chains are broken .
> Our best fallback is to name the views differently but leave them owned by
dbo. Not as neat and tidy. ie
> CREATE VIEW dbo.Fred_SALES_LEADS AS SELECT * FROM dbo.ALL_SALES_LEADS
WHERE SALES_PERSON='Fred'
> GRANT SELECT ON dbo.Fred_SALES_LEADS TO Fred
> Is there any way around our original problem, SQL gurus? I think this
would be a very good use of user-owned views in a number of realistic
scenarios.

owner of maintenance jobs-permission hole?

Hello,
I have a SQL 2000 user who created some maintenance jobs (they are owned by
her account). She can run them manually but says she can't schedule them. If
I change the owner of the jobs to SA, does that mean she could then modify
those jobs and get data with SA rights that she didn't have access to with
her account?
tia,
jjjj,
Why can't she schedule her jobs? If she has public rights to the msdb
database, I believe that is enough to allow her to schedule jobs that run
under her own rights.
If you allow these jobs to run at higher levels of security, then certainly
the job could technically get access to additional data.
If you do not want to grant your user rights to create SQL Agent jobs in
msdb, then you will need to set up the jobs. However, I would suggest
creating a service user account (e.g. User1MaintJobs), grant that account
the needed rights for the job, and assign that account as the job owner.
FWIW,
RLF
"jj" <jeff_detoro@.urmc.rochester.edu> wrote in message
news:eLzCr3nlIHA.696@.TK2MSFTNGP05.phx.gbl...
> Hello,
> I have a SQL 2000 user who created some maintenance jobs (they are owned
> by
> her account). She can run them manually but says she can't schedule them.
> If
> I change the owner of the jobs to SA, does that mean she could then modify
> those jobs and get data with SA rights that she didn't have access to with
> her account?
> tia,
> jj
>

owner of maintenance jobs-permission hole?

Hello,
I have a SQL 2000 user who created some maintenance jobs (they are owned by
her account). She can run them manually but says she can't schedule them. If
I change the owner of the jobs to SA, does that mean she could then modify
those jobs and get data with SA rights that she didn't have access to with
her account?
tia,
jj
jj,
Why can't she schedule her jobs? If she has public rights to the msdb
database, I believe that is enough to allow her to schedule jobs that run
under her own rights.
If you allow these jobs to run at higher levels of security, then certainly
the job could technically get access to additional data.
If you do not want to grant your user rights to create SQL Agent jobs in
msdb, then you will need to set up the jobs. However, I would suggest
creating a service user account (e.g. User1MaintJobs), grant that account
the needed rights for the job, and assign that account as the job owner.
FWIW,
RLF
"jj" <jeff_detoro@.urmc.rochester.edu> wrote in message
news:eLzCr3nlIHA.696@.TK2MSFTNGP05.phx.gbl...
> Hello,
> I have a SQL 2000 user who created some maintenance jobs (they are owned
> by
> her account). She can run them manually but says she can't schedule them.
> If
> I change the owner of the jobs to SA, does that mean she could then modify
> those jobs and get data with SA rights that she didn't have access to with
> her account?
> tia,
> jj
>

owner of dburgent

hi:
I get a database called"linlin".But the owner of database is not dbo or sa,but user "ss".I revert the database into my SQL server.
When I use it with user sa, I need add linlin. before the name of the table and storage processes.But what influnce to do this?
When insert ,update,delete something from linlin with user sa,is it OK?My SQL is personnal edition.
Appreciate your help!

I bet you that you executed your SQL statements under the context of that user 'ss'. That is why it is not showing as "dbo."

The reason why you have to prefix those tables with the database name is because your connection is not using that database. In your connection string, you can specify the [database] property to the name of the database. Then you won't have to prefix your SQL with the database name from there on.

|||

You're right. The SQL statements is under the context of that user 'ss'.
The database is my brother's.And I can not use ss.My brother let me connect database to asp.net.
But what can I do?
My brother let me create a user named "ss",but it desn't work.My "ss" can not connect to the database "linlin".
help me.

|||

The ss user must be granted access to access the database. To do this, in enterprise manager, go to the users. Go to the properties of this user "ss" and under permissions, give the ss DB Owner access to the database.

Owner of database

I've written code that allows a user to create a table according to their specifications. However, when I as another user tries to open the table, I get all sorts of errors. The only reason I can think that this happens is because I dont have sufficient permission/s to access that table. Is this correct, and if so, how do I get around this problem ?

Many thanks.You should GRANT permissions for other user to access the table.
Refer to books online for more information.|||The table owner could also be causing you some greif. In other words if you create 'tbl_one' with 'user_one' and then try and access the table with 'user_two' you may need to explictly identify the tables owner. In other words 'select * from user_one.tbl_one' .
Of course just as Satya mentioned the user would need select permissions on this table (user_one.tbl_one)

Owner of a database

Hello,
What are the effects in working with a database owned by a user 'X'? In other
words, what are the differences whether i.e. the owner is 'sa', a user which is
db_owner, or a "normal" user?
[SQL Server 2K]
Thank You
Joachim
Hi
> What are the effects in working with a database owned by a user 'X'? In
> other
> words, what are the differences whether i.e. the owner is 'sa', a user
> which is
> db_owner, or a "normal" user?
One is that a "normal user" cannot delete/drop any objects created by
another user.
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:eiHwrUAIGHA.1312@.TK2MSFTNGP09.phx.gbl...
> Hello,
> What are the effects in working with a database owned by a user 'X'? In
> other
> words, what are the differences whether i.e. the owner is 'sa', a user
> which is
> db_owner, or a "normal" user?
> [SQL Server 2K]
> Thank You
> Joachim
|||Iam not sure what it is that you want to know but when using SQL
Authentication, you can create you own user say userx and make them
owner of the database i.e assign the role db_owner, There is not
different really with using 'sa', but when you are using sa make sure
you have protected your database correctly
|||You are talking about two different things here.
Making someone the actual owner of a database is NOT the same as putting
them in the db_owner role.
If they are the true owner, their login name will be stored in the
sysdatabases table in the master database, and when they use the database of
which they are the owner, they will have the user name DBO.
A user in the database can be put in the db_owner role, but they still have
their own user name, they are not DBO. Their name is not stored anywhere
outside the database. Within the database they have all the permissions of
the true owner, but they keep their own user name.
Please read about logins, users and database roles in the Books Online.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
<Lucky.Ncube@.gmail.com> wrote in message
news:1138016039.779184.79720@.g44g2000cwa.googlegro ups.com...
> Iam not sure what it is that you want to know but when using SQL
> Authentication, you can create you own user say userx and make them
> owner of the database i.e assign the role db_owner, There is not
> different really with using 'sa', but when you are using sa make sure
> you have protected your database correctly
>

Owner of a database

Hello,
What are the effects in working with a database owned by a user 'X'? In othe
r
words, what are the differences whether i.e. the owner is 'sa', a user which
is
db_owner, or a "normal" user?
[SQL Server 2K]
Thank You
JoachimHi
> What are the effects in working with a database owned by a user 'X'? In
> other
> words, what are the differences whether i.e. the owner is 'sa', a user
> which is
> db_owner, or a "normal" user?
One is that a "normal user" cannot delete/drop any objects created by
another user.
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:eiHwrUAIGHA.1312@.TK2MSFTNGP09.phx.gbl...
> Hello,
> What are the effects in working with a database owned by a user 'X'? In
> other
> words, what are the differences whether i.e. the owner is 'sa', a user
> which is
> db_owner, or a "normal" user?
> [SQL Server 2K]
> Thank You
> Joachim|||Iam not sure what it is that you want to know but when using SQL
Authentication, you can create you own user say userx and make them
owner of the database i.e assign the role db_owner, There is not
different really with using 'sa', but when you are using sa make sure
you have protected your database correctly|||You are talking about two different things here.
Making someone the actual owner of a database is NOT the same as putting
them in the db_owner role.
If they are the true owner, their login name will be stored in the
sysdatabases table in the master database, and when they use the database of
which they are the owner, they will have the user name DBO.
A user in the database can be put in the db_owner role, but they still have
their own user name, they are not DBO. Their name is not stored anywhere
outside the database. Within the database they have all the permissions of
the true owner, but they keep their own user name.
Please read about logins, users and database roles in the Books Online.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
<Lucky.Ncube@.gmail.com> wrote in message
news:1138016039.779184.79720@.g44g2000cwa.googlegroups.com...
> Iam not sure what it is that you want to know but when using SQL
> Authentication, you can create you own user say userx and make them
> owner of the database i.e assign the role db_owner, There is not
> different really with using 'sa', but when you are using sa make sure
> you have protected your database correctly
>

Owner of a database

Hello,
What are the effects in working with a database owned by a user 'X'? In other
words, what are the differences whether i.e. the owner is 'sa', a user which is
db_owner, or a "normal" user?
[SQL Server 2K]
Thank You
JoachimHi
> What are the effects in working with a database owned by a user 'X'? In
> other
> words, what are the differences whether i.e. the owner is 'sa', a user
> which is
> db_owner, or a "normal" user?
One is that a "normal user" cannot delete/drop any objects created by
another user.
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:eiHwrUAIGHA.1312@.TK2MSFTNGP09.phx.gbl...
> Hello,
> What are the effects in working with a database owned by a user 'X'? In
> other
> words, what are the differences whether i.e. the owner is 'sa', a user
> which is
> db_owner, or a "normal" user?
> [SQL Server 2K]
> Thank You
> Joachim|||Iam not sure what it is that you want to know but when using SQL
Authentication, you can create you own user say userx and make them
owner of the database i.e assign the role db_owner, There is not
different really with using 'sa', but when you are using sa make sure
you have protected your database correctly|||You are talking about two different things here.
Making someone the actual owner of a database is NOT the same as putting
them in the db_owner role.
If they are the true owner, their login name will be stored in the
sysdatabases table in the master database, and when they use the database of
which they are the owner, they will have the user name DBO.
A user in the database can be put in the db_owner role, but they still have
their own user name, they are not DBO. Their name is not stored anywhere
outside the database. Within the database they have all the permissions of
the true owner, but they keep their own user name.
Please read about logins, users and database roles in the Books Online.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
<Lucky.Ncube@.gmail.com> wrote in message
news:1138016039.779184.79720@.g44g2000cwa.googlegroups.com...
> Iam not sure what it is that you want to know but when using SQL
> Authentication, you can create you own user say userx and make them
> owner of the database i.e assign the role db_owner, There is not
> different really with using 'sa', but when you are using sa make sure
> you have protected your database correctly
>

Owner

I have a few objects (views, tables, stored procedures) that are owned by a
user called 'appWebUser'. If I'm logged under another user, i.e.
(appTrainer), I have to qualify the owner.object to access it instead of
just the object name. Is there any way to just use the object name like the
dbo owner. For example.
Currently: logged in as appTrainer
select * from appWebUser.table1
What I Want: logged in as appTrainer
select * from table1Sorry, I should have included ...
If you want to get rid of the owner.object issue, change all of the object
owners to dbo.
"Morgan" <mfears@.spamcop.net> wrote in message
news:eCBjzulcDHA.3620@.TK2MSFTNGP11.phx.gbl...
> As long as all of the object names are unique for each type of object
> (table, view, etc) across the whole database, you can rename them using
> sp_changeobjectowner. However, as you'll see in BOL, any permissions on
the
> table must be reapplied once you change the owner. I would go about this
> very carefully.
>
> "Tim" <Tim@.NOSPAM.com> wrote in message
> news:#8dn8XlcDHA.2436@.TK2MSFTNGP12.phx.gbl...
> > I have a few objects (views, tables, stored procedures) that are owned
by
> a
> > user called 'appWebUser'. If I'm logged under another user, i.e.
> > (appTrainer), I have to qualify the owner.object to access it instead of
> > just the object name. Is there any way to just use the object name like
> the
> > dbo owner. For example.
> >
> > Currently: logged in as appTrainer
> > select * from appWebUser.table1
> >
> > What I Want: logged in as appTrainer
> > select * from table1
> >
> >
>

owned schema or role members?

Hi,
I want to create a login (for account ASPNET from the Active Directory) in
sql server express 2005 for a specific database.
When addidng a new user to a specific database, i see:
Owned schemas, where i take db_datareader and db_datawriter
Roles members: also db_datareader and db_datawriter
What's the difference between both and are they both required fpr account
ASPNET?
Tbanks
BartBart
--db_datareader
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/7d71fca8-ad8d-49c5-b4cc-c1cd
ab0fab43.htm
--db_datawriter
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/08a5c847-f993-4402-b3ac-a511
3f41e8c8.htm
"Bart" <b@.sdq.dc> wrote in message
news:OMfgIZlbHHA.3420@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I want to create a login (for account ASPNET from the Active Directory) in
> sql server express 2005 for a specific database.
> When addidng a new user to a specific database, i see:
> Owned schemas, where i take db_datareader and db_datawriter
> Roles members: also db_datareader and db_datawriter
> What's the difference between both and are they both required fpr account
> ASPNET?
> Tbanks
> Bart
>
>
>|||Thanks, but my question was more about the difference between Owned schema
and Role members.
Is it enough to take db_anything in Role members or must the user also owns
a schema with db_anything?
"Uri Dimant" <urid@.iscar.co.il> schreef in bericht
news:eDumYbsbHHA.2088@.TK2MSFTNGP04.phx.gbl...
> Bart
> --db_datareader
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/7d71fca8-ad8d-49c5-b4cc-c1
cdab0fab43.htm
>
> --db_datawriter
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/08a5c847-f993-4402-b3ac-a5
113f41e8c8.htm
>
> "Bart" <b@.sdq.dc> wrote in message
> news:OMfgIZlbHHA.3420@.TK2MSFTNGP05.phx.gbl...
>|||Bart
SCHEMA and Roles are different things. When you create a user ut should be
mapped to schema you have specified ir DEFAULT schema--DBO.
Can you elaborate on what you are trying to achive?
"Bart" <b@.sdq.dc> wrote in message
news:uEq8NwvbHHA.4140@.TK2MSFTNGP06.phx.gbl...
> Thanks, but my question was more about the difference between Owned schema
> and Role members.
> Is it enough to take db_anything in Role members or must the user also
> owns a schema with db_anything?
> "Uri Dimant" <urid@.iscar.co.il> schreef in bericht
> news:eDumYbsbHHA.2088@.TK2MSFTNGP04.phx.gbl...
>|||Well, i want to create a new login for account ASPNET (which runs under
ASP.NET) and then define an user (aspnet) for 'mydatabase'. That user must
get read/write prights to the db. At that level, i see in the window
configuration two things about read /write: Owned Schema and Role members.
So my question is: should i take db_readreader / db_writer in the Ownd
Schema or in Role members or in both?
Thanks
"Uri Dimant" <urid@.iscar.co.il> schreef in bericht
news:%23p8We%232bHHA.4632@.TK2MSFTNGP03.phx.gbl...
> Bart
> SCHEMA and Roles are different things. When you create a user ut should be
> mapped to schema you have specified ir DEFAULT schema--DBO.
> Can you elaborate on what you are trying to achive?
>
> "Bart" <b@.sdq.dc> wrote in message
> news:uEq8NwvbHHA.4140@.TK2MSFTNGP06.phx.gbl...
>|||Bart
Does the user should run queries which manipulate with tables belong to
anohter SCHEMA?
I'd go with ROLES and grant SELECT/EXECUTE permission on SCHEMAs that the
user needs to query
"Bart" <b@.sdq.dc> wrote in message
news:u1ddKM3bHHA.4772@.TK2MSFTNGP05.phx.gbl...
> Well, i want to create a new login for account ASPNET (which runs under
> ASP.NET) and then define an user (aspnet) for 'mydatabase'. That user
> must get read/write prights to the db. At that level, i see in the window
> configuration two things about read /write: Owned Schema and Role members.
> So my question is: should i take db_readreader / db_writer in the Ownd
> Schema or in Role members or in both?
> Thanks
> "Uri Dimant" <urid@.iscar.co.il> schreef in bericht
> news:%23p8We%232bHHA.4632@.TK2MSFTNGP03.phx.gbl...
>|||"Uri Dimant" <urid@.iscar.co.il> schreef in bericht
news:OOCCsw3bHHA.4000@.TK2MSFTNGP02.phx.gbl...
> Bart
> Does the user should run queries which manipulate with tables belong to
> anohter SCHEMA?
> I'd go with ROLES and grant SELECT/EXECUTE permission on SCHEMAs that the
> user needs to query
Thanks, but I still don't understand the difference between giving the Role
db_datareader / db_datawriter to user 'aspnet' and adding user 'aspnet' to
Schema dbdatareader and Schema db_datawriter ...|||Bart (b@.sdq.dc) writes:
> Thanks, but I still don't understand the difference between giving the
> Role db_datareader / db_datawriter to user 'aspnet' and adding user
> 'aspnet' to Schema dbdatareader and Schema db_datawriter ...
You should not need the schemss. They exist of legacy reasons. In SQL 2000
there was no difference between a role/user on the one hand and a schema
on the other. If you created a user/role X, you also got a schema X included
in the price.
In SQL 2005 schemas and database principals (users and roles) are separated.
But Microsoft still by default creates schemas for all pre-defined roles
and users, since old applications may rely on these and create objects
in these schemas.
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|||ok, thanks
"Erland Sommarskog" <esquel@.sommarskog.se> schreef in bericht
news:Xns99004B34D29Yazorman@.127.0.0.1...
> Bart (b@.sdq.dc) writes:
> You should not need the schemss. They exist of legacy reasons. In SQL 2000
> there was no difference between a role/user on the one hand and a schema
> on the other. If you created a user/role X, you also got a schema X
> included
> in the price.
> In SQL 2005 schemas and database principals (users and roles) are
> separated.
> But Microsoft still by default creates schemas for all pre-defined roles
> and users, since old applications may rely on these and create objects
> in these schemas.
>
> --
> 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