segregation of duties in sql 2005

Say i have to create a role with minimum privileges having permissions to
execute sp_adduser. But it seems i need to be a dbo to perform that
operation.

So in order to run sp_adduser a user must be a dbo. Now that is not what i
intend to do because if i make the person as a dbo it will enjoy many more
priveleges which i dont want to give. I want a user to have permission on
only one procedure and that is sp_adduser (and ofcourse the procedures
dependent on sp_adduser as well) and nothing else.
So what is the best way to solve such a problem both in SQL 2000 and SQL 2005.

"Tom Moreau" wrote:

> Can you be more specific?
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
>
> "Munish Narula" wrote in message
>
> I am trying to implement segregation of duties in sql 2005. Can you please
> suggest me how to go about it.
>
>
 >> Stay informed about: segregation of duties in sql 2005 

You should read up on fixed database roles:

http://www.sqlservercentral.com/articles/Security/sqlserversecurityfix...atabase

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau


"Munish Narula" wrote in message

Say i have to create a role with minimum privileges having permissions to
execute sp_adduser. But it seems i need to be a dbo to perform that
operation.

So in order to run sp_adduser a user must be a dbo. Now that is not what i
intend to do because if i make the person as a dbo it will enjoy many more
priveleges which i dont want to give. I want a user to have permission on
only one procedure and that is sp_adduser (and ofcourse the procedures
dependent on sp_adduser as well) and nothing else.
So what is the best way to solve such a problem both in SQL 2000 and SQL
2005.

"Tom Moreau" wrote:

> Can you be more specific?
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
>
> "Munish Narula" wrote in message
>
> I am trying to implement segregation of duties in sql 2005. Can you please
> suggest me how to go about it.
>
>
 >> Stay informed about: segregation of duties in sql 2005 

Munish Narula (munish.narula@wipro.com) writes:
> Say i have to create a role with minimum privileges having permissions to
> execute sp_adduser. But it seems i need to be a dbo to perform that
> operation.

To run CREATE USER - which is what you should use in SQL 2005 - you need
the permission ALTER ANY USER. Thus, you do need to be dbo.




--
Erland Sommarskog, SQL Server MVP, esquel.RemoveThis@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 >> Stay informed about: segregation of duties in sql 2005