Showing posts with label updated. Show all posts
Showing posts with label updated. Show all posts

Friday, March 30, 2012

Protect and invirible Database from each other

Hi,
I'm using Windows 2000 Server + SQL Server 2000 + updated SP
This server is our Web Hosting services and we have many database in SQL Ser
ver.
Each databases are belonged to each customer domains in this Server.
All customers work with SQL Server by using SQL Enterprise Manage to connect
to the Server via TCP/IP.
My problem is that, after customers connect to the SQL Server, they can see
every Databases' name and table name although I'd set the right of each cust
omer to be able to see only their own database.
I'd ask many SQL Experts but they said that if I used SQL Enterprise manager
to remote connect to the SQL Server, this senario could not be protected an
d let me ask Microsoft.
Using SQL Enterprise Manager is the easiest way for my customer to manage th
eir database, so if I let them use the SQL command line to connect to the SQ
L Server, no customers will use my service.
Please help.
Regards,
NeungThere is no way currently to stop them from seeing the other databases. But
unless you give them specific permissions they can not access or view the
data in them.
Andrew J. Kelly SQL MVP
"Neung" <Neung@.discussions.microsoft.com> wrote in message
news:F5F3F38C-8F8B-499F-8C9C-7BE19D350C9A@.microsoft.com...
> Hi,
> I'm using Windows 2000 Server + SQL Server 2000 + updated SP
> This server is our Web Hosting services and we have many database in SQL
Server.
> Each databases are belonged to each customer domains in this Server.
> All customers work with SQL Server by using SQL Enterprise Manage to
connect to the Server via TCP/IP.
> My problem is that, after customers connect to the SQL Server, they can
see every Databases' name and table name although I'd set the right of each
customer to be able to see only their own database.
> I'd ask many SQL Experts but they said that if I used SQL Enterprise
manager to remote connect to the SQL Server, this senario could not be
protected and let me ask Microsoft.
> Using SQL Enterprise Manager is the easiest way for my customer to manage
their database, so if I let them use the SQL command line to connect to the
SQL Server, no customers will use my service.
> Please help.
> Regards,
> Neung|||Thanks and hope it will be solved in the next version.
Regards,
Neung
"Andrew J. Kelly" wrote:

> There is no way currently to stop them from seeing the other databases. B
ut
> unless you give them specific permissions they can not access or view the
> data in them.
> --
> Andrew J. Kelly SQL MVP
>
> "Neung" <Neung@.discussions.microsoft.com> wrote in message
> news:F5F3F38C-8F8B-499F-8C9C-7BE19D350C9A@.microsoft.com...
> Server.
> connect to the Server via TCP/IP.
> see every Databases' name and table name although I'd set the right of eac
h
> customer to be able to see only their own database.
> manager to remote connect to the SQL Server, this senario could not be
> protected and let me ask Microsoft.
> their database, so if I let them use the SQL command line to connect to th
e
> SQL Server, no customers will use my service.
>
>|||Yes, SQL 2005 has a lot of improvements in this area.
Andrew J. Kelly SQL MVP
"Neung" <Neung@.discussions.microsoft.com> wrote in message
news:032A5CFE-D0D9-4727-8032-707D90436D80@.microsoft.com...[vbcol=seagreen]
> Thanks and hope it will be solved in the next version.
> Regards,
> Neung
>
> "Andrew J. Kelly" wrote:
>
But[vbcol=seagreen]
the[vbcol=seagreen]
SQL[vbcol=seagreen]
can[vbcol=seagreen]
each[vbcol=seagreen]
manage[vbcol=seagreen]
the[vbcol=seagreen]|||> My problem is that, after customers connect to the SQL Server, they can
see every Databases' name and table name although I'd set the right of each
customer to be able to see only their own database.
Although database names are visible, I would not expect users to be able to
see other database contents unless they have explicitly been granted access
or the 'guest' user is enabled in the other databases.
Hope this helps.
Dan Guzman
SQL Server MVP
"Neung" <Neung@.discussions.microsoft.com> wrote in message
news:F5F3F38C-8F8B-499F-8C9C-7BE19D350C9A@.microsoft.com...
> Hi,
> I'm using Windows 2000 Server + SQL Server 2000 + updated SP
> This server is our Web Hosting services and we have many database in SQL
Server.
> Each databases are belonged to each customer domains in this Server.
> All customers work with SQL Server by using SQL Enterprise Manage to
connect to the Server via TCP/IP.
> My problem is that, after customers connect to the SQL Server, they can
see every Databases' name and table name although I'd set the right of each
customer to be able to see only their own database.
> I'd ask many SQL Experts but they said that if I used SQL Enterprise
manager to remote connect to the SQL Server, this senario could not be
protected and let me ask Microsoft.
> Using SQL Enterprise Manager is the easiest way for my customer to manage
their database, so if I let them use the SQL command line to connect to the
SQL Server, no customers will use my service.
> Please help.
> Regards,
> Neung

Wednesday, March 28, 2012

Propery expressions: Values not updated

I see some strange behavior when running a package using the SQL Server Agent. The package I run calls a number of child packages, in which I use property expressions to set - among other things - the path to text files I read into a database.

The property expressions are in the form: @.[User::ThePath] + "\\TheFile.txt". The variable ThePath is read from an XML configuration file at runtime from the "master" package and passed on to the child packages using a parent package variable configuration.

My problem is this: When the package is executed by the SQL Server Agent, the properties for the path of the text files are not updated, and the package fails, as it cannot locate the files. When using DTExecUI to execute the same package, everything runs fine.

Could it be a permissions issue? I don't think so, as the SQL Server Agent runs under the same account as I am logged in when executing with DTExecUI.

Anyone?

It does sound a bit like permissions.
So are you referring to the Account the Agent service runs under OR the account the specific Agent Job Step is executing AS, which are 2 different things.

>>>Could it be a permissions issue? I don't think so, as the SQL Server Agent runs under the same account as I am logged in when executing with DTExecUI.

http://support.microsoft.com/?kbid=918760

|||The SQL Agent is running under a local administrator account, which is the same I use when I log in and run the package using DTExecUI. In the scheduled job, the package is "Run as" the "SQL Agent Service Account", which I think should mean that it is executing in the same security context as when I run it manually?!|||

This one has been bugging me big time!

Now, it seems that I might have found a workaround. If I store the main/parent package (the one executed in the Agent job) in MSDB and let the job execute it from here, everything seems to work fine and the property expressions are properly updated at runtime - even though those property expressions are used in the child packages. Previously I had all the packages stored in SSIS Package Store (File System), and that is also where I still keep the child packages.

Can anyone explain this behavior?

|||Can anyone give any explanation for this behavior?|||Now, it gets even more strange. When the job runs on a schedule, it fails, but when I start it manually by clicking "Start job at step..." it runs fine.

Friday, March 23, 2012

Propagation of event notification when tables are updated.

I would like to propagate an event signal to an external application
when a table in my MSSql2000 server is updated.

Prog A; I have an external application adding records to a table.
Prog B; I have another external application using this table as well.

When Prog A creates a new record in the Table, how can I have Prog B be
notified of the event without polling the table or creating a link
between Prog A and Prog B.

Thanks.> When Prog A creates a new record in the Table, how can I have Prog B be
> notified of the event

Depends what you mean by "notified". If you want to invoke some code within
the current process (B) then the obvious way is to have B read a table or
watch for some other event and then act accordingly. What exactly do you
want to achieve? Is this simply about optimistic locking?

--
David Portas
SQL Server MVP
--|||dubian (collatz@.bigtexansoftware.com) writes:
> I would like to propagate an event signal to an external application
> when a table in my MSSql2000 server is updated.
> Prog A; I have an external application adding records to a table.
> Prog B; I have another external application using this table as well.
> When Prog A creates a new record in the Table, how can I have Prog B be
> notified of the event without polling the table or creating a link
> between Prog A and Prog B.

By far, the easiest way is to poll.

The other way would be to have a trigger on the table, that fires of an
extended stored procedure or OLE object to somehow send a singal to
Process B. Since extended stored procedures and local OLE objects in
the same memory space as the rest of SQL Server, they are somewhat
dangerous: if they crash on an access violation, the entire server
goes belly-up.

I believe that you also can use the sp_OAxxx routiens to start an
OLE object on a remote server. In this there is less risk for crashes.
But this is like to take time, and when you are in a trigger you are
in a transaction hold locks. If the update frequency is high, this
trigger can kill your throughput.

A variation is to have the trigger to write to a table, and then
run a job from SQL Server Agent that reads the table and alerts the
other process. Such a job would run once a minute or so. Since this job
could be an ActiveX task you could signal with XP:s and that.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp