Database Security...

[JaedenRuiner]

Well,

I'm working to start making my application "live" as we are starting within the next week to use the app around the office, but I need to take a second run at setting up the permissions and such, and frankly the roles are a little...vague. I understand what the help says for all of these

• db_accessadmin
• db_backupoperator
• db_datareader
• db_datawriter
• db_ddladmin
• db_denydatareader
• db_denydatawriter
• db_owner
• db_securityadmin

but i don't know how that translates to what I want to happen, and I don't know if that means I have to affect security of individual tables or not.

Basically this is my setup and desire:

There is windows/user authentication and windows authentication pretty much bases its security off of your domain wide user account, so MYDOMAIN\UserName becomes the "User" for that account so it's still is user based in effect. Given that, whether I affect a schema, role, or individual users, this is what I want:

6 base tables - These My users need to be able to Read/Write (SELECT, INSERT, UPDATE, DELETE) I believe that db_datareader and db_datawriter. But they cannot DROP, and i dont' know if that is covered in the database user roles.
As well, I do need them to be able to create and drop other tables that are added on the fly. These are temporary tables, though not in the database terminology of a "temp table" they are just regular tables that are added used and dropped. I just want to prevent the dropping of my 6 base tables.

now, in Managment studio, you can go to the database and its properties which has a permissions section, where you can assign permissions to users and or roles, and that seems somewhat based on the user role with additional permissions. Like my account (user account and win auth account, i have both for both styles of authorization) is flagged db_owner and in the permission it has the effective permission for everything, which is fine.

But how would i be able to protect certain tables while making sure they have all the permissions they need. I would expect I could define a "role" to do this but i'm missing a little definition, and funny enough, when you go to help they don't have the links (for easy access) to what each role/permission means. Some I can infer, but others not so much.

A few pointers is all i really need and I can go from there.

Thanks

 

[wakh]

Hi mate,

I don't know how much I will be able to help you, because the roles of SQL Server 2005 are vague to me too, doesn't make sense to me compared to the ones available in SQL Server 2000. But regarding your scenario there is a role that permits creation, deletion and modification of tables which you need to use in your case if you want to give users the DROP permission, in short there are roles that gives you the permissions you are looking for. I also have recently started playing with SQL Server 2005, so can't tell you which roles you need to use, search out the description of those roles and you'll find it for sure.

I am sure you understand my position buddy.

Regards,
wakh