How do I Hide Databases from External Users?

I am going to give user rights for an external user to connect to my SQL Server via Client Network Utility.

Atlough I have given user permissions to only access one database and not the whole list, how do I make sure that they cannot see all the other databases on my SQL Server?

I have 20 instances of databases on my SQL Server and ideally I would like to give 20 different people access - but each of them when they enter my SQL Server, should not even know that the other databases exist.

Thanks.RE:
Q1 I am going to give user rights for an external user to connect to my SQL Server via Client Network Utility.

Q2 Although I have given user permissions to only access one database and not the whole list, how do I make sure that they cannot see all the other databases on my SQL Server?

I have 20 instances of databases on my SQL Server and ideally I would like to give 20 different people access -

Q3 but each of them when they enter my SQL Server, should not even know that the other databases exist. Thanks.

A1 {What was meant is not 100% clear; however, bestowing "[user rights for an external user to connect to SQL Server]" would generally be accomplished within Enterprise Manager, *Query Analyser, osql, isql, or programmatically, i.e.(via an API: ADO, CLib, DMO, OLEDB, etc.,); but not via the Client Network Utility (at least not directly)?}

A2 One may meet such requirements by designing the user application(s), i.e.(the ones the users will use with their respective DBs), such that other DB catalogs are not displayed. (This is arguably probably one of the better means of meeting such a requirement.)

Note: Various "all encompassing" implementation approaches may entail significant negative consequences. For example this includes many implementations that:
a Deny users Select and Exec rights to certain objects in the Master DB.
b Modify / add objects to the Master DB (this option especially, is NOT recommended).
c Require special connectivity software that supports the desired feature set (custom made ODBC, Sql Server, OLEDB, etc., drivers).

A3 You may wish to verify that this is in fact an important requirement. (That is, one that is worth the ramifications of the means chosen to implement a design that will meet the requirement.) As noted in A2, maintainability and / or functionality may be impaired with some kinds of "DB hiding" implementation approaches.

--

* The following Special Stored Procedures are supported in MS Sql Server 2k (execute from QA, etc.) for managing security:
sp_addalias
sp_addapprole
sp_addgroup
sp_addlinkedsrvlogin
sp_addlogin
sp_addremotelogin
sp_addrole
sp_addrolemember
sp_addserver
sp_addsrvrolemember
sp_adduser
sp_approlepassword
sp_change_users_login
sp_changedbowner
sp_changegroup
sp_changeobjectowner
sp_dbfixedrolepermission
sp_defaultdb
sp_defaultlanguage
sp_denylogin
sp_dropalias
sp_dropapprole
sp_dropgroup
sp_droplinkedsrvlogin
sp_droplogin
sp_dropremotelogin
sp_droprole
sp_droprolemember
sp_dropserver
sp_dropsrvrolemember
sp_dropuser
sp_grantdbaccess
sp_grantlogin
sp_helpdbfixedrole
sp_helpgroup
sp_helplinkedsrvlogin
sp_helplogins
sp_helpntgroup
sp_helpremotelogin
sp_helprole
sp_helprolemember
sp_helprotect
sp_helpsrvrole
sp_helpsrvrolemember
sp_helpuser
sp_password
sp_remoteoption
sp_revokedbaccess
sp_revokelogin
sp_setapprole
sp_srvrolepermission
sp_validatelogins|||With what tools will your users be connecting to your Server? ODBC, OSQL, Enterprise Manager?|||Originally posted by Paul Young
With what tools will your users be connecting to your Server? ODBC, OSQL, Enterprise Manager?

Thank you for your posts :)

I am using EM to connect|||Great, what will your users use to connect?

If your users will use EM or Query Analyzer then they have enough to see the diffrent DBs with little to no effort. A knowlegable user can even see system tables via I/OSQL. Past that, in ODBC a use will still be able to get a list of DB's if they specify a database when setting up the DSN.sql

How Do I give EXECUTE Permissions on Stored Procedures?

Hey guys,

I'm pretty new to SQL configuration, and I need to give EXECUTE
persmissions for one of the SQL user roles. I am running SQL 2005
Management Studio Express - free version. I found the list of my
stored procedures, but I can not locate any permissions screen. Can
someone help point me in the right direction? Thanks!alvinstraight38@.hotmail.com wrote:

Quote:

Originally Posted by

Hey guys,
>
I'm pretty new to SQL configuration, and I need to give EXECUTE
persmissions for one of the SQL user roles. I am running SQL 2005
Management Studio Express - free version. I found the list of my
stored procedures, but I can not locate any permissions screen. Can
someone help point me in the right direction? Thanks!
>

if you right click on the SP and click properties, that should bring up
the options.

--sharif|||alvinstraight38@.hotmail.com (alvinstraight38@.hotmail.com) writes:

Quote:

Originally Posted by

I'm pretty new to SQL configuration, and I need to give EXECUTE
persmissions for one of the SQL user roles. I am running SQL 2005
Management Studio Express - free version. I found the list of my
stored procedures, but I can not locate any permissions screen. Can
someone help point me in the right direction? Thanks!

If you want to use the GUI, make sure that you have SP2. I think that
alternative was missing in RTM and SP1.

Then again, in the long run you are better of using GRANT commands.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Sep 20, 4:11 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

alvinstraigh...@.hotmail.com (alvinstraigh...@.hotmail.com) writes:

Quote:

Originally Posted by

I'm pretty new to SQL configuration, and I need to give EXECUTE
persmissions for one of the SQL user roles. I am running SQL 2005
Management Studio Express - free version. I found the list of my
stored procedures, but I can not locate any permissions screen. Can
someone help point me in the right direction? Thanks!

>
If you want to use the GUI, make sure that you have SP2. I think that
alternative was missing in RTM and SP1.
>
Then again, in the long run you are better of using GRANT commands.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Ahh, I see now why I was lost. I right click on the SP, and there is
no option for Properties. Yet, I can set permissions on tables. How
stupid. You mention SP2. How can I tell which service pack I am
running? I went to Help - About and it shows:

Microsoft SQL Server Management Studio Express Version 9.00.2047.00|||alvinstraight38@.hotmail.com (alvinstraight38@.hotmail.com) writes:

Quote:

Originally Posted by

Ahh, I see now why I was lost. I right click on the SP, and there is
no option for Properties. Yet, I can set permissions on tables. How
stupid. You mention SP2. How can I tell which service pack I am
running? I went to Help - About and it shows:
>
Microsoft SQL Server Management Studio Express Version 9.00.2047.00

That's SP1.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Sep 20, 10:26 pm, "alvinstraigh...@.hotmail.com"
<alvinstraigh...@.hotmail.comwrote:

Quote:

Originally Posted by

On Sep 20, 4:11 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:
>
>
>
>
>

Quote:

Originally Posted by

alvinstraigh...@.hotmail.com (alvinstraigh...@.hotmail.com) writes:

Quote:

Originally Posted by

I'm pretty new to SQL configuration, and I need to give EXECUTE
persmissions for one of the SQL user roles. I am running SQL 2005
Management Studio Express - free version. I found the list of my
stored procedures, but I can not locate any permissions screen. Can
someone help point me in the right direction? Thanks!

>

Quote:

Originally Posted by

If you want to use the GUI, make sure that you have SP2. I think that
alternative was missing in RTM and SP1.

>

Quote:

Originally Posted by

Then again, in the long run you are better of using GRANT commands.

>

Quote:

Originally Posted by

--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se

>

Quote:

Originally Posted by

Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

>
Ahh, I see now why I was lost. I right click on the SP, and there is
no option for Properties. Yet, I can set permissions on tables. How
stupid. You mention SP2. How can I tell which service pack I am
running? I went to Help - About and it shows:
>
Microsoft SQL Server Management Studio Express Version 9.00.2047.00- Hide quoted text -
>
- Show quoted text -

You can grant permissions dynamically in this way to all db objects:
/* tables and views*/
select 'Grant select,insert,update,delete on '+name+ ' to USER'
from sysobjects
where xtype in ('U','V')

/*Stored procedures*/
select 'Grant exec on '+name+ ' to USER'
from sysobjects
where xtype in ('P')|||abu hisham wrote:

Quote:

Originally Posted by

You can grant permissions dynamically in this way to all db objects:
/* tables and views*/
select 'Grant select,insert,update,delete on '+name+ ' to USER'
from sysobjects
where xtype in ('U','V')
>
/*Stored procedures*/
select 'Grant exec on '+name+ ' to USER'
from sysobjects
where xtype in ('P')

To clarify, this will not directly grant the permissions, but will
output SQL code that can be copy+pasted into Query Analyzer and
executed to grant the permissions.|||abu hisham (yjogee@.hotmail.co.uk) writes:

Quote:

Originally Posted by

You can grant permissions dynamically in this way to all db objects:
/* tables and views*/
select 'Grant select,insert,update,delete on '+name+ ' to USER'
from sysobjects
where xtype in ('U','V')
>
/*Stored procedures*/
select 'Grant exec on '+name+ ' to USER'
from sysobjects
where xtype in ('P')

In SQL 2005 this can be achieved with a single statement:

GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE
ON SCHEMA::schema_name TO user

Access granted on schema level are inherited by objects in the schema, which
means that it also applies to future objects.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx