only those jobs that run in the databases to which they have access

In the book <Inside SQL Server 2005 Tools>,

there is a paragraph as below:

=====================

Warning

In an ideal world, members of SQLAgentReaderRole and SQLAgentOperatorRole would be restricted to viewing and acting upon only those jobs that run in the databases to which they have access. Unfortunately, the SQL Server Agent security model does not allow this finer-granularity filtering. Therefore, when you make a user a member of these roles you must realize the implied security consequences to avoid unintentional information disclosure.

=====================

What is meaning of the sentence in red above?

Members of these database roles can view and execute jobs that they own, and create job steps that run as an existing proxy account, if you use.

/3GB not working

Hi,

I am on a box with 4 GB of memory that only runs SQL Server. SQL Server is using 1.7 GB as per task manager. There are a lot of databases on this box. I looked at the boot.ini and /3GB was not set. I have done this at least 50 times before: turn on the /3GB switch and SQL Server will use up to 2.7 GB per task manager. (I know it is actually 2GB and 3GB but it seems that in task manager it shows up as 1.7-1.8 and 2.7-2.8)

Anyway back to the story. So I set /3GB, rebooted the server and SQL Server still only consumes up to 1.7GB. I realize that SQL Server might not actually need more memory, but I have a distinct feeling that it wants more but is being constrained; that is, the the /3GB switch is not working. More relevant information:

1) the boot.ini line:
multi(0)disk(0)rdisk(0)partition(1)\WINNT="Microsoft Windows 2000 Advanced Server" /fastdetect /3GB

2) max server memory (MB) = 3000

3) min server memory (MB) = 3000

4) awe enabled = 0

5) @.@.version = 8.00.2039 which is SQL Server 2000 SP4

Has anyone seen this before? Any ideas on how to troubleshoot this?Just figured out the problem: This server is using SQL Server 2000 Standard Edition. For the benefit of all future readers of this thread, the maximum amount of memory that SQL Server 2000 Standard Edition and SQL Server 2000 Personal Edition can ever use is 2GB. SQL Server 2000 Developer's Edition and SQL Server 2000 Enterprise Edition will both go higher. The actual amount of memory is dependent on the operating system.

.mdf and .ldf files

Hi All,
One of my databases has .ldf file that is bigger in size than .mdf file. Is it a problem? If yes, what can cause this problem? And how to fix it? I looked at all the other ones and the .ldf files are smaller than .mdf files. Thanks.You probably forgot to change the Recovery Model from FULL to SIMPLE. After you've fixed that, dump the transaction log. From Query Analyzer

ALTER DATABASE myDb SET RECOVERY SIMPLE
GO
BACKUP LOG myDb WITH TRUNCATE_ONLYBe sure to change myDb to your database name.

-PatP|||Thank you.|||...Except doing so will not change the size of the log device.

Right-click on the database and see if you have any LOG (and FULL for that matter) backups done. After confirming that you have, determine what is the business requirement for database recoverability after a failure (1 hour, 6 hours, 24 hours, or "I don't care"), determine the volume of activity against the database (you can use perfmon Transactions/sec counter), and set up a transaction log dump job. Set up an alert to notify you when the space used on the log device exceeds the full size of the data device, and run for a day under this set of settings. Only then you can intelligently say what the actual size of the log should be, and can safely shrink the file to that size.|||...Except doing so will not change the size of the log device.Hmmm... Maybe I mis-read the description of the TRUNCATE_ONLY option in the BOL for BACKUP LOG (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ba-bz_35ww.asp), but it sure looks like it will reduce the size of the device. Empirically, it works as described in BOL for me.

I will agree with you (rdjabarov) that a DBA should not willy-nilly truncate the log or change the logging options. This is definitely something that should be considered before you just blindly hack away.

-PatP

(ts Urgent) Connect to different databases

Hi

I have my database on four different database servers. I have a web appliction using asp.net 2.0. i want to add data to all four database servers. How can i do that with asp.net 2.0

Thanks in advance

Take care

Bye

In you web.config you can add all the connectionStrings to the section connectionStrings.

Then in your code, you can retrieve the relevant connectionString based on some condition to connect to the required database.

|||

Hi

I really appreciate your response

Thanks for your reply

Take care

Bye

(SS2000) question about distributed queries

I need to write some distributed SQL. I'm accessing databases outside my local instance through Linked Servers.

My problem is: this SQL will run at many customer sites and the database names will be different at each.

So to refer to a table in another instance I of course have to use <LinkedServerName>.<dbname>.<owner>.<object>.

Problem: the <dbname> will be different depending on the customer site. So hardwiring the <dbname> into my scripts is a pretty major problem.

I want to avoid dynamically generating the SQL and executing through sp_executesql.

Is it possible to define a Linked Server in such a way that I can skip over the database name (refered to as "catalog" in the definition). I have a one to one mapping between databases and Linked Servers, so per Linked Server I can assume access to one and only one database.

I'm using SS2000 so synonyms are out of the picture.

In a nutshell: I need to write sciprts that can easily adapt to different database names in all the distributed queries I'm writing. Is it possible to do this in SS2000?

Please help!

Best Regards,

Mike

Use the OPENROWSET function (see BOL topic OPENROWSET (Transact-SQL)).

This will allow you to specify a connection provider (oledb maybe) and connection string, then a query to run on the target server/db.

EXAMPLE:

SELECT a.*

FROM OPENROWSET(<provider name>, <connection string>,

<query>) AS a;

So you could run the same query on different server and different DBs just by adjusting the connection string.

(SS2000) question about distributed queries

I need to write some distributed SQL. I'm accessing databases outside my local instance through Linked Servers.

My problem is: this SQL will run at many customer sites and the database names will be different at each.

So to refer to a table in another instance I of course have to use <LinkedServerName>.<dbname>.<owner>.<object>.

Problem: the <dbname> will be different depending on the customer site. So hardwiring the <dbname> into my scripts is a pretty major problem.

I want to avoid dynamically generating the SQL and executing through sp_executesql.

Is it possible to define a Linked Server in such a way that I can skip over the database name (refered to as "catalog" in the definition). I have a one to one mapping between databases and Linked Servers, so per Linked Server I can assume access to one and only one database.

I'm using SS2000 so synonyms are out of the picture.

In a nutshell: I need to write sciprts that can easily adapt to different database names in all the distributed queries I'm writing. Is it possible to do this in SS2000?

Please help!

Best Regards,

Mike

Use the OPENROWSET function (see BOL topic OPENROWSET (Transact-SQL)).

This will allow you to specify a connection provider (oledb maybe) and connection string, then a query to run on the target server/db.

EXAMPLE:

SELECT a.*

FROM OPENROWSET(<provider name>, <connection string>,

<query>) AS a;

So you could run the same query on different server and different DBs just by adjusting the connection string.