1

I am interested to know about the SQL database users under SQL->Security and their server roles and its implications for SharePoint users.

1

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.

senario:

I need to pull custom data from a custom table on a sharepoint sql db.

example 1:

within the webpart i add a direct query to call to the table and pull out data.

example 2:

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

http://msdn.microsoft.com/en-us/library/ee658127.aspx

enter image description here

http://msdn.microsoft.com/en-us/library/aa581778.aspx

  • If we are creating a sitecolection, then, Is it true that the username to login into the central admin should have sysadmin server role in SQL so that it can assign the correct priviledges to the db for that site collection being created, so that it is accessible by the user specified in the site collection admin? – variable Apr 24 '14 at 4:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.