Dotnetpanel Forums

Another problem is when a user goes in to "RESTORE" a database.  By default SMS browses the SQL Server Machine and thus the user has the ability to view ALL files and the directory structure of the C:/ drive.

any idea how to solve this? beside total disable remote SSMS login and using MyLittleTools, thanks in advance 

Yep, myLittleAdmin is a great tool. We are authorized myLittleTools partners and all DNP customers can purchase myLittleAdmin with 25% discount

rockinthesixstring:

Awesome, how do we buy it with this discount?

Just send an email at sales (at) mylittletools.net telling you'd like to receive DNP special offer. We will then send you a 25% discount coupon code.

Best regards

Hi Elian,

Welcome to DNP forums!

Guys, use this chance to ask your questions directly to myLittleTools founder!

directhostinguk:

Anyone know if theres a script that stops Database users seeing other Databases.

I know theres one for SQL 2000 is there one for SQL 2005/Express

Hi,

In case someone needs this for SQL Server 2008 just: DENY VIEW ANY DATABASE TO public; (fixed-server role)

http://msdn.microsoft.com/en-us/library/ms189077.aspx

Should work for SQL Server 2005 as well.

Some other tips:

Auto Close is not that good as it seems and depends on your situation. http://itknowledgeexchange.techtarget.com/sql-server/auto-close-is-almost-as-bad-as-auto-grow/

Auto Grow - same story. http://itknowledgeexchange.techtarget.com/sql-server/autogrow-is-the-bane-of-my-existence/

Other tips are more than welcome!

 Hello,

how exactly

prostoemailee:

DENY VIEW ANY DATABASE FROM public; (fixed-server role)

is working? User connects with SQL Server Management Studio and see only his databases?

Are there any downsides of this solution?

GrZeCh:

is working?

Yes, it is.

GrZeCh:

User connects with SQL Server Management Studio and see only his databases?

Yes, login sees only databases he is mapped to. + system dbs.

GrZeCh:

Are there any downsides of this solution?

Not found so far in my setup.

It is advisable to "DENY VIEW SERVER STATE TO public;" and "DISABLE TRIGGER ALL ON all server;" for security reasons as well.

http://technet.microsoft.com/en-us/library/cc966485.aspx

Does this solution allows to have more than one user added to database?

GrZeCh:

Does this solution allows to have more than one user added to database?

When you say "user", do you mean Login or database User? Couse there is a big difference.

If Login - then yes. If db User - then you can create them via statements or Management Studio, as DNP does not have an ability to create db Users without Logins.

If you find this confusing please find more info here: http://msdn.microsoft.com/en-us/library/ms191465.aspx

Running:

"DENY VIEW ANY DATABASE FROM public;"

returns error Incorrect syntax near 'from'.

so it probably should be:

"DENY VIEW ANY DATABASE TO PUBLIC;"

but after running this command there are no database in SQL Server Management Studio DB list so this is NOT working (SQL Server 2008). I'm just letting know to others who was curious about this.

GrZeCh:

but after running this command there are no database in SQL Server Management Studio DB list so this is NOT working (SQL Server 2008).

Most likely your have denied CONNECT to guest to master, msdb, tempdb.

To see mapped DBs in Management Studio check if guest has CONNECT allowed to the databases mentioned above.

 @ mitodna wrote at Wed, May 9 2007 4:59 AM

It's something you will have to change with the sp_MSdbuseraccess stored procedure, I found script for MSSQL 2005. If You are experiencing a slow response from SQL Server Enterprise Manager when many databases exist in an instance of SQL Server, then go through following article.

http://support.microsoft.com/kb/889696/en-us

The above article accomplished this in SQL 2000. In MSSQL 2005 you cann't run script for all users, but you can apply settings for single database using using following steps,