Advice on security model for XML Web Services for many users in Active Directory role

I am working on the security model for an application that will be used by 100s of users with a dedicated SQL 2005 database for this application and access via SQL XML Web Services.

The client has asked to make it "open" during alpha testing such that anyone can access the web services without having to set them up first. Is there a way to do this? The best I can figure is to use mixed mode security and hard code a login and password. Any method using Windows authentication would require that I add every user at a minimum to the database.

In production, all users will have an active directory role specified that determines if they should have access to the web services or not. However, it is my understanding that to use Windows authentication, I would still need to add each individual user at a minimum as a Login to the SQL Server, and under best practices also as database users with permissions granted to the endpoint.

Am I correct in the above, or is there a more efficient way to achieve these results?

Thanks

-L

Your post seems to have been moved to this forum, as I have not seen it before. Could you please let me know where you posted this originally?

If the individual users are all belonging to a Windows group, you could just grant connection permissions to that group. But I am not familiar with XML Web Services, so I cannot say whether this is the best course of action.

Have you checked the following link?

http://msdn2.microsoft.com/en-us/library/ms191274.aspx

I'd suggest to also post in the XML forum: SQL Server XML

Thanks
Laurentiu

sql

Admin role lost - inability to administer database

When I open SQL Server Enterprise Administrator to create or manipulate data
bases errors are thrown at me for not having appropriate permissions. Howeve
r, on one database I am able to modify its contents, tables and so forth.
How have I lost access? What can I do to fix it?Hi,
Are you trying to do this with a user with "DB_OWNER" role. In this case
only the database which is assigned this role will only have the permission.
If you have to perform administrative task on all databases then either
register using "SA" user or a Login with "Sys Admin" server fixed role.
How to identify the role assigned:
1. Login to Query Analyser as "SA"
2. Execute the procedure sp_helplogins <login name>
Note:
Automatically the permissions will never be lost / revoked in SQL server.
Thanks
Hari
MCDBA
"John Russell" <anonymous@.discussions.microsoft.com> wrote in message
news:46C5D23D-730B-4820-97B6-F6A710E73C81@.microsoft.com...
> When I open SQL Server Enterprise Administrator to create or manipulate
databases errors are thrown at me for not having appropriate permissions.
However, on one database I am able to modify its contents, tables and so
forth.
> How have I lost access? What can I do to fix it?

admin or other role

I have one other question. How do you define roles? What role(s) are required to fully administer SQL Server? What role(s) are required to fully administer a single database in SQL Server?

thanks,

In Books Online, look up [fixed server roles] and [fixed database roles].

Those roles are predefined. You can also create your own roles (which only has permissions that you grant to them.)

Full server administration = sysadmin.

Single database administration = db_owner.