Windows authentication

[David]

Hi all,

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. Its
logs users on correctly i guess, using a more secure link via the
domain. 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?

I had a brief look through but could not find anything that stood out,
in the SQL Manager.

Thanks

David

P.S This is SQL Server 2005 Workgroup edition running on a 2003 windows
domain server. Alongside a SQL 2000 server, which is running fine using
sa accounts.

For those in the same timezones as me... have a good evening.

[Erland Sommarskog]

David (da************@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx