| Author |
Topic |
|
sridharkamala
Starting Member
15 Posts |
 Posted - 2004-09-22 : 10:28:45
|
| All, I have a third party Application accessing database using dbo privilege. I want to restrict it to Create tables, view and indexes, drop tables, view and indexes, insert rows, and connect to database. And these privileges depend on RDBMS. Can i give this application any fixed server role to restrict access?Thanks |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-09-22 : 13:16:47
|
| In order for them to create dbo objects, they need db_owner database role.Tara |
 |
|
|
sridharkamala
Starting Member
15 Posts |
Posted - 2004-09-22 : 13:23:21
|
| Tara, Thanks for your reply. But my under standing is that, if i give db_owner Database fixed role....they would have full access to the database. They can perform any dbo would do. But i need to stop application gaining control over the database by restricting privileges limited to Create table, view, index and drop the same andconnect to database. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-09-22 : 13:26:26
|
| Why does an application need to create objects? This should be done in development.Tara |
|
|
|
sridharkamala
Starting Member
15 Posts |
Posted - 2004-09-22 : 13:31:28
|
| Yes! but when development is done by Development team. They send it accross to Third Part Application DBA sitting other corner of our environment. While this guy creates objects through Application process. They been having dbo since long time and now that my management realized that dbo is a powerful user of database(loss has been already done)we want to restrict them. I know every one suggests to run development efforts from our side, but this is not happening in my case. Because development efforts has to go through application. Hope i am clear. |
|
|
|
sridharkamala
Starting Member
15 Posts |
Posted - 2004-09-22 : 13:32:22
|
| By the Way!! I forgot to mention that this in Development Environment. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-09-22 : 13:34:19
|
| Developers SHOULD have db_owner access to the database. You just need to educate them on what they should not do even if they have permissions to do them.Tara |
|
|
|
sridharkamala
Starting Member
15 Posts |
Posted - 2004-09-22 : 13:38:01
|
| Well!! That's the problem here. They are creating users and killing processess, you understand what i am saying. We been educating them and they have been warned!!! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-09-22 : 13:39:57
|
db_owner does not allow killing of processes though. Your users must have:quote:
KILL permissions default to the members of the sysadmin and processadmin fixed database roles, and are not transferable.
Tara |
|
|
|
sridharkamala
Starting Member
15 Posts |
Posted - 2004-09-22 : 13:50:22
|
| You are right. It needs sa privilege to kill and sessions. They had sa privilege, we gave them dbo for now. But my questions is....for Create, drop, insert and connect...Is dbo appropriate?Or is there any way to over come this issue..... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-09-22 : 13:52:07
|
| Yes dbo is appropriate if they need to create dbo objects. So the question is are they creating objects owned by dbo or by their own userid?Tara |
|
|
|
sridharkamala
Starting Member
15 Posts |
Posted - 2004-09-22 : 14:01:15
|
| These users are not memebers of sysadmin fixed server role but they are member of db_owner fixed database role. When they create objects its their own objects. WE need to restrict that part..... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-09-22 : 14:03:59
|
| But who owns the objects. dbo? Or their own userid?If you login with sa, do you run this:SELECT *FROM Userid.TableNameOr just:SELECT * FROM TableNameIf the second, then the objects are owned by dbo which requires db_owner role in order to create them.Tara |
|
|
|
sridharkamala
Starting Member
15 Posts |
Posted - 2004-09-22 : 14:08:14
|
| Objects that are created by them are under their own userid's |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-09-22 : 14:10:31
|
| Then as sa, you can grant explicit permissions for the functions that they require. You'll use the GRANT statement to do this. Have a look at GRANT in SQL Server Books Online for details.Tara |
|
|
|
sridharkamala
Starting Member
15 Posts |
Posted - 2004-09-22 : 14:15:41
|
| Can i use GRANT when giving permission on DATABASE? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-09-22 : 14:17:45
|
| I don't understand the question.Tara |
|
|
|
sridharkamala
Starting Member
15 Posts |
Posted - 2004-09-22 : 15:47:30
|
| Tara, Sorry. Let me ask you straight. I will give db_owner to all developers like you said.I don't want them to perform these actions....ALTER DATABASE, BACKUP, DBCC, EXECUTE any system sp's, GRANT, SETUSER, UPDATE STATISTICS...... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-09-22 : 15:51:23
|
| That's not possible. If they don't need db_owner role to create dbo objects like in your case, then just grant them the permissions that they need using GRANT.Tara |
|
|
|
sridharkamala
Starting Member
15 Posts |
Posted - 2004-09-22 : 16:07:59
|
| Well! That is the last thing i would do giving them Object Privs.Its not easy i guess in my case( In Terms of Management)Thanks for your help Tara |
|
|
|
sridharkamala
Starting Member
15 Posts |
Posted - 2004-09-23 : 16:04:17
|
| Tara, I have Questions in regards to an upgrade from SQL 6.5 to SQL 2k. Is there any major issues upgrading stored procs? |
|
|
|
Next Page
|
Third Party Application Privileges