Link to home
Start Free TrialLog in
Avatar of Pau Lo
Pau Lo

asked on

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 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!)
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

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
SELECT * FROM sys.server_principals

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

Open in new window

Avatar of Pau Lo
Pau Lo


the reports we have are already pulling data from those tables ...
Can you post the exact queries you're using?
And the result from each query?
Avatar of EugeneZ
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial