SQL Server permissions

We are doing a quick audit of permissions in one of our ERP applications. The data is stored in SQL and from what I can tell is the application was developed in asp.net. What is confusing me is we got
1) a report of server level logins and any server level roles (i.e. sysadmin, securityadmin, serveradmin)
2) a report of all database level roles (i.e. db_datareader, db_owner)
3)) a report of all object level permissions i.e. tables (SELECT, UPDATE etc)

Logins for the application is based on windows credentials.. all of the users are listed in the server level login report (1), but 99% of the users don't have any server level role, don't have any database level role, and don't even appear on any of the object level permissions (i.e. tables). So how do they update data via the application? really confused. Each users should have different levels of access in the system. Specific to the object level permissions, there is just one login application_login that has access to all the objects. Is this common in terms of secuity, i.e. 1 account that has unrestricted access to the database, and permissions set somehow at application level?

Even stranger still, is the username (application_login) in the object level permissions report is not even listed in report 1 (the server level logins!)
LVL 3
pma111Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Can't really help you without seeing a print screen of the logins and database users.
Anyway, what's returning by the following SELECTs?
USE master
GO
SELECT * FROM sys.server_principals

USE ERP_databaseName_here
GO
SELECT * FROM sys.database_principals
SELECT * FROM sys.database_permissions

Open in new window

0
pma111Author Commented:
the reports we have are already pulling data from those tables ...
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Can you post the exact queries you're using?
And the result from each query?
0
Eugene ZCommented:
#1 <users don't have any server level role, don't have any database level role, and don't even appear on any of the object level permissions (i.e. tables).>

if these users have access to DB => they are PUBLIC db role members by default
if the public role has permissions <=> users have rights

---

#2<So how do they update data via the application?>
if your application is using "application login"
 users can have access via "application security -- check if you haver in your DB "users" (or something like this) table where from apps users get permissions

==
need more details
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.