SQL Server model...

[Parasu]

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

 

[Bernadette]

Hi There

You can add your clients to the following roles in the database.

db_reader (can read records in the database tables - SELECT)
db_writer (can update and delete records in the database
tables - UPDATE, INSERT, DELETE)
db_ddladmin (can create and delete objects in the databases)

If you want to revoke /add any more specific permissions.
1. Open up Enterprise Manager, Go to Database, Users
2. Right-Click on the User
3. Select Properties
4. Click [Permissions]
5. Set and Revoke Select/Update/Delete/Exec permissions
from there for each object in the database
Hope This Helps
Bernadette

 

[foxdev]

One other consideration is whether you want to allow them to run various system stored procedures. Robert Bradley

http://www.lowcostprints.com

http://www.foxdev.com

 

[Parasu]

Hi Bernadette and foxdev,
Thanx alot for those advises. I'll work on it.
Someone told me that, I can make the user as dbo of his database. Is this Ok or does this have some implications.

Cheers
Parasuraman

 

[Bernadette]

Hi Parasu

If you add someone to the db_owner role you automatically give the user complete control over the database. Members of this role can modify database settings, change object permissions, perform database backups, perform any administration task on the database.

Be careful of giving your users more permissions than they need.
Check out "Database Roles" in "Books On-Line". It will explain what each of the roles do

Hope this helps Bernadette

 

[foxdev]

Bernadette is correct, and in your situation, it may well be appropriate to give the customer complete control over their database - just don't let them create new ones.

One of my sites is hosted on NT by a company that includes SQL Server. IIRC, they did give me the DBO permission. Robert Bradley

http://www.lowcostprints.com

http://www.foxdev.com

 

[Parasu]

Hi guys,
Thanx again. I dont understand one thing. If I make the user as a db_owner, then, he can increase the size of the database and other things which he is not supposed to do.

Foxdev, Regarding IIRC, you were the dbo. So, u should have got the rights to increase the database size and other things. What exactly was the roles that were given to u in ur IIRC scenario
Cheers and thanx for ur wonderful replies, guys

Parasu