Sql Server Authentication and counting the number of users accessing sql server

Question

Sql Server Authentication and counting the number of users accessing sql server

Barkingdog

SSCoach

Points: 18670
More actions
October 4, 2010 at 9:05 pm

#387936

I was given a sql server to administer. Some databases are accessed by sql authentication and I can't determine how many users\devices are actually using that sql account. I have to rely on the application developers to tell me how many people have database access (their applications have their own security tables).
1. Are there any tools I can use to determine the number of Users\Devices accessing the databases?
2. I wanted to use Windows authentication but was told "No" because any user with Management Studio installed (and we have several with read\write\execute permissions) could access the databases directly. Arrrrrrghhhh! How do you deal with user access from a licensing and database-protection perspective?
TIA,
Barkingdog

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Barkingdog (10/4/2010)
2. I wanted to use Windows authentication but was told "No" because any user with Management Studio installed (and we have several with read\write\execute permissions) could access the databases directly. Arrrrrrghhhh! How do you deal with user access from a licensing and database-protection perspective?

And by using SQL Server Authentication they can't?? Sorry, don't see the logic there.

Barkingdog SSCoach Points: 18670 More actions · Answer 2

Let me clarify the last point. Suppose we have three users, say User A, User B, and User C. The application program will verify they have permissions to run the app and access the database. If so, all accounts will access the database using the multiplexed sql user "DatabaseAccessAccount". None of the users know the password to that sql account so they, effectively, have access to the database but not through their own user accounts. So having Management Studio on their local workstation does not give them access to the database. If we gave their AD account access, then they would have direct access to the database via Management Studio.

Barkingdog

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Then why do these users have Management Studio if they don't have access to the SQL Server using it? I guess I'm missing something.

jeff.mason SSCrazy Eights Points: 9585 More actions · Answer 4

Frankly this sounds like a potential fit for using application roles. The app role would have permissions and the NT account would not (but could still be tracked).