Hi,
I am developping an application using Windows forms(C#.net) and SQL Server 2005 Express edition. I would like to use SQL Server authentication. This is what I would like my application to do:
When a user logs in and is authenticated by SQL Server, the application to be able read the user's permissions/rights from SQL Server and use them to restrict to access what the user can do in the application. I have gone through lots of articles but all articles talk either of security in the .net environment or SQL Server security. None talks about integrating database security with application security.
Any leads will be appreciated.
Jakiiki
Hi Jakiiki,
Applications connect to SQL Server either via SQL Authentication or Windows Authentication. Once Autheticated the Applications identity in SQL Server is determined and access to resources is determined by the permissions the identity possess.
You can retrieve permission and role information about a user from sql server's catalog views.
sys.server_permissions and sys.database_permissions will list out all of the permissions granted to sql logins and sql user respectively.
sys.server_principals and sys.database_principals will list the sql logins for the instance and the sql users for the current database.
database and server roles will be listed in sys.database_principals and sys.server_principals
membership in these roles is tracked in the catalog view sys.database_role_members and sys.server_role_members
HTH,
-Steven Gott
SDE/T
SQL Server
sql