Posted by Erland Sommarskog on 12/03/06 15:48

David (david.goodyear@gmail.com) writes:
> You're all stars, really helpful. Maybe one day i can be :)
>
> My question is regarding the authentication in Windows SQL Server 2005.
>
> We have setup a sa account. We purchase software from a third party
> that runs on it. Before their software logs on the sql server as sa,
> and does its own access control, windows autentication takes over.

Tihs does not really make sense. I could guess that the application
uses an application role, or that it authenticates the users on its
own, and then impersonates the users in the database. Then again, it
may be something completely different.

> However, I am probably blind, but where can we assign rights to
> each domain user? I.e. stop XXY dropping tables, but allow ABC to drop
> tables?

Normally, you don't assign permissions per user, but rather you add
users to a role, and then grant permissions to that role. As I recall,
you can also grant access to a Windows group, and then assign rights
to that group.

The best way to assign rights is the GRANT statement. There is a GUI
for this in Enterprise Manager, but I have not used it myseelf.

But since your application appears to apply its own security scheme,
I think you should talk with the vendor about it before you do anything.



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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]