SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


User logins versus service account


User logins versus service account

Author
Message
Leah
Leah
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 165
We have an application that uses Windows authentication to the database. Logins are created for every new user added. External users will also be accessing this application (we have an external domain). External users will access the application through ISA via the web. I'm trying to consider all the factors (security implications/maintenance/etc) and I was hoping someone could help.

I'll give three examples:
1) When you use Windows authentication, users receive direct access to the database. When you use a service account, users have no access to the database unless they are using the application.
2) If you don't use AD groups, the logins could get cluttered with accounts (and then you have to factor in how to maintain those IDs and delete them when users don't exist anymore, etc)
3) When you move the database from one server to another, you need to create all the logins before the database is restored or else they will be orphaned.

What other implications are there or am I worrying over nothing?
russell-154600
russell-154600
SSC Veteran
SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)

Group: General Forum Members
Points: 261 Visits: 185
i'll use windows authentication and create AD groups. grant access to groups not individual accounts
brad.joseph 13171
brad.joseph 13171
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 9
Hope this helps give some insight to your dilemma:

In my opinion it is always best to use Windows Authentication unless it is absolutely necessary.

Better still, I like to create AD user groups per system/application/database. Usually it involves creating 3 groups. Namely: Admin (db_owner), Read only and Read/Write. In this way if a user needs access to a database they can be allocated into the correct group in AD and nothing further needs to be done inside SQL Server.

The beauty of this is that it reduces your SQL Server maintenance quite dramatically, for example: if a person leaves the company they are removed from AD thus the user is automatically removed from the relevant databases meaning less maintenance to clean up old users etc.

This method also helps to reduce the amount of users that are listed in your database or within the SQL Logins again creating an environment that needs less support.

Another advantage is your SQL Admins do not have to get involved when new users need to be given access to the databases. The AD Admins add the new users into the relevant AD Group, nothing needs to be done within SQL Server at all.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search