SQL Server Security

[Anastasia]

Hello.

We are building an ASP application with a SQL Server 2000 as a backend. I am working on SQL Server Security. The ideal picture of security is: only database administrators have permissions to alter and create database objects and all DML permissions; developers have permissions to create new stored procedures, but do not have permissions to alter any of the stored procedures that were created by dbo; all other access should be through the application roles. My biggest problem is assigning permissions to the developers. How can I modify DDL permissions?

Any helpful literature, online documentation, or personal suggestions are greatly appreciated.

Thank you,
Anastasia

[Ray Miao]

Should setup a r&d box for developers, then you don't have to assign permission on production box to developers.


------------
Anastasia at 5/7/01 12:22:55 PM

Hello.

We are building an ASP application with a SQL Server 2000 as a backend. I am working on SQL Server Security. The ideal picture of security is: only database administrators have permissions to alter and create database objects and all DML permissions; developers have permissions to create new stored procedures, but do not have permissions to alter any of the stored procedures that were created by dbo; all other access should be through the application roles. My biggest problem is assigning permissions to the developers. How can I modify DDL permissions?

Any helpful literature, online documentation, or personal suggestions are greatly appreciated.

Thank you,
Anastasia

[Anastasia]

I was talking about the r&d box. The problem is that I want to know about every change in the development database because I am responsible for upgrading the production box. The only DDL I want allow them is CREATE PROC. In this case I will be able to see who created the proc and recompile it as dbo after review.


------------
Ray Miao at 5/7/01 12:33:51 PM

Should setup a r&d box for developers, then you don't have to assign permission on production box to developers.


------------
Anastasia at 5/7/01 12:22:55 PM

Hello.

We are building an ASP application with a SQL Server 2000 as a backend. I am working on SQL Server Security. The ideal picture of security is: only database administrators have permissions to alter and create database objects and all DML permissions; developers have permissions to create new stored procedures, but do not have permissions to alter any of the stored procedures that were created by dbo; all other access should be through the application roles. My biggest problem is assigning permissions to the developers. How can I modify DDL permissions?

Any helpful literature, online documentation, or personal suggestions are greatly appreciated.

Thank you,
Anastasia

[MAK]

why do u want to give
"create procedure" permission on production BOX?

Can they execute their own procs?

-MAK


------------
Anastasia at 5/7/01 12:51:27 PM

I was talking about the r&d box. The problem is that I want to know about every change in the development database because I am responsible for upgrading the production box. The only DDL I want allow them is CREATE PROC. In this case I will be able to see who created the proc and recompile it as dbo after review.


------------
Ray Miao at 5/7/01 12:33:51 PM

Should setup a r&d box for developers, then you don't have to assign permission on production box to developers.


------------
Anastasia at 5/7/01 12:22:55 PM

Hello.

We are building an ASP application with a SQL Server 2000 as a backend. I am working on SQL Server Security. The ideal picture of security is: only database administrators have permissions to alter and create database objects and all DML permissions; developers have permissions to create new stored procedures, but do not have permissions to alter any of the stored procedures that were created by dbo; all other access should be through the application roles. My biggest problem is assigning permissions to the developers. How can I modify DDL permissions?

Any helpful literature, online documentation, or personal suggestions are greatly appreciated.

Thank you,
Anastasia

[Anastasia]

I don't.
I'm talking about the development box.
I want developers to be able to create new objects for testing. Since they logged in using Windows NT authentication I am able to monitor their activity. But I want to make sure they don't touch any dbo objects on the development database.

Thank you,
Anastasia

------------
MAK at 5/7/01 12:56:53 PM

why do u want to give
"create procedure" permission on production BOX?

Can they execute their own procs?

-MAK


------------
Anastasia at 5/7/01 12:51:27 PM

I was talking about the r&d box. The problem is that I want to know about every change in the development database because I am responsible for upgrading the production box. The only DDL I want allow them is CREATE PROC. In this case I will be able to see who created the proc and recompile it as dbo after review.


------------
Ray Miao at 5/7/01 12:33:51 PM

Should setup a r&d box for developers, then you don't have to assign permission on production box to developers.


------------
Anastasia at 5/7/01 12:22:55 PM

Hello.

We are building an ASP application with a SQL Server 2000 as a backend. I am working on SQL Server Security. The ideal picture of security is: only database administrators have permissions to alter and create database objects and all DML permissions; developers have permissions to create new stored procedures, but do not have permissions to alter any of the stored procedures that were created by dbo; all other access should be through the application roles. My biggest problem is assigning permissions to the developers. How can I modify DDL permissions?

Any helpful literature, online documentation, or personal suggestions are greatly appreciated.

Thank you,
Anastasia