Ownership of Stored Procedures/Functions By Role Other Than dbo

I would like to enforce the following security policy. This policy is used in our other db systems (Rdb and Oracle), and I'm thinking about how to implement this in SQL Server. (Yes, I'm a DBA.)

(1) Developers are not allowed to create/alter/delete tables owned
by dbo. To prevent this, no developers will be granted role db_owner.
Developers should only be creating/modifying stored procedures/functions.

(2) All tables will be owned by dbo. DBA's (who have role db_owner
and server privileges) will be creating/modifying table definitions. DBA's will also be granting individual table priv's to the developers (most likely through the role "dco" below).

(3) All stored procedures/functions will be owned by a new role "dco"
(database code owner). All developers will be granted role "dco". No tables should be created in "dco", so role "dco" will be DENY-ed the privilege CREATE TABLE. (I'm also thinking about restricting view creation to dbo by DENY-ing CREATE VIEW.) DBA's will implicitly get access to dco procs/funcs from server privileges.

Does anybody see any possible problems with this approach? Have you tried anything like this? I've read about "broken ownership chains", but as long as the DBA grants the object privs on the tables to the developers I don't see a problem.

Thanks in advance for any input.

JeffWho will be the owner of what the DCO's will create?|||Role "dco" will own the stored procedures/functions, ie:

Create Procedure mydb.dco.myproc ...
Create Function mydb.dco.myfunc ...

Jeff

Ownership issue

One of my apps uses a login that is tied down to only use the sprocs and tables it is allowed to access. Its just given 'public' role. However, it needs to be able to add and delete fields from one table in particular. Is there a way to allow this? The login it uses has been given full rights to the table but an alter table command faults. The tbale must be a 'dbo' because other apps use it -- can I be granted DDL rights on a per table basis?Enterprise manager allow for table access specifically to tables, vies, store procedures etc... Right can be assigned per table or view. Read, Write, Update, Delete.|||Yes they can...but not for alter table it seems.|||Alter table? Dump it out and bring it back in ussing DTS utl for easy cleanup.|||Huh?

I want my app to issue an alter table command in SQL Server, it seems impossible without having dbowner in login -- which would seem massive overkill|||According to the BOL in the description of the GRANT (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqldmo/dmoref_con01_85f7.asp) command, in thefootnote 1 for ALTER TABLE it says that the object owner can issue ALTER TABLE commands for the objects that they own. The table shows that the db_owner and db_ddladmin roles can issue ALTER TABLE commands for objects owned by other users.

-PatP|||Thanks for clearing that up. Looks like I am stuffed really. Ah well...

I have got around it temporarily by raising the rights on the accout, doing the operation and then lowering them.

Owner of the database after restore

I restored a database SynComp_TEST from a backup using a login testbinu,
which have dbcreator fixed server role. ( SynComp_TEST not exists earlier)
I got below message ..
Processed 1672 pages for database 'SynComp_TEST', file 'Metadata_001' on
file1.
Processed 104 pages for database 'SynComp_TEST', file 'Datasets_001' on file
1.
Processed 8 pages for database 'SynComp_TEST', file 'Indexes_001' on file 1.
Processed 1 pages for database 'SynComp_TEST', file 'Logs_001' on file 1.
Msg 916, Level 14, State 1, Server BOYD, Line 1
Server user 'testbinu' is not a valid user in database 'SynComp_TEST'.
Msg 3013, Level 16, State 1, Server BOYD, Line 1
RESTORE DATABASE is terminating abnormally.
Now I think the owner of the database is 'sa ' ?Am I right ?
Is there any way I can restore this database with testbinu as the owner ?
Or at least testbinu as a valid user in the database?
Hi,
First you have to correct the user testbinu. The error has come because the
login testbinu was not there in the master database syslogins table.
You can rectify the problem using the procedure sp_change_users_login (see
sql server books online on usage)
After builing up the Login/ user chain , you can change the database owner
to testbinu if you need
use <dbname>
go
sp_changedbowner testbinu
Thanks
Hari
MCDBA
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:#34ScB$cEHA.3664@.TK2MSFTNGP12.phx.gbl...
> I restored a database SynComp_TEST from a backup using a login testbinu,
> which have dbcreator fixed server role. ( SynComp_TEST not exists earlier)
> I got below message ..
> Processed 1672 pages for database 'SynComp_TEST', file 'Metadata_001' on
> file1.
> Processed 104 pages for database 'SynComp_TEST', file 'Datasets_001' on
file
> 1.
> Processed 8 pages for database 'SynComp_TEST', file 'Indexes_001' on file
1.
> Processed 1 pages for database 'SynComp_TEST', file 'Logs_001' on file 1.
> Msg 916, Level 14, State 1, Server BOYD, Line 1
> Server user 'testbinu' is not a valid user in database 'SynComp_TEST'.
> Msg 3013, Level 16, State 1, Server BOYD, Line 1
> RESTORE DATABASE is terminating abnormally.
>
> Now I think the owner of the database is 'sa ' ?Am I right ?
> Is there any way I can restore this database with testbinu as the owner ?
> Or at least testbinu as a valid user in the database?
>

Owner of the database after restore

I restored a database SynComp_TEST from a backup using a login testbinu,
which have dbcreator fixed server role. ( SynComp_TEST not exists earlier)
I got below message ..
Processed 1672 pages for database 'SynComp_TEST', file 'Metadata_001' on
file1.
Processed 104 pages for database 'SynComp_TEST', file 'Datasets_001' on file
1.
Processed 8 pages for database 'SynComp_TEST', file 'Indexes_001' on file 1.
Processed 1 pages for database 'SynComp_TEST', file 'Logs_001' on file 1.
Msg 916, Level 14, State 1, Server BOYD, Line 1
Server user 'testbinu' is not a valid user in database 'SynComp_TEST'.
Msg 3013, Level 16, State 1, Server BOYD, Line 1
RESTORE DATABASE is terminating abnormally.
Now I think the owner of the database is 'sa ' ?Am I right ?
Is there any way I can restore this database with testbinu as the owner ?
Or at least testbinu as a valid user in the database?Hi,
First you have to correct the user testbinu. The error has come because the
login testbinu was not there in the master database syslogins table.
You can rectify the problem using the procedure sp_change_users_login (see
sql server books online on usage)
After builing up the Login/ user chain , you can change the database owner
to testbinu if you need
use <dbname>
go
sp_changedbowner testbinu
Thanks
Hari
MCDBA
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:#34ScB$cEHA.3664@.TK2MSFTNGP12.phx.gbl...
> I restored a database SynComp_TEST from a backup using a login testbinu,
> which have dbcreator fixed server role. ( SynComp_TEST not exists earlier)
> I got below message ..
> Processed 1672 pages for database 'SynComp_TEST', file 'Metadata_001' on
> file1.
> Processed 104 pages for database 'SynComp_TEST', file 'Datasets_001' on
file
> 1.
> Processed 8 pages for database 'SynComp_TEST', file 'Indexes_001' on file
1.
> Processed 1 pages for database 'SynComp_TEST', file 'Logs_001' on file 1.
> Msg 916, Level 14, State 1, Server BOYD, Line 1
> Server user 'testbinu' is not a valid user in database 'SynComp_TEST'.
> Msg 3013, Level 16, State 1, Server BOYD, Line 1
> RESTORE DATABASE is terminating abnormally.
>
> Now I think the owner of the database is 'sa ' ?Am I right ?
> Is there any way I can restore this database with testbinu as the owner ?
> Or at least testbinu as a valid user in the database?
>|||Follow the steps that Hari outlined for you to change the
owner.
To check a db owner, you can execute:
sp_helpdb
-Sue
On Tue, 27 Jul 2004 11:40:11 -0400, "Abraham"
<binu_ca@.yahoo.com> wrote:

>I restored a database SynComp_TEST from a backup using a login testbinu,
>which have dbcreator fixed server role. ( SynComp_TEST not exists earlier)
>I got below message ..
>Processed 1672 pages for database 'SynComp_TEST', file 'Metadata_001' on
>file1.
>Processed 104 pages for database 'SynComp_TEST', file 'Datasets_001' on fil
e
>1.
>Processed 8 pages for database 'SynComp_TEST', file 'Indexes_001' on file 1
.
>Processed 1 pages for database 'SynComp_TEST', file 'Logs_001' on file 1.
>Msg 916, Level 14, State 1, Server BOYD, Line 1
>Server user 'testbinu' is not a valid user in database 'SynComp_TEST'.
>Msg 3013, Level 16, State 1, Server BOYD, Line 1
>RESTORE DATABASE is terminating abnormally.
>
>Now I think the owner of the database is 'sa ' ?Am I right ?
>Is there any way I can restore this database with testbinu as the owner ?
>Or at least testbinu as a valid user in the database?
>

Owner of the database after restore

I restored a database SynComp_TEST from a backup using a login testbinu,
which have dbcreator fixed server role. ( SynComp_TEST not exists earlier)
I got below message ..
Processed 1672 pages for database 'SynComp_TEST', file 'Metadata_001' on
file1.
Processed 104 pages for database 'SynComp_TEST', file 'Datasets_001' on file
1.
Processed 8 pages for database 'SynComp_TEST', file 'Indexes_001' on file 1.
Processed 1 pages for database 'SynComp_TEST', file 'Logs_001' on file 1.
Msg 916, Level 14, State 1, Server BOYD, Line 1
Server user 'testbinu' is not a valid user in database 'SynComp_TEST'.
Msg 3013, Level 16, State 1, Server BOYD, Line 1
RESTORE DATABASE is terminating abnormally.
Now I think the owner of the database is 'sa ' ?Am I right ?
Is there any way I can restore this database with testbinu as the owner ?
Or at least testbinu as a valid user in the database?Hi,
First you have to correct the user testbinu. The error has come because the
login testbinu was not there in the master database syslogins table.
You can rectify the problem using the procedure sp_change_users_login (see
sql server books online on usage)
After builing up the Login/ user chain , you can change the database owner
to testbinu if you need
use <dbname>
go
sp_changedbowner testbinu
Thanks
Hari
MCDBA
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:#34ScB$cEHA.3664@.TK2MSFTNGP12.phx.gbl...
> I restored a database SynComp_TEST from a backup using a login testbinu,
> which have dbcreator fixed server role. ( SynComp_TEST not exists earlier)
> I got below message ..
> Processed 1672 pages for database 'SynComp_TEST', file 'Metadata_001' on
> file1.
> Processed 104 pages for database 'SynComp_TEST', file 'Datasets_001' on
file
> 1.
> Processed 8 pages for database 'SynComp_TEST', file 'Indexes_001' on file
1.
> Processed 1 pages for database 'SynComp_TEST', file 'Logs_001' on file 1.
> Msg 916, Level 14, State 1, Server BOYD, Line 1
> Server user 'testbinu' is not a valid user in database 'SynComp_TEST'.
> Msg 3013, Level 16, State 1, Server BOYD, Line 1
> RESTORE DATABASE is terminating abnormally.
>
> Now I think the owner of the database is 'sa ' ?Am I right ?
> Is there any way I can restore this database with testbinu as the owner ?
> Or at least testbinu as a valid user in the database?
>

Owner of the database after restore

I restored a database SynComp_TEST from a backup using a login testbinu,
which have dbcreator fixed server role. ( SynComp_TEST not exists earlier)
I got below message ..
Processed 1672 pages for database 'SynComp_TEST', file 'Metadata_001' on
file1.
Processed 104 pages for database 'SynComp_TEST', file 'Datasets_001' on file
1.
Processed 8 pages for database 'SynComp_TEST', file 'Indexes_001' on file 1.
Processed 1 pages for database 'SynComp_TEST', file 'Logs_001' on file 1.
Msg 916, Level 14, State 1, Server BOYD, Line 1
Server user 'testbinu' is not a valid user in database 'SynComp_TEST'.
Msg 3013, Level 16, State 1, Server BOYD, Line 1
RESTORE DATABASE is terminating abnormally.
Now I think the owner of the database is 'sa ' ?Am I right ?
Is there any way I can restore this database with testbinu as the owner ?
Or at least testbinu as a valid user in the database?Hi,
First you have to correct the user testbinu. The error has come because the
login testbinu was not there in the master database syslogins table.
You can rectify the problem using the procedure sp_change_users_login (see
sql server books online on usage)
After builing up the Login/ user chain , you can change the database owner
to testbinu if you need
use <dbname>
go
sp_changedbowner testbinu
Thanks
Hari
MCDBA
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:#34ScB$cEHA.3664@.TK2MSFTNGP12.phx.gbl...
> I restored a database SynComp_TEST from a backup using a login testbinu,
> which have dbcreator fixed server role. ( SynComp_TEST not exists earlier)
> I got below message ..
> Processed 1672 pages for database 'SynComp_TEST', file 'Metadata_001' on
> file1.
> Processed 104 pages for database 'SynComp_TEST', file 'Datasets_001' on
file
> 1.
> Processed 8 pages for database 'SynComp_TEST', file 'Indexes_001' on file
1.
> Processed 1 pages for database 'SynComp_TEST', file 'Logs_001' on file 1.
> Msg 916, Level 14, State 1, Server BOYD, Line 1
> Server user 'testbinu' is not a valid user in database 'SynComp_TEST'.
> Msg 3013, Level 16, State 1, Server BOYD, Line 1
> RESTORE DATABASE is terminating abnormally.
>
> Now I think the owner of the database is 'sa ' ?Am I right ?
> Is there any way I can restore this database with testbinu as the owner ?
> Or at least testbinu as a valid user in the database?
>

owned schema or role members?

Hi,
I want to create a login (for account ASPNET from the Active Directory) in
sql server express 2005 for a specific database.
When addidng a new user to a specific database, i see:
Owned schemas, where i take db_datareader and db_datawriter
Roles members: also db_datareader and db_datawriter
What's the difference between both and are they both required fpr account
ASPNET?
Tbanks
BartBart
--db_datareader
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/7d71fca8-ad8d-49c5-b4cc-c1cd
ab0fab43.htm
--db_datawriter
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/08a5c847-f993-4402-b3ac-a511
3f41e8c8.htm
"Bart" <b@.sdq.dc> wrote in message
news:OMfgIZlbHHA.3420@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I want to create a login (for account ASPNET from the Active Directory) in
> sql server express 2005 for a specific database.
> When addidng a new user to a specific database, i see:
> Owned schemas, where i take db_datareader and db_datawriter
> Roles members: also db_datareader and db_datawriter
> What's the difference between both and are they both required fpr account
> ASPNET?
> Tbanks
> Bart
>
>
>|||Thanks, but my question was more about the difference between Owned schema
and Role members.
Is it enough to take db_anything in Role members or must the user also owns
a schema with db_anything?
"Uri Dimant" <urid@.iscar.co.il> schreef in bericht
news:eDumYbsbHHA.2088@.TK2MSFTNGP04.phx.gbl...
> Bart
> --db_datareader
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/7d71fca8-ad8d-49c5-b4cc-c1
cdab0fab43.htm
>
> --db_datawriter
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/08a5c847-f993-4402-b3ac-a5
113f41e8c8.htm
>
> "Bart" <b@.sdq.dc> wrote in message
> news:OMfgIZlbHHA.3420@.TK2MSFTNGP05.phx.gbl...
>|||Bart
SCHEMA and Roles are different things. When you create a user ut should be
mapped to schema you have specified ir DEFAULT schema--DBO.
Can you elaborate on what you are trying to achive?
"Bart" <b@.sdq.dc> wrote in message
news:uEq8NwvbHHA.4140@.TK2MSFTNGP06.phx.gbl...
> Thanks, but my question was more about the difference between Owned schema
> and Role members.
> Is it enough to take db_anything in Role members or must the user also
> owns a schema with db_anything?
> "Uri Dimant" <urid@.iscar.co.il> schreef in bericht
> news:eDumYbsbHHA.2088@.TK2MSFTNGP04.phx.gbl...
>|||Well, i want to create a new login for account ASPNET (which runs under
ASP.NET) and then define an user (aspnet) for 'mydatabase'. That user must
get read/write prights to the db. At that level, i see in the window
configuration two things about read /write: Owned Schema and Role members.
So my question is: should i take db_readreader / db_writer in the Ownd
Schema or in Role members or in both?
Thanks
"Uri Dimant" <urid@.iscar.co.il> schreef in bericht
news:%23p8We%232bHHA.4632@.TK2MSFTNGP03.phx.gbl...
> Bart
> SCHEMA and Roles are different things. When you create a user ut should be
> mapped to schema you have specified ir DEFAULT schema--DBO.
> Can you elaborate on what you are trying to achive?
>
> "Bart" <b@.sdq.dc> wrote in message
> news:uEq8NwvbHHA.4140@.TK2MSFTNGP06.phx.gbl...
>|||Bart
Does the user should run queries which manipulate with tables belong to
anohter SCHEMA?
I'd go with ROLES and grant SELECT/EXECUTE permission on SCHEMAs that the
user needs to query
"Bart" <b@.sdq.dc> wrote in message
news:u1ddKM3bHHA.4772@.TK2MSFTNGP05.phx.gbl...
> Well, i want to create a new login for account ASPNET (which runs under
> ASP.NET) and then define an user (aspnet) for 'mydatabase'. That user
> must get read/write prights to the db. At that level, i see in the window
> configuration two things about read /write: Owned Schema and Role members.
> So my question is: should i take db_readreader / db_writer in the Ownd
> Schema or in Role members or in both?
> Thanks
> "Uri Dimant" <urid@.iscar.co.il> schreef in bericht
> news:%23p8We%232bHHA.4632@.TK2MSFTNGP03.phx.gbl...
>|||"Uri Dimant" <urid@.iscar.co.il> schreef in bericht
news:OOCCsw3bHHA.4000@.TK2MSFTNGP02.phx.gbl...
> Bart
> Does the user should run queries which manipulate with tables belong to
> anohter SCHEMA?
> I'd go with ROLES and grant SELECT/EXECUTE permission on SCHEMAs that the
> user needs to query
Thanks, but I still don't understand the difference between giving the Role
db_datareader / db_datawriter to user 'aspnet' and adding user 'aspnet' to
Schema dbdatareader and Schema db_datawriter ...|||Bart (b@.sdq.dc) writes:
> Thanks, but I still don't understand the difference between giving the
> Role db_datareader / db_datawriter to user 'aspnet' and adding user
> 'aspnet' to Schema dbdatareader and Schema db_datawriter ...
You should not need the schemss. They exist of legacy reasons. In SQL 2000
there was no difference between a role/user on the one hand and a schema
on the other. If you created a user/role X, you also got a schema X included
in the price.
In SQL 2005 schemas and database principals (users and roles) are separated.
But Microsoft still by default creates schemas for all pre-defined roles
and users, since old applications may rely on these and create objects
in these schemas.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||ok, thanks
"Erland Sommarskog" <esquel@.sommarskog.se> schreef in bericht
news:Xns99004B34D29Yazorman@.127.0.0.1...
> Bart (b@.sdq.dc) writes:
> You should not need the schemss. They exist of legacy reasons. In SQL 2000
> there was no difference between a role/user on the one hand and a schema
> on the other. If you created a user/role X, you also got a schema X
> included
> in the price.
> In SQL 2005 schemas and database principals (users and roles) are
> separated.
> But Microsoft still by default creates schemas for all pre-defined roles
> and users, since old applications may rely on these and create objects
> in these schemas.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx