March 7, 2007 at 4:08 am
Hi,
I have a permissions issue which is becoming a real headache!
We have our databases arranged so that each application has it's own database to hold the stored procedures relevent to it, the application user is a member of the db_owner role of it's own DB. The stored procedures in the 'code' database select data from other databases and permissions to those tables are granted through database ownership chaining. The application user has execute permission to the SPs in it's own database but not select permissions on the base tables.
I have set up a database for a new application to work in the same way, however the problem is that the permissions will only work if my windows login (which is sysadmin) is the owner of this new database. When the application user executes a stored procedure an error is returned saying that select permissions are denied on the base table. If change the owner of the datbase ot myself the application user can run the SPs without a problem. I have tried changing the owner of the new database to SA and other sys admin windows logins but none of them work except for mine!!! I do not want to leave myself as the owner of the database becasue I think that it should be a system account, and if I leave the company my windows account will be disabled.
I have tried re-building the new database several times with no sucess, changed it's owner to every suitable login we have with no sucess. What could be special about my own login that would allow this to work where other logins fail?
Any help is greatly appriciated.
Thanks
Dave
March 8, 2007 at 6:32 am
If you add the application user to the database that you wish it to be able to access and just grant the permission to the tables it need to access you should be fine. You have also stated several things that should raise some concerns about the permissions that are given to the applications.
March 9, 2007 at 3:51 am
Hi,
Thanks for the reply.
I do not want to give direct select permissions to the table as it contains some sensitive information. By only having execute permissions on a stored procedure, this allows us to control which fields and which records the user the can acesses on the table.
I was wondering why the changing the owner of the DB from my windows login to sa or other sysadmin windows logins would prevent the DB ownership chaining from working.
Dave
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply