I am interested to know about the SQL database users under SQL->Security and their server roles and its implications for SharePoint users.
there shouldnt be any implication or hardly any ;) ... the accounts that would have access would be the SQL administrator, but also the following sharepoint accounts... Farm Administrator, Web Application pool account, SharePoint Service Application Pool account, search Crawl account and the User Profile Synchronization account.
Users dont have direct access. If you use SQL query than you would use a managed account that is added into the SQL server security for that specific DB(only two accounts!! read and write).
for best practice and minimal security risk you need a layered approach.
I need to pull custom data from a custom table on a sharepoint sql db.
within the webpart i add a direct query to call to the table and pull out data.
I create a data access layer and through this layer (data access project). I then add this dll to the webpart project and call the data access layer. The data access layer than calls a stored procedure with either get or set permission to get or set data within the custom table.
within example 1 its bad, why? because you only have one layer and its open to sql injection attacks leaving the DB compromised to a user having full control. Within example 2 you have an intermidiate layer that leaves injection attacks to a minimal if not near impossible. The stored procedure uses specific accounts and only gets or sets specific data into a specific table. The account used would be either a read or write account within the security section of the database for tightened security. A stored procedure is another layer in itself called abstraction layer.
more info here