How to control the security in this case?

Dear all,
I have a SQL Server 2000 in my head office, it has several databases.
However, one of the databases is maintained by a colleague of a regional
office. He has the all the authority (read / write) to work on this database
.
But there is a problem, we have developed some DTS packages in head office
and this colleague is going to make his own packages. We would like to have
a
control that this colleague is not able to access our DTS packages (not even
read), he can only opens his own the DTS packages.
Is there any control to achieve this purpose? Please advise. Thanks a lot.
IvanOpen the packages in design mode, use "save as" and set the owner and user
password.
AMB
"Ivan" wrote:

> Dear all,
> I have a SQL Server 2000 in my head office, it has several databases.
> However, one of the databases is maintained by a colleague of a regional
> office. He has the all the authority (read / write) to work on this databa
se.
> But there is a problem, we have developed some DTS packages in head office
> and this colleague is going to make his own packages. We would like to hav
e a
> control that this colleague is not able to access our DTS packages (not ev
en
> read), he can only opens his own the DTS packages.
> Is there any control to achieve this purpose? Please advise. Thanks a lot.
> Ivan
>|||Hi, Alejandro
I'm afrais that does not solve the problem. I still keep seeing the DTS's
created by others.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:B1A8ECBE-047C-4095-889A-A7DA9C4BFD04@.microsoft.com...[vbcol=seagreen]
> Open the packages in design mode, use "save as" and set the owner and user
> password.
>
> AMB
> "Ivan" wrote:
>
database.[vbcol=seagreen]
office[vbcol=seagreen]
have a[vbcol=seagreen]
even[vbcol=seagreen]
lot.[vbcol=seagreen]|||Hi Uri,
It is allowed that the colleague in the regional office to create his own
DTS. I just don't want to view the DTS created in my head office.
Besides setting password in each DTS package, is there any other way?
Thanks.
Ivan
"Uri Dimant" wrote:

> Hi, Alejandro
> I'm afrais that does not solve the problem. I still keep seeing the DTS's
> created by others.
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:B1A8ECBE-047C-4095-889A-A7DA9C4BFD04@.microsoft.com...
> database.
> office
> have a
> even
> lot.
>
>

How to control MS SQL Server DataBases permissions through visual basic program

Hello,

Could someone help me to view, add, delete, and modify users of SQL Server DataBase and permissions of them on this database through Visual Basic Programming

Because I want to do VB application through which I will view, add,delete, and modify users of SQL Server DataBase and their permissions on this database.

Thank you very very muchYou can use the system stored procedures.|||Originally posted by rnealejr
You can use the system stored procedures.

Thank you very much Mr. rnealejr for your reply.

Would you like to send me a sample code in visual basic to view ,for example, users of SQL Server DataBase and their permissions??

I will be thankful|||Which version of sql server are you using 7 or 2000 ?|||Originally posted by rnealejr
Which version of sql server are you using 7 or 2000 ?
Hello, Mr. rnealejr
I use MS SQL Server 2000.
Also, I have a problem with SQL Server.
I have about 50 tables and views in an Oracle DataBase and I have tried to translate them to SQL Server DataBases.
They were translated succefully but there was two problems :
1- Key constraints were not translated? (i.e. the destination SQL Server database became without primary key and foreign key constraints and aother constraints)
2- Also, views are translated into tables not into view ??

Could you slove these problems?

After I get more information I will be able to serve your good forum with good posts becuase until now I am student

Thank you very much|||How did you translate from oracle to sql server ?|||You can use sp_helpuser, sp_helplogins - use the ado command object.|||Originally posted by rnealejr
How did you translate from oracle to sql server ?

Yes
I have used DTS Wizard to do this translation but tables were translated without constraints and also views were translated into tables instead of corresponding views although all tables, from whcih views get data, are available in the translated database.

Thank you very much Mr.

How to connect two databases on alocal server in SQL

Hello , This is Vidya newly joined to this group.
I want some help .... Its really urgent

In my project I want to connect to two databases on alocal server in SQL ? Is there any SQL query to connect two databases ?

Situation is=>
There are two Databases Db1, Db2. I want to take data from tables of Db1 to data of tables of Db2 and vice versa.I want simple SQL query without any scripting language or VB statements. Its very urgent Please help me in that.If the two databases are on the same server you can reference either one in the other by using the fully qualified object name: [database].[owner].[object]|||Thank You Sir, For your quiock reply. I have tried it but still I am unable to connect.

Sir can we have '-' (Hyphen Character) for our database name? like a_com_-_b, Is It a valid name...

Thank You|||Yes, database names can contain hyphens. But I would not recommend it for reasons of style.

Post the code you are trying that is not working. You should be able to do something like:
Use Database1
go

select Table1.Column1,
Table2.Column2
from Table1
inner join Database2.dbo.Table2 as Table2 on Table1.pkey = Table2.pkey

How to connect generic database field to report?

Hello,

I want to make a report where multiple users can use the same report to connect to their databases and then print out the report with information from those databases. Both databases have the exact same tables and fields but the data that is in them is different. However, I have only been able to figure out how to connect the report to one specific database, and therefore the report always prints out information from that database instead of the user specified one. So let's say I want my report to print out the name that is in the database field Name for any database I connect to, how would I do this?

Assuming SSRS 2005, you can use an expression-based connection string as demonstrated in the ExpressionBasedConnection sample report in this download. Since I think it is a no-no to pass the connection string as a report parameter, the report gets it from the Report Server config file.|||I looked at your samples but I'm not sure I quite understand them. It looks like your ExpressionBasedConnection report just connected to the database named AdventureWorks. But my reports are going to be connecting to databases that have the same underlying structure but with variable names that I won't know when creating the report. I will only know the name of the database when the user runs our software to connect to whatever database they use. But I still want to create a report that will use, for example, the Name, Address, State fields from the Customer table, but I don't want to hook it up to one specific database. How do I do this?|||

OK, but somehow the end-user has to specify the database name at some point, correct? Let's assume that the user will pass the database name as a report parameter. Then, you can use an expression-based connection string in the report data source (it must be private), to establish connection to that database.

Did I miss anything?

|||Yes at some point the user will specify what database they want to connect to but I don't think I fully understand how to use that database in the report. So let's say I want to add a dataset to my report by the name of Company. So in the Dataset tab of the report I select new dataset and I get to the Dataset dialog box. So I name the dataset Company. I go into the "Datasource connection" dialog box by clicking the ... button. I've created a parameter for the dataset call ConnectionStr, so therefore in the Connection String edit box, I type " = Parameters!Connection.Value". Then I click OK that dialog and return to the Dataset dialog box. For the query string I type "SELECT * FROM Company" and then click OK. So in the datasets toolbar, I can see a dataset named Company but it doesn't have any of the company fields in it like Name, Address, etc. How to I get these fields in the dataset so I can use them in my report? Since the report doesn't know what the ConnectionStr parameter is yet it doesn't seem like it will be able to do this.|||Start with a normal connection string. Click the Refresh Fields button on the Data tab in the Report Designer. Then change to an expression-based connection string.|||Alright, so I did what you suggested, but when I drag a field from the dataset I want it says First(Fields!Name.Value, "Company"), but I don't want a report that just prints the information of the first company in the dataset, I want a report printed for every company that is in the dataset after my query has been applied.|||This usually happens when the data region is bound to a different dataset than the one you are dragging the fields from. Take a look at the Dataset property of the data region and clear it (or set to Company). Then, you can re-drag your fields or remove the First function so the field reference is =Fields!Name.Value.|||So I've accomplished this, and now I am having a similar problem of connecting a generic data source to the reportviewer. When I'm in the Data Sources window and I select Add New Data Source, in order to add a new one, I have to select a specific database. But I don't want the reportviewer to connect to one specific data source, I just want it to print out the report that I have made following your tips, how do I do this?

How To Connect From SQL Server Database To Another SQL Server Database

HI to all...

Im using Sql server 2005.

I have two databases.

1st database Name is : rmstime

2nd database Name is : rmsdev

Both are having Permissions(User names and passwords.)

Now i want to access a a table in rmstime from rmsdev database.

Please send me a solution.

Any help should be appreciated...

Thanx in advance.

Srinivas Kajuluri.

Assuming that you have the relevant permissions to do so, you can just access tables/sprocs/views etc with 3 part naming:

Code Snippet

SELECT * FROM rmstime.dbo.YourTable

or

EXEC rmstime.dbo.YourProc

HTH!

|||

It will work only when u have access to the database..

but i need to specify the username and password.

For Ex username and password of the databse is 'aadddd' and 'qqqwww',

know i want to know where to specify these username and passwords?

|||

use the following System stored Proc....

sp_addlinkedserver 'SERVER NAME'

|||

Ok, you didn't specify that these databases were on different servers.

In this case, you will need to add a linked server using sp_addlinkedserver and add the security using sp_addlinkedsrvlogin.

You also have the option to enable ad hoc distributed queries and then use OPENROWSET.

HTH

how to configure multiple databases for one asp.net website

Hello:
I am working on a website that will require the use of multiple
databases - a unique database for each user in fact. It is somewhat of
a light CRM application and it is important to keep a user's customer
information in their own database.
So I am hoping to get input on the best way to implement this. The
approach I am using so far includes storing the database connection
string in a property of the user's profile object. However, this
requires referencing the profile object each time I hit the database.
And for pages that are using the sqldatasource control, that means
assigning the connectionstring property for that control in the
page_load event outside of an "if not page.ispostback" block. So I
wonder if there isn't a better way.
Any feedback would be really appreciated here.
ChrisHow about storing the user's database name in their profile object and/or in
a session variable. Then you can prefix all of your calls with the database
name:
... "EXEC " + session("database_name") + ".dbo.ProcedureName";
Of course, if you are doing this for scalability reasons, you will probably
want to consider multiple connection strings for different servers...
<chrishalldba@.yahoo.com> wrote in message
news:1138581470.361914.172540@.o13g2000cwo.googlegroups.com...
> Hello:
> I am working on a website that will require the use of multiple
> databases - a unique database for each user in fact. It is somewhat of
> a light CRM application and it is important to keep a user's customer
> information in their own database.
> So I am hoping to get input on the best way to implement this. The
> approach I am using so far includes storing the database connection
> string in a property of the user's profile object. However, this
> requires referencing the profile object each time I hit the database.
> And for pages that are using the sqldatasource control, that means
> assigning the connectionstring property for that control in the
> page_load event outside of an "if not page.ispostback" block. So I
> wonder if there isn't a better way.
> Any feedback would be really appreciated here.
> Chris
>|||How about storing the user's database name in their profile object and/or in
a session variable. Then you can prefix all of your calls with the database
name:
... "EXEC " + session("database_name") + ".dbo.ProcedureName";
Of course, if you are doing this for scalability reasons, you will probably
want to consider multiple connection strings for different servers...
<chrishalldba@.yahoo.com> wrote in message
news:1138581470.361914.172540@.o13g2000cwo.googlegroups.com...
> Hello:
> I am working on a website that will require the use of multiple
> databases - a unique database for each user in fact. It is somewhat of
> a light CRM application and it is important to keep a user's customer
> information in their own database.
> So I am hoping to get input on the best way to implement this. The
> approach I am using so far includes storing the database connection
> string in a property of the user's profile object. However, this
> requires referencing the profile object each time I hit the database.
> And for pages that are using the sqldatasource control, that means
> assigning the connectionstring property for that control in the
> page_load event outside of an "if not page.ispostback" block. So I
> wonder if there isn't a better way.
> Any feedback would be really appreciated here.
> Chris
>|||Aaron:
Thanks for your reply. I actually left out some stuff in my post to
keep the thread somewhat open-ended. Your suggestion actually looks
very close to what I ended up implementing. I am storing a database
connection string in a profile object, then loading that into a session
variable when the user logs in. It sounds like I'm on the right track
so that's reassuring. Thanks for the peace of mind!
Chris

How to configure a JBOD disk array (PowerVault) for Performance

We have an application that is experiencing I/O contention,
particularly in tempdb but also in two other databases. The data is
stored on mirrored PowerVault 220's, each with 10 of 14 possible disks.
The PowerVaults are JBOD devices, not true SANs. The current config has
four separate groups of physical drives assigned to distinct logical
drives for log files, tempdb, and the two app dbs. This means, for
example, that tempdb resides on one mirrored drive. The standard advice
when faced with disk contention is to add spindles if possible. With 4
empty slots, we would presumably assign the new physical disks to the
most stressed db, e.g. tempdb.

An alternative arrangement would be to combine all the physical drives
into one logical drive, and put all the files, log and data, onto the
single logical drive. The hope for this configuration is that the
PowerVault would automagically distribute the data among the drives
such that all drives were in use, all spindles reading and writing at
maximum capacity when necessary. It is my understanding that
full-featured SANs, like NetApps and EMC models, do this. My question
is whether this configuration is best for the PowerVault, as well. Or
is this the essential difference between JBOD and a true SAN?

Has anyone tried both arrangements?

Advice is much appreciated.Hi

I can't say I know anything about the internals of JBOD.. but I would expect
combining the drives into a single logical drive to not be benificial even
though they are distributing the data over more spindles the type of access
between log file and data files will probably be counter productive.

If you are having contention on tempdb you may want to consider splitting
tempdb into multiple files (preferrably on separate sets of spindles) as
described in http://support.microsoft.com/defaul...kb;en-us;328551

John

"JRoughgarden" <jroughgarden@.stanfordalumni.org> wrote in message
news:1139511253.211524.40670@.o13g2000cwo.googlegro ups.com...
> We have an application that is experiencing I/O contention,
> particularly in tempdb but also in two other databases. The data is
> stored on mirrored PowerVault 220's, each with 10 of 14 possible disks.
> The PowerVaults are JBOD devices, not true SANs. The current config has
> four separate groups of physical drives assigned to distinct logical
> drives for log files, tempdb, and the two app dbs. This means, for
> example, that tempdb resides on one mirrored drive. The standard advice
> when faced with disk contention is to add spindles if possible. With 4
> empty slots, we would presumably assign the new physical disks to the
> most stressed db, e.g. tempdb.
> An alternative arrangement would be to combine all the physical drives
> into one logical drive, and put all the files, log and data, onto the
> single logical drive. The hope for this configuration is that the
> PowerVault would automagically distribute the data among the drives
> such that all drives were in use, all spindles reading and writing at
> maximum capacity when necessary. It is my understanding that
> full-featured SANs, like NetApps and EMC models, do this. My question
> is whether this configuration is best for the PowerVault, as well. Or
> is this the essential difference between JBOD and a true SAN?
> Has anyone tried both arrangements?
> Advice is much appreciated.|||Yes, we have split tempdb in accordance with this article. No obvious
improvement yet. Looking at the -T1118 flag in a replicated
environment.

I am gathering that a JBOD is just a holder for physical disks that is
capable of splitting them up into logical disks, with or without RAID.
No real optimization.

Still, it would be an interesting experiment to put everything on one
logical disk comprising 14-physical disks vs. splitting the 14 into
separate logical drives for log files, tempdb mdf, and app data file
mdfs.

How to configure a Database both, as a Publisher and a Subscriber ?

Hi ,
I am using merge replication wherein we have the following architecture.
We have three different databases. One DB acts as the back office server. The second acts as the desktop DB and the last one is a mobile DB i.e. SQLCE DB.
Data needs to be synchronized between the back office server and the desktop. The SQL CE database connects to the desktop for data synchronization. i.e.
I want to push and pull data from the mobile DB to the desktop DB. Hence, here the Desktop DB acts as the Publisher, and the SQL CE DB acts as subscriber.
Further, the desktop DB needs to push and pull data from the back office DB. Hence, in this case, the Desktop DB acts as the subscriber to the Server, and the back office DB acts as publisher.

Merge replication is not allowing me to make the desktop DB as publisher as well as the subscriber at the same time.

Is there any way we can accomplish this?

Thanks in advance.
Regards,
Pushkar.Hi,

Open the SQL Enterprise manager and select the distributor SQL server.
Select the replication option in the tools menu.
Select the option Configure publishers, subscribers and distributors.

Remember to declare all the involved SQL servers on the Distributor (!) via the Client Network Utility (a small program which is installed together with SQL server)

Success, VincentJS

How to compare two databases using their DDLs?

Hello all!

My question is: how to compare two database structures if I only have
their DDL files? As the result I would expect an sql-script upgrating
one database structure to another.

The most 3rd-party tools I tested require connection to both databases.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!"Evgeny Gopengauz" <evgop@.ucs.ru> wrote in message
news:4124caa2$0$14418$c397aba@.news.newsgroups.ws.. .
> Hello all!
> My question is: how to compare two database structures if I only have
> their DDL files? As the result I would expect an sql-script upgrating
> one database structure to another.
> The most 3rd-party tools I tested require connection to both databases.
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Personally, I would use the DDL to create two databases, then compare them
with a tool. If you want to be able to compare two databases without having
MSSQL available, then you would have to look for a tool which supports that.
I believe ERwin does, although I'm not sure (and it's rather expensive).

Simon

How to compare SQL server stored procedures from two separate databases?

Is there a straight forward way or a tool to compare SQL server database objects, such as stored procedures located in separate databases?

Well, the way that I always do it is to use the RedGate SQL Compare tool. www.red-gate.com. It works very nice and will compare everything, including permissions.

There are other ways, and Buck Woody (who I have seen around the forums) has an article here that covers it pretty well:

http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=210&rl=1

|||You can use Visual Studio Team System(VSTS) for Database professional. It is covered all the features of readgate and something extra.|||

I use the easiest/dumbest/cheapest way

Get SPs from both databases, and use free version of examdiff to compare them (or you can use Visual SourceSafe, etc..)

|||

There are several free tools that do this. The one that I use is SQL Effects Clarity CE.

It compares tables, stored procedures, views, functions, users, roles, etc.

You can download it here: http://www.sqleffects.com/sfxClarityDetails.html

Hope this helps.

How to compare database structures?

Let us suppose that I have two similar databases and need to create an
sql-script upgrating one database structure to another. For example, these
databases are from different versions of some software, first is from early
version, next is from current, and second one contains several new tables,
sevelal new fields in old tables, several new or changed stored procedures,
UDFs and so on.

How to solve this problem using standard tools?http://www.red-gate.com/SQL_Compare.htm

"Evgeny Gopengauz" <evgop@.ucs.ru> wrote in message
news:ce42is$c8r$1@.news.rol.ru...
> Let us suppose that I have two similar databases and need to create an
> sql-script upgrating one database structure to another. For example, these
> databases are from different versions of some software, first is from
> early
> version, next is from current, and second one contains several new tables,
> sevelal new fields in old tables, several new or changed stored
> procedures,
> UDFs and so on.
> How to solve this problem using standard tools?|||"Evgeny Gopengauz" <evgop@.ucs.ru> wrote in message news:<ce42is$c8r$1@.news.rol.ru>...
> Let us suppose that I have two similar databases and need to create an
> sql-script upgrating one database structure to another. For example, these
> databases are from different versions of some software, first is from early
> version, next is from current, and second one contains several new tables,
> sevelal new fields in old tables, several new or changed stored procedures,
> UDFs and so on.
> How to solve this problem using standard tools?

With the tools supplied with MSSQL, there's really no way to do this
easily. Most people buy a third-party tool (Red Gate, Embarcadero
etc.) to do comparisons and migrations. There are some good, cheap
tools, and unless you have lots of time and you want to re-invent the
wheel, you should probably investigate what's available.

Simon|||> > How to solve this problem using standard tools?
> With the tools supplied with MSSQL, there's really no way to do this
> easily. Most people buy a third-party tool (Red Gate, Embarcadero
> etc.) to do comparisons and migrations. There are some good, cheap
> tools, and unless you have lots of time and you want to re-invent the
> wheel, you should probably investigate what's available.

Thank you! Now I guess why I have found nothing in the BOL :)|||The latest issue of SQL Magazine has a tool from www.apexsql.com that may
help you out.
Oscar

"Evgeny Gopengauz" <evgop@.ucs.ru> wrote in message
news:ce42is$c8r$1@.news.rol.ru...
> Let us suppose that I have two similar databases and need to create an
> sql-script upgrating one database structure to another. For example, these
> databases are from different versions of some software, first is from
early
> version, next is from current, and second one contains several new tables,
> sevelal new fields in old tables, several new or changed stored
procedures,
> UDFs and so on.
> How to solve this problem using standard tools?|||In a pinch, you can always dump the structures of the two databases into
two text files and use a DIFF utility to identify the differences. No
very sexy but it gets the job done.

Farid

Evgeny Gopengauz wrote:
> Let us suppose that I have two similar databases and need to create an
> sql-script upgrating one database structure to another. For example, these
> databases are from different versions of some software, first is from early
> version, next is from current, and second one contains several new tables,
> sevelal new fields in old tables, several new or changed stored procedures,
> UDFs and so on.
> How to solve this problem using standard tools?|||Hi Evgeny,

Try dbMaestro. It's a product that allows comparison, migration and
archiving of database schema and data. it is generate migration script
for different between schema and data , and you can run this script on
sql server

You can find it here:

http://www.extreme.co.il

fn <f_n_a_c_e_r_removeunderlines@.hotmail.com> wrote in message news:<R7OdnW0-MNboz5rc4p2dnA@.britsys.net>...
> In a pinch, you can always dump the structures of the two databases into
> two text files and use a DIFF utility to identify the differences. No
> very sexy but it gets the job done.
> Farid
>
> Evgeny Gopengauz wrote:
> > Let us suppose that I have two similar databases and need to create an
> > sql-script upgrating one database structure to another. For example, these
> > databases are from different versions of some software, first is from early
> > version, next is from current, and second one contains several new tables,
> > sevelal new fields in old tables, several new or changed stored procedures,
> > UDFs and so on.
> > How to solve this problem using standard tools?