Create DB Role owned by public

I use the following script in order to create db role:

USE [MyDB]
GO
CREATE ROLE [myRole] AUTHORIZATION [public]
GO

It doesn't work:
Msg 15405, Level 16, State 1, Line 1
Cannot use the special principal 'public'.

However this code works fine:

USE [MyDB]
GO
CREATE ROLE [myRole] AUTHORIZATION [dbo]
GO
ALTER AUTHORIZATION ON ROLE::[myRole] TO [public]
GO

So the question is why?

Seems to me like a bug, please post it on http://connect.microsoft.com/SQLServer/Feedback

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Yes, as Jens said, it sounds like a bug; thanks a lot for reporting it.

Now my question, why would you like to make “public” the owner of the role? It is the equivalent of saying “everyone is the owner”, therefore anyone can do whatever they want on the role (add, drop member, drop it or even take complete ownership of the role).

I would strongly recommend against granting such an elevated privilege (CONTROL or own any object) to public.

Please, let us know what is the scenario you are trying to solve and we will be glad to give some alternative suggestions.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thanks for answer,

Actually I'm creating an application for managing SQL Server security

and as far as I see now the best solution is not to give users ability to choose 'public' as owner for db roles at all, right?

|||

Correct. That’s why we think it is a bug that ALTER AUTHORIZATION allows it.

Once again thanks a lot for the feedback and for reporting the bug.

-Raul Garcia

SDE/T

SQL Server Engine