September 9, 2003 at 7:25 am
hi
Could someone please help me in following:
We have four types of people who use our VB6 program.
1. The Users; 2. The Business Analyses; 3. The Developer; 4. The DBA
We use SQL 2000 as back end database. I need different permissions for all of those four types.
The Users: enable to run the VB6 program and Add/Delete/Update data using VB program. They can have access only in Training and Production database.
The Business Analyses: enable to run the VB6 program and Add/Delete/Update data using VB program. They can have access only in Test, Training, Prototype and Production databases.
The Developers: enable to run the VB6 program and Add/Delete/Update data using VB program and can Add/Delete/Update data direct in SQL 2000 but can’t make any structure changes such as Add/Delete/Update table/view/stored procedure. They can have access only in Development, Test, Training, Prototype and Production databases.
The DBA: enable to run the VB6 program and Add/Delete/Update data using VB program and can do all database administrative duties in SQL 2000. They have access to all databases with all access right.
September 9, 2003 at 7:54 am
Given your situation, I would set up NT Groups on the server that your SQL Server is on versus individual window logins. Then set up the four seperate groups with the appropriate permissions in SQL Server. From then on, you add the user to the appropriate group and they are set to go.
Dave
September 9, 2003 at 8:14 am
How do you insert/delete/update data from VB programs, via stored procedures or hardcode insert/delete/update statements?
If you use sps, the login doesn't have to have insert/delete/update to the tables, you only need to grant execution permission on these sps to the login. If you hardcode the insert/delete/update in your VB programs, the login does have to have datawriter role.
In terms database accessing rights, your users and business analysts do not seems to have different roles except the server access.
For your developers, you can revoke 'create table, create view, create procedure' permissions to them.
In addition to DAVNovak suggestion, you could also consider to use database and application roles.
September 9, 2003 at 8:20 am
Thanks Dave,
I did set up the four groups.
I set their roles as:
The user- Role=Public and tick databases as above
The BAs-Role=Public, tick databases as above
The Developer-Role=db_datawriter and db_datareader, tick database as above
But some reason the Developers and the BAs lost their rights to access the databases where they should be able to have access.
Allen, we use stored procedures.
September 15, 2003 at 2:09 pm
Khalidhussain,
I do not know why the BA's lost there access, when they should be the same as the Users in permission. The only difference that I could see from your descriptions is that the BA's have access to additional databases.
As to the developers, I would suggest setting them up as DBO's in the databases, but revoke the database permissions to create table, view, stored procedures, defaults, and rules (like Allen suggested). You can get to these permissions by right clicking on database name in Enterprise Manager and select properties. The permissions are the last tab in the properties box.
I hope this helps you out.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply