Database security

Hi,
I'm trying to implement some security on our more sensitive tables in a database.
The database is used by all for read/write via Web pages (IIS).
Is there any way to restrict users from accessing a table other than from a specific application (i.e. IIS or Crystal Reports)?
Am I looking in the wrong direction?

Thanks
MottyYes, you can do that by implementing application security(application role).
For more details see "application roles" in BOL.

Originally posted by mseal1
Hi,
I'm trying to implement some security on our more sensitive tables in a database.
The database is used by all for read/write via Web pages (IIS).
Is there any way to restrict users from accessing a table other than from a specific application (i.e. IIS or Crystal Reports)?
Am I looking in the wrong direction?

Thanks
Motty|||How is the access to the tables controlled?Thru Stored procedure ,roles?|||I have no control at this time as to how users access the Db.
Security is using NT logons, and domain users can read/write to all tables.
(Hope I don't sound too naive about administrating my database (SQL 7.0)

Thanks
Motty|||What if I have no control over the application that accesses SQL, then I can't run the sp_setapprole to gain access?|||Once the app role in place, you won`t need to keep NT logons , so this it would be the only way to connect to the database for the users. (supposing of course that guest acc. don`t exists in the current DB)

Originally posted by mseal1
What if I have no control over the application that accesses SQL, then I can't run the sp_setapprole to gain access?|||I know I'm sounding a little thick today
I have several applications (off the shelf) such as Crystal reporting, Access, Excel
I want to be able to limit access to a table based on the application name the users are coming from.
If I use Profiler, I have a column called 'Application Name' that identifies the type of application.
Can I use that information? At times I don't have a way to 'send' the sp_setapprole command.

Thanks for all your help!|||No you don't because SQL implements the security based on accounts and roles. The only way to restrict the access is to declare a custom role in your DB for each app., then set the privileges according to your policy, and map your users to these roles.

Originally posted by mseal1
I know I'm sounding a little thick today
I have several applications (off the shelf) such as Crystal reporting, Access, Excel
I want to be able to limit access to a table based on the application name the users are coming from.
If I use Profiler, I have a column called 'Application Name' that identifies the type of application.
Can I use that information? At times I don't have a way to 'send' the sp_setapprole command.

Thanks for all your help!|||Thanks,
I think I have enough to start

Database Permissions

Hello All,
I have a need to set up database security on our QA and Production servers
in the following manner:
IT Managers - Read/write access. Ability to view/start/stop scheduled jobs
not owned by them (all jobs are owned by sa).
Team Leads - Allow them to create/drop/alter stored procedures and functions
only. Otherwise, read-only access to all other objects
Developers - Read-only access to all objects.
For the IT Managers, I have a couple of options. 1) Give dbo permissions,
which will give them everything but the ability to view/start/stop jobs. I
won't give them sysadmin rights.
For the Developers, it's pretty easy. db_datareader permissions,
db_denydatawriter permissions.
For the Team Leads, I have not come up with anything bullet-proof. If I
give db_ddladmin rights, it allows them to modify data regardless of any
explicit deny permissions I put on any objects.
Does anyone have any suggestions?
Thanks,
David Grau
Database Administrator
Surprise & DelightDavid
1. Create ITManagers Group and add it to sysadmin server role.
2. Create TeamLead Group
a) Don't make it a member of sysadmin server role
b) GRANT CREATE TABLE ,CREATE Function ,GRANT CREATE Procedure TO
TeamLead
Take a look at creating Roles in the BOL as well
"David Grau" <DavidGrau@.discussions.microsoft.com> wrote in message
news:D265D1AC-C3D5-408B-8C77-B91696442F9F@.microsoft.com...
> Hello All,
> I have a need to set up database security on our QA and Production servers
> in the following manner:
> IT Managers - Read/write access. Ability to view/start/stop scheduled
> jobs
> not owned by them (all jobs are owned by sa).
> Team Leads - Allow them to create/drop/alter stored procedures and
> functions
> only. Otherwise, read-only access to all other objects
> Developers - Read-only access to all objects.
> For the IT Managers, I have a couple of options. 1) Give dbo permissions,
> which will give them everything but the ability to view/start/stop jobs.
> I
> won't give them sysadmin rights.
> For the Developers, it's pretty easy. db_datareader permissions,
> db_denydatawriter permissions.
> For the Team Leads, I have not come up with anything bullet-proof. If I
> give db_ddladmin rights, it allows them to modify data regardless of any
> explicit deny permissions I put on any objects.
> Does anyone have any suggestions?
> Thanks,
> David Grau
> Database Administrator
> --
> Surprise & Delight|||Thanks for your reply. However, let me add more detail now that I know more
about this request.
The IT Managers want to have SQL Logins that have expanded security beyond
their Windows logins. Is there a way to give them read/write to each
database as well as the ability to start/stop/delete scheduled jobs?
Similarly, Team Leaders want separate SQL Logins that they can use that have
the following: read-only access to the databases; create/drop/alter stored
procedures and functions. No other abilities for the Team Leaders. They
should not be able to create/alter/drop tables or any other objects.
Can all this be accomplished through database roles?
Thanks,
David Grau
--
Surprise & Delight
"Uri Dimant" wrote:

> David
> 1. Create ITManagers Group and add it to sysadmin server role.
> 2. Create TeamLead Group
> a) Don't make it a member of sysadmin server role
> b) GRANT CREATE TABLE ,CREATE Function ,GRANT CREATE Procedure TO
> TeamLead
>
> Take a look at creating Roles in the BOL as well
>
> "David Grau" <DavidGrau@.discussions.microsoft.com> wrote in message
> news:D265D1AC-C3D5-408B-8C77-B91696442F9F@.microsoft.com...
>
>|||David
> The IT Managers want to have SQL Logins that have expanded security beyond
> their Windows logins. Is there a way to give them read/write to each
> database as well as the ability to start/stop/delete scheduled jobs?
Add them to sysadmin server role
"David Grau" <DavidGrau@.discussions.microsoft.com> wrote in message
news:3079CDD8-49BC-4466-9FC2-2CED09181265@.microsoft.com...[vbcol=seagreen]
> Thanks for your reply. However, let me add more detail now that I know
> more
> about this request.
> The IT Managers want to have SQL Logins that have expanded security beyond
> their Windows logins. Is there a way to give them read/write to each
> database as well as the ability to start/stop/delete scheduled jobs?
> Similarly, Team Leaders want separate SQL Logins that they can use that
> have
> the following: read-only access to the databases; create/drop/alter
> stored
> procedures and functions. No other abilities for the Team Leaders. They
> should not be able to create/alter/drop tables or any other objects.
> Can all this be accomplished through database roles?
> Thanks,
> David Grau
> --
> Surprise & Delight
>
> "Uri Dimant" wrote:
>

database performance very bad, any advice?

check cpu, harddisk queue, read & write, memory usage, all very normal.

but one purticular database is very slow( all others are normal). it took 10 seconds to insert one record into the slow database. each record is less than 300 byte, all varchar. now there are 1 million records in the core table.

use DBCC showcontig to check the slow db, found out this:

Extent Scan Fragmentation -- 99.47%

will a "DBCC indexdefrag" help in this case?

Thanks for any help!Possible causes for poor performance:

* Statistics out of date. Use auto update statistics option or UPDATE STATISTICS

* Slow insert, could be due to the indexes needing an update with say a 90% fill factor. Don't use clustered index for data that is always changing.

* Have you got too many indexes?

* Try using data striping using file groups. Eg if you have 2 heavily used tables in a database, performance would be better if they were on separate disks / RAID arrays

* Is your transaction log being truncated (normally happens when tranaction log is backed up). Use DBCC sqlperf(logspace) .

* Is your transaction log file expanding every time you add more data?

* Is your database file expanding every time you add more data?

* Have you tried a DBCC checkdb ?

* Use NT Performance monitor to look at Disk, CPU and Memory activity counters.