Risks of single MSSQL domain account for mult servers?

Greetings:
I am trying to conceive what risks might be created by running
multiple SQL servers within a domain under a single domain account, as
opposed to 1) running under the local service account or 2) multiple
domain service accounts.
In this case, all the SQL servers are SQL2000 running on Win2003. The
service account is assigned only to the "Domain Users" group.
We do use linked server calls, and I have played and suceeded getting
Kereberos up to avoid double hop issues when using Windows Auth. In
fact, this is one of the reasons that sparked the question in my mind
-- in all the MS Kerebos SQL<->SQL examples, the SQL servers run under
a unique service account.
As an aside, most of the servers are "line of business" servers, but
HR runs under a unique server with more sensitive information. I don't
really think that merits a seperate service account, but again, I
could well be missing something.
I mostly looking for food for thought, but concrete examples of
gotchas would be appreciated.
Thanks all.
d.D (or should I call you d?),
One drawback of using a single service account is that a breach of security
on that account means a breach on all of your SQL Servers.
(Yes, it is easier to only have one account to manage. Also, once upon a
time (a long time ago) it made replication easier.)
Russell Fields
"D Barry" <google@.dcbarry.com> wrote in message
news:6d9b9a07.0405201046.548244c2@.posting.google.com...
> Greetings:
> I am trying to conceive what risks might be created by running
> multiple SQL servers within a domain under a single domain account, as
> opposed to 1) running under the local service account or 2) multiple
> domain service accounts.
> In this case, all the SQL servers are SQL2000 running on Win2003. The
> service account is assigned only to the "Domain Users" group.
> We do use linked server calls, and I have played and suceeded getting
> Kereberos up to avoid double hop issues when using Windows Auth. In
> fact, this is one of the reasons that sparked the question in my mind
> -- in all the MS Kerebos SQL<->SQL examples, the SQL servers run under
> a unique service account.
>
> As an aside, most of the servers are "line of business" servers, but
> HR runs under a unique server with more sensitive information. I don't
> really think that merits a seperate service account, but again, I
> could well be missing something.
>
> I mostly looking for food for thought, but concrete examples of
> gotchas would be appreciated.
> Thanks all.
> d.|||Russell:
It's "d.". "D." is just too pompous... ;-)
I should have stated the breach against one is a breach of all
arugument. (We do use nice long complex passwords.) I'm looking for
other
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message news:<ubmx4TqPEHA.2976@.TK2MSFTNGP10.phx.gbl>...
> D (or should I call you d?),
> One drawback of using a single service account is that a breach of security
> on that account means a breach on all of your SQL Servers.
> (Yes, it is easier to only have one account to manage. Also, once upon a
> time (a long time ago) it made replication easier.)
> Russell Fields
> "D Barry" <google@.dcbarry.com> wrote in message
> news:6d9b9a07.0405201046.548244c2@.posting.google.com...
> > Greetings:
> >
> > I am trying to conceive what risks might be created by running
> > multiple SQL servers within a domain under a single domain account, as
> > opposed to 1) running under the local service account or 2) multiple
> > domain service accounts.
> >
<snip>
> > Thanks all.
> >
> > d.sql

Rights needed for Surface Area Configuration tool?

Greetings.

Can a non-local admin run the Surface Area Configuration tool? Our dba wants to be able to use the tool, but is not a local admin on the SQL Server. Would we be able to assign a minimal set of rights to him without giving him full local admin?

Thanks.

Dale.

The SAC tool pretty much assumes that a local admin is setting up the configuration of the system. It uses WMI to access the services and it some cases needs to create a connection to the server within the contenct of the SQL Admin. If there is a standard setup you need for your SQL instances, you might consider having an administrator setup a configuration file and run it across your servers using the SAC command line utility.

Thank you,

Bill Ramos, Lead PM, SQL Manageability

returning the number of rows from TOP PERCENT

Greetings.

I want to write a sql query that returns the number of rows from a query that uses top percent. For example,

select top (7) percent object from objecttable

It appears that @.@.rowcount will give the info but it does so as a separate column on each returned row.

Any suggestions would be appreciated.

Thanks.

alan

Try this

select top (7) percent object , count(1) over (partition by 1)

from objecttable order by somecolumn

You are using the new window aggregate ability.

Remember you should always use an order by with TOP

|||

Thanks for the quick reply.

I tried

select top (7) percent objectid , count(1) over (partition by 1)

from bases order by state

and got

(objectid) (no column name)

16 85
19 85
23 85
85 85
38 85
34 85

I'm not sure what '85' represents. When I select and use @.@.rowcount I get '6' in each row. All I really want to do is return '6'. I figure I missed something.

alan

|||@.@.ROWCOUNT gives the number of rows affected by the last statement. So when you use it in the SELECT list you are returning the rows that were affected by the statement before the SELECT. It is not the same as the rows that will be returned by the query. Best is to issue another query "SELECT @.@.ROWCOUNT as rows" after the query or count on the client-side. This will provide the best performance. You could use COUNT with OVER clause but note that has performance implications due to additional computation, sorting etc.|||

This will return the single value 6 (Given your table holds 85 rows)

WITH t1 AS
(SELECT TOP(7) PERCENT * FROM test)
SELECT COUNT(*) FROM t1;

Or this one:

SELECT COUNT(*)
FROM (SELECT TOP(7) PERCENT * FROM test) as t1;

The Execution Plan for Both Queries is the same.

|||Works great. Thanks !|||Works for me. Cool ! Thanks.|||

Or just

select count(1) *7/100

from mytable

|||

Sorry, but the result is not the same (Given the table test holds 85 rows):

SELECT COUNT(1) *7/100 FROM test;

Returns 5

SELECT COUNT(*)
FROM (SELECT TOP(7) PERCENT * FROM test) as t1;

Returns 6

To do it like your hint, the formula must be

SELECT CAST(ROUND(COUNT(1) * 7./100, 1) AS INT) FROM test;

Which returns 6.

There's no performance difference, but i'll prefer the solution which is the easiest to understand.

|||

The answers are different because the TOP will return whole rows i.e. rounds up.

So the answer is something like 5.3