Any valid login can access Enterprise Manager

Hi.
When creating a SQL Server2000 login (NT Authen) with read-only rights to
user tables in a user database, this very same login can:
1. Login into EM
2. Though cannot change any objects, but can
- 1. view all system objects (logins, DTS etc)
3. STOP SQL Server Agent
4. RESTART SQL SERVER!!!!
This all seem to be traced back to the fact every login is a member of the
PUBLIC role, and the PUBLIC role allow u to do all of the above!!!
Can anyone tell me how to:
1. Prevent user (not DBA, DBO's etc) login into EM?
2. Prevent user login into QA?
Cheers!> 2. Though cannot change any objects, but can
> - 1. view all system objects (logins, DTS etc)
You can disable the msdb guest user (EXEC msdb..sp_dropuser 'guest') to
prevent access to msdb. This will prevent viewing DTS packages. See
http://support.microsoft.com/defaul...b;en-us;282463.
You can 'REVOKE SELECT FROM syslogins' to prevent non privileged users from
enumerating logins via EM.

> 3. STOP SQL Server Agent
> 4. RESTART SQL SERVER!!!!
The ability to stop and start services is controlled through Windows
permissions, not SQL Server security. If the account is a member of the
Windows 'Administrators' or 'Power Users' groups, then the user can stop and
start services using any tool or command. EM will not allow non-privileged
users to stop/start services.
Hope this helps.
Dan Guzman
SQL Server MVP
"Oddie" <Oddie@.discussions.microsoft.com> wrote in message
news:2DB045CF-4B2F-4493-BEB5-FA684D5800A1@.microsoft.com...
> Hi.
> When creating a SQL Server2000 login (NT Authen) with read-only rights to
> user tables in a user database, this very same login can:
> 1. Login into EM
> 2. Though cannot change any objects, but can
> - 1. view all system objects (logins, DTS etc)
> 3. STOP SQL Server Agent
> 4. RESTART SQL SERVER!!!!
> This all seem to be traced back to the fact every login is a member of the
> PUBLIC role, and the PUBLIC role allow u to do all of the above!!!
> Can anyone tell me how to:
> 1. Prevent user (not DBA, DBO's etc) login into EM?
> 2. Prevent user login into QA?
> Cheers!|||Thks Dan - it sure works - but still no way of preventing a valid SQL Login
to access other objects on EM or seeing them using other tools (such as
Visual Studio).
Thks again!
"Dan Guzman" wrote:

> You can disable the msdb guest user (EXEC msdb..sp_dropuser 'guest') to
> prevent access to msdb. This will prevent viewing DTS packages. See
> http://support.microsoft.com/defaul...b;en-us;282463.
> You can 'REVOKE SELECT FROM syslogins' to prevent non privileged users fro
m
> enumerating logins via EM.
>
> The ability to stop and start services is controlled through Windows
> permissions, not SQL Server security. If the account is a member of the
> Windows 'Administrators' or 'Power Users' groups, then the user can stop a
nd
> start services using any tool or command. EM will not allow non-privilege
d
> users to stop/start services.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Oddie" <Oddie@.discussions.microsoft.com> wrote in message
> news:2DB045CF-4B2F-4493-BEB5-FA684D5800A1@.microsoft.com...
>
>|||By default, SQL 2000 users can read catalog meta data in those databases
they have permissions to access. It's possible to revoke public permissions
from some of the catalog objects but this can break data access API's so
proceed at your own risk. SQL 2005 provides more control over meta data
access.
Hope this helps.
Dan Guzman
SQL Server MVP
"Oddie" <Oddie@.discussions.microsoft.com> wrote in message
news:5D081158-8F2B-428B-ABE2-32892258C3C0@.microsoft.com...[vbcol=seagreen]
> Thks Dan - it sure works - but still no way of preventing a valid SQL
> Login
> to access other objects on EM or seeing them using other tools (such as
> Visual Studio).
> Thks again!
> "Dan Guzman" wrote:
>|||Thks Dan for all your help!
"Dan Guzman" wrote:

> By default, SQL 2000 users can read catalog meta data in those databases
> they have permissions to access. It's possible to revoke public permissio
ns
> from some of the catalog objects but this can break data access API's so
> proceed at your own risk. SQL 2005 provides more control over meta data
> access.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Oddie" <Oddie@.discussions.microsoft.com> wrote in message
> news:5D081158-8F2B-428B-ABE2-32892258C3C0@.microsoft.com...
>
>

Any one who knows this error about SQL 65 upgrade? its urgent, thanks.

Hi All,

I am doing the upgrade from SQL6.5 to SQL2K, the server version is:
SQL 6.5 English SP5 on WinNT Enterprise 4.0 English SP6,
new server is SQL 2K standard English SP1 on WinNT Enterprise 4.0 English SP6.

When I use SQL upgrade wizard to perform a two server upgrade, all steps seems ok, when move to the step of "Export & Import Via Name Pipe" I got a error. Pls see the below error. Pls let me know the reason and how to resolve it.

log file: Export and Import via Named Pipe - 007test.err

############# Log file content ############
Export.exe on the remote machine was hung...

Export Exit Code: 259 - export.exe -CodePage 1252 -DeviceType Pipe -AllTables yes -MasterPath D:\MSSQL\DATA\MASTER.DAT -DatabaseName test -DevicePath \\.\pipe\~cnvpipe0

Import Exit Code: -1 - ~cnvpipe0

Msg 4854, Level 21, State 1, Server NTAPPLIX02, Procedure , Line 1

[Microsoft][ODBC SQL Server Driver][SQL Server]****

Time:12-03-2003 17:05:04.433

Error return from function CreateFile

in file CnvPipe.cpp

Line=142

rc=1326

Msg=Logon failure: unknown user name or bad password.

****
######################################Create/use a domain account for both sql server's service startup account. Make sure to add this domain account to the local admin group. Lastly, login using this domain account to run the upgrade.|||Originally posted by rnealejr
Create/use a domain account for both sql server's service startup account. Make sure to add this domain account to the local admin group. Lastly, login using this domain account to run the upgrade.

Hi,

Thanks, because the SQL65 server does not join any domain just a stand alone server, and i can not join this SQL server to any existing domain, but the SQL2K server is in a NT domain. Any solution for this situation ? One more problem, is the name pipe only connection method when perform a two server SQL upgrade?

Many thanks,

Zhanlan

Any Linux-based apps similar to MS SQL Enterprise w/ DTS ?

Hi all,

I bet this doesn't exist, but does anyone know of an MS SQL Enterprise
Manager-type application for Linux that would give me access to a DTS
stored on the server? I'm a web programmer by profession, and most of
my tools are Linux-based, but because I do so much indepth work in MS
SQL and DTS's, I've been dual-booting between Windows and Linux mainly
because there's no way I know of to access my MS SQL DTS's from Linux.

Any Linux solutions out there that will read and maybe even edit SQL
DTS's?

Thanks,

Alex.Don't know of any linux tools for sql server but here are two alternatives
that may be useful to you:
http://www.vmware.com/ - no need for dual booting if you can run both OSs at
the same time

http://www.microsoft.com/downloads/...&displaylang=en
SQL Server web data admin tool

"Alex" <alex@.totallynerd.com> wrote in message
news:2ba4b4eb.0404150752.6019c780@.posting.google.c om...
> Hi all,
> I bet this doesn't exist, but does anyone know of an MS SQL Enterprise
> Manager-type application for Linux that would give me access to a DTS
> stored on the server? I'm a web programmer by profession, and most of
> my tools are Linux-based, but because I do so much indepth work in MS
> SQL and DTS's, I've been dual-booting between Windows and Linux mainly
> because there's no way I know of to access my MS SQL DTS's from Linux.
> Any Linux solutions out there that will read and maybe even edit SQL
> DTS's?
> Thanks,
> Alex.

Any Good SQL Server Intermediate Training Courses?

Quick question. I am in the NYC area (Westchester) and I am using Cognos DecisionStream to build my SQL Server 2000 Enterprise Data Warehouse.

I want to learn more about complex SQL, specifically for SQL Server.

What do you reccommend? Should I go for strictly an advanced SQL course, or should I go for SQL Server training.

Does anyone know of a really good site where I can find Advanced SQL training?

Also, I want to learn more about scripting and VB. Can anyone reccommend a place where I can get this beginner training.

Sorry to take up your time, but to sum up:

1. Advanced SQL training in NYC area
2. Beginner VB training in NYC area.

Cost is not necessarily an issue.

Thanks!You are asking for recommendations, which mean you want other people's opinions. I've always got at least one of those to spare!

For VB and Microsoft SQL Server, I'd recomment Quilogy (http://www.quilogy.com/). in my opinion, Alan Groh is one of the best teacher/mentor types in the business.

For the really, really interesting SQL, I'd recomment Joe Celko (http://www.celko.com/). Joe was on the committee that defined what SQL ought to do, and he has been a consultant for years teaching and helping folks figure out just what the existing SQL engines really do, especially when that deviates from the standard. If nothing else, he's fun to listen to when he discovers a new "feature" in one of the SQL dialects!

-PatP|||Thank you very much, I will look them up! I really appreciate it!|||Logon to these forums daily and look at what people are asking and try to figure the problems out...

kinda like a pop quiz every day...

Oh and read the solutions...

Do you have access to sql server and client side tools?

What do you consider "complex sql" to be?

Theta joins always give me a moment to pause...|||I consider complex SQL to be anything other than the simple select statement, like sub-queries, and transformation/alterations on fields like date conversions, etc.

Do you suggest any books with good amounts of source code and puzzles, that is up to date?|||Yeah...Celko's SQL for Smarties should blow your mind...it's not really platform specific

Ken Hendersons Book The Guru's Guids to SQL Server, Stored Procedures, XML and HTML is, and it's outstanding...AND comes with a cd loaded with code...

And there's also

http://www.sqlteam.com/store.asp

Any command list the tables in the Databsae

Please help. In my Enterprise Manager, there is some local database, some
other (is in another network)
IN SQL Analyzer , what command I can list out the tablelist for each
database'
ThanksSee:
http://groups-beta.google.com/group...1d?dmode=source
Anith|||check for stored procedure sp_tables in BOL
or u can make use of sysobjects table available in Master Database
best Regards,
Chandra
http://groups.msn.com/SQLResource/
http://chanduas.blogspot.com/
---
*** Sent via Developersdex http://www.examnotes.net ***|||You can use an undocumented sp (not recommended in production dbs) or declar
e
a cursor to traverse databases and execute stored procedure sp_tables in eac
h
db context.
use northwind
go
exec sp_msforeachdb 'select ''?''; use [?]; exec sp_tables @.table_type =
'''table''
go
declare @.sql nvarchar(4000)
declare @.db sysname
declare dbs_cursor cursor local fast_forward
for
select
catalog_name
from
information_schema.schemata
open dbs_cursor
while 1 = 1
begin
fetch next from dbs_cursor into @.db
if @.@.error != 0 or @.@.fetch_status != 0 break
select @.db
set @.sql = N'use ' + quotename(@.db) + N' exec sp_tables @.table_type =
'''table''
print @.sql
exec sp_executesql @.sql
end
close dbs_cursor
deallocate dbs_cursor
go
AMB
"Agnes" wrote:

> Please help. In my Enterprise Manager, there is some local database, some
> other (is in another network)
> IN SQL Analyzer , what command I can list out the tablelist for each
> database'
> Thanks
>
>

Any better monitoring for locks and blocking in SQL2005?

Are the facilities for monitoring locks and blocking in SQL2005 better than
2000?
I found that in SQL2000 Enterprise Manager by the time you waited for
"current activity" to refresh, the information could have been and gone.
I'd be interested to hear if you've been using it sucessfully to do this
sort of thing.
If you want something similar to Current Activity, Activity
Monitor in Management Studio is an improvement over the
Current Activity node in Enterprise Manager. Opens in a
separate window, you have a few different view to chose
from, you can set a refresh interval.
2005 also has server level reports such as Blocking
Transactions and Top Transactions by Locks.
But just like Current Activity, you are seeing a snapshot
and you need to refresh. You have more options in 2000 to
understand the blocking, locking if you use queries to
monitor activity and it's somewhat the same in 2005.
However, there are improvements to the monitoring and
details you can get related to locking, blocking. In 2005,
you have a lot more details exposed on the waits. You can
also set up a blocked process threshold and use this with
the Blocked Process Report event class in Profiler, SQL
trace. You can also get detailed historical information
related to locking and blocking from the DMV
Sys.dm_db_index_operational_stats.
The following article has a lot of information on you can
use to monitor blocking, locking issues in 2005:
Troubleshooting Performance Problems in SQL Server 2005
http://www.microsoft.com/technet/pro.../tsprfprb.mspx
-Sue
On Thu, 19 Jan 2006 04:50:02 -0800, "James"
<James@.discussions.microsoft.com> wrote:

>Are the facilities for monitoring locks and blocking in SQL2005 better than
>2000?
>I found that in SQL2000 Enterprise Manager by the time you waited for
>"current activity" to refresh, the information could have been and gone.
>I'd be interested to hear if you've been using it sucessfully to do this
>sort of thing.
|||Night and day, across the board. SQL Server 2000 exposed very limited
information to be able to monitor. SQL Server 2005 has layer after layer
after layer of diagnostics. From DBCC commans through DMVs/DMFs, there
aren't many areas of the engine you can't get at.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"James" <James@.discussions.microsoft.com> wrote in message
news:98B66E56-8895-4040-BA63-056144C882C6@.microsoft.com...
> Are the facilities for monitoring locks and blocking in SQL2005 better
> than
> 2000?
> I found that in SQL2000 Enterprise Manager by the time you waited for
> "current activity" to refresh, the information could have been and gone.
> I'd be interested to hear if you've been using it sucessfully to do this
> sort of thing.

Anti-Virus on SQL Configuration

Hi:
We have Symantec Enterprise Anti-Virus 10.0 but it is not currently
installed on our new SQL box. I would like to know what files and folders t
o
exclude from scanning before I install. I have search Symantec knowledgebas
e
but no answers. Does MS have a page on this like they do for Exchange or is
it pretty much the same as Exchange?
Thanks,http://support.microsoft.com/?kbid=309422
http://www.sqlservercentral.com/col...rusprograms.asp
Andrew J. Kelly SQL MVP
"Cindy" <Cindy@.discussions.microsoft.com> wrote in message
news:1C577C85-E5D4-48B0-BBE1-B363C06C706B@.microsoft.com...
> Hi:
> We have Symantec Enterprise Anti-Virus 10.0 but it is not currently
> installed on our new SQL box. I would like to know what files and folders
> to
> exclude from scanning before I install. I have search Symantec
> knowledgebase
> but no answers. Does MS have a page on this like they do for Exchange or
> is
> it pretty much the same as Exchange?
> Thanks,|||Thanks Andrew. It would be nice if MS had step by step like with Exchange.
Symantec has nothing but the other article you pointed me to helps.
Cindy
"Andrew J. Kelly" wrote:

> http://support.microsoft.com/?kbid=309422
>
> http://www.sqlservercentral.com/col...rusprograms.asp
>
>
> --
> Andrew J. Kelly SQL MVP
>
> "Cindy" <Cindy@.discussions.microsoft.com> wrote in message
> news:1C577C85-E5D4-48B0-BBE1-B363C06C706B@.microsoft.com...
>
>