SQL Server 2005 – Understanding Permission Precedence

Your response pointed me in the right direction.

After looking at the object and verifying the permissions many times, I shifted my focus to the Login and User.

Apparently the login is mapped to a different user. So when I checked the server_principal against the database_principal there was a mismatch.

The login is called appuser and i am also trying to grant permissions inside the database to appuser. Unfortunately someone created a different database user which is actually called appwebuser.

So once i discovered that we were granting permissions to the wrong user and update the code to the correct database user the problem was resolved.

I used the following query to track down the login. I add in the servername and dbname so i can check against my different environments and report to the customer.

SELECT 
@@servername  as [server_name]
,db_name()  as [database_name]
,sp.name as [server_principal_name]
,sp.type as [server_principal_type]
,sp.type_desc as [server_principal_type_desc]
,sp.create_date as [server_principal_create_date]
,sp.default_database_name as [server_principal_default_database_name]
,dp.name as [database_principal_name]
,dp.type as [database_principal_type]
,dp.type_desc as [database_principal_type_desc] 
,dp.default_schema_name as [database_principal_default_schema_name]
FROM 
    sys.database_principals  dp INNER JOIN
    sys.server_principals sp ON (dp.sid = sp.sid)
WHERE 
    sp.name like 'appuser'
    or dp.name like  'appuser'
    or sp.name like 'appwebuser'
    or dp.name like  'appwebuser'

I just wrote about this in my answer to Granting rights on postgresql database to another user on ServerFault.

Basically, the best solution when you have a single user and you want to grant other users the same rights is to turn that user into a group, create a new user with the same name as the original one that's a member of the group, and grant that group to other users too.

So in your case, rails gets renamed to say myapp_users, then you create a new login role (user) named rails and GRANT myapp_users TO rails. Now you an GRANT myapp_users TO meltemi. Both the new rails account and the meltemi user now have the rights of the old railsaccount.

For more fine-grained control I ususally advise that you avoid giving the day-to-day login users or their groups ownership of the tables. Give them access via a NOINHERIT group they must explicitly SET GROUP to, or better, use a completely different user for privileged operations like DDL and GRANTs. Unfortunately this doesn't work with Rails, because Rails likes to apply migrations whenever it feels like it and AFAIK doesn't give you the ability to specify a different, more-privileged user to run the migrations as.