Granting permissions for SQL Server 2005

 Hi All,

In testing my code with a user that is not the dbo , I've come across a permissions problem. In the past, I've always used stored procs to access the database, so only gave Select permission to the procs to the users. Obviously with an ORM, that is no longer the case since it fires down raw SQL. This seems to mean that I will need to add the specific SELECT, INSERT and UPDATE permissions on every table in the database that is accessible. Is that the case ? Do you have any best practice recommendations for SQL 2005 (using a Winforms app with Windows authentication) ? What I have done in the past is create a SQL role for a particular application, then add the logins to that role and grant permissions to the role rather than the individual logins.

Cheers

Paul

Hi Paul,

Yes - you will need to grant the appropriate access rights for each user context which your application could be connecting to the database with when using LightSpeed. For read only entities you can get away with just SELECT, however INSERT and UPDATE (and likely DELETE) are going to be required for everything else.

We would recommend connecting through a single Windows Authenticated user account to SQL and managing access as appropriate for that user, the application security concerns should then be managed within your application directly. If you need finer grained control, you could look at stored procs :)

Happy to discuss a specific scenario if can provide more detail..

Jeremy

 Thanks for that Jeremy, I thought that was the case, but just wanted to check in case I was missing anything.

Using a role then adding windows users to that role and granting permissions to the role works in pretty much the same way as having a single user, but it means that each user can have a seperate windows login (forgot to mention it's winforms app).