SQL Server - what's the point of database users?

[Freaki Li Cuatre]

I seem to be able to do everything with just the login.

Why have the overhead of creating database users too?

I'm sure there are more complex scenarios than mine where this would be needed.

[eek]

I seem to be able to do everything with just the login.

Why have the overhead of creating database users too?

I'm sure there are more complex scenarios than mine where this would be needed.

because you may not want some users and systems anywhere near certain information

[SimonMac]

because you may not want some users and systems anywhere near certain information

Why not just give everyones sysadmin privileges?

Oh hang on......

[mudskipper]

I seem to be able to do everything with just the login.

Exactly. That's the point of users - most won't need to do everything.

[Freaki Li Cuatre]

Exactly. That's the point of users - most won't need to do everything.

No, but I can set all that at the login level.

Let's suppose I have an AD group called CUK Users and I want that group to be able to have read/write privileges on a database called CUK. All I do is map the login to that table and add the db_reader/db_writer roles for CUKUsers.

I've still got everything else locked down - the login can only run queries on CUK. And I haven't had to create users to do this. So, why would I create users in this scenario when the login seems to do everything?

[eek]

No, but I can set all that at the login level.

Let's suppose I have an AD group called CUK Users and I want that group to be able to have read/write privileges on a database called CUK. All I do is map the login to that table and add the db_reader/db_writer roles for CUKUsers.

I've still got everything else locked down - the login can only run queries on CUK. And I haven't had to create users to do this. So, why would I create users in this scenario when the login seems to do everything?

audit trails?

[SpontaneousOrder]

I don't know SQL Server. Is Users a synonym for Schemas, like in oracle?

[robpow]

Why do you need the user context to reach the DB at all? In a multi-tier application you tend to do all the auth in the middle tier/webserver/etc and just have an application identity connect to the DB.

Or do the users need access to the DB using SQL Mgmt Studio, Excel etc?

Matt

[lilelvis2000]

No, but I can set all that at the login level.

Let's suppose I have an AD group called CUK Users and I want that group to be able to have read/write privileges on a database called CUK. All I do is map the login to that table and add the db_reader/db_writer roles for CUKUsers.

I've still got everything else locked down - the login can only run queries on CUK. And I haven't had to create users to do this. So, why would I create users in this scenario when the login seems to do everything?

Fine, if you are using AD. But most of my clients won't let AD near SQL Server. I suppose they are worried about network admin users blowing away databases.

Not being a DBA its not something I worry about.

[SpontaneousOrder]

I seem to be able to do everything with just the login.

Why have the overhead of creating database users too?

I'm sure there are more complex scenarios than mine where this would be needed.

Do your databases have a guest user? If you remove that then they would probably need a 'proper' user.
i.e. can you get access to one DB, and restrict another, all using just the login?