SQL Server model...

[Parasuraman]

Hi all,
Im a newbie SQL DBA. I need the help of you guys. Here is my problem.
My company is an Application Service Provider (ASP) which will also do some database hosting. The SQL database will reside in my data center. We'll create a database and a user for each client who registers for database hosting. I'll be giving the connection details for my client to connect to my sql server thru EM. Im really worried about the databases when I expose my server thru EM.
Can anyone say what will be the right security options to be followed in my scenario. Remember that, each client can view, change and delete objects in his database only. He should not change database size, should not create new db etc.
Pls help

[MAK]

If the client is not going to create Objects - Give them Datareader and datawriter access.
If they are gonna create objects, make them a DBO for that database.
If u r so scared, Given them individual Object access. It will be tedious.
or USE "Grant " for giving access from SQL.

-MAK


------------
Parasuraman at 4/27/01 5:59:42 AM

Hi all,
Im a newbie SQL DBA. I need the help of you guys. Here is my problem.
My company is an Application Service Provider (ASP) which will also do some database hosting. The SQL database will reside in my data center. We'll create a database and a user for each client who registers for database hosting. I'll be giving the connection details for my client to connect to my sql server thru EM. Im really worried about the databases when I expose my server thru EM.
Can anyone say what will be the right security options to be followed in my scenario. Remember that, each client can view, change and delete objects in his database only. He should not change database size, should not create new db etc.
Pls help

[Me]

Please don't take this personally, but you're in over your head.

First thing I would do is make sure the SA password isn't still blank, and change it if it is. I'd also get rid of xp_cmdshell. Finally, I'd call in a consultant. His/Her cost will be small compared to the potential liability you face when an unauthorized person accesses or modifies your clients' data.

------------
Parasuraman at 4/27/01 5:59:42 AM

Hi all,
Im a newbie SQL DBA. I need the help of you guys. Here is my problem.
My company is an Application Service Provider (ASP) which will also do some database hosting. The SQL database will reside in my data center. We'll create a database and a user for each client who registers for database hosting. I'll be giving the connection details for my client to connect to my sql server thru EM. Im really worried about the databases when I expose my server thru EM.
Can anyone say what will be the right security options to be followed in my scenario. Remember that, each client can view, change and delete objects in his database only. He should not change database size, should not create new db etc.
Pls help

[Parasuraman]

Hi Me,
Thanx for that. Ive already changed the sa password to something. And regrading the consultant thing, I think, I can manage. Im not new to SQL Server. Hope things work fine
Cheers and thanx again

[Parasuraman]

------------
MAK at 4/27/01 9:17:48 AM

If the client is not going to create Objects - Give them Datareader and datawriter access.
If they are gonna create objects, make them a DBO for that database.
If u r so scared, Given them individual Object access. It will be tedious.
or USE "Grant " for giving access from SQL.

-MAK
---------

Hi Mak,
Thanx for that piece of info. I just had another doubt. If I make the user DBO of his database, he is able to view the Master and Tempdb and other databases objects. Is there any way to avoid this?
Thanx again
Cheers