Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a sql server user that our application is connecting as. The user has been granted db_datareader and db_datawriter as well as Grant Execute on each stored procedure that it needs to use.

These are the permissions I see when I veiw the usermapping on the Login and when I view the user under the database.

Given these permissions I don't think the user should be able to alter a procedure; however, when my developer logs in as that user they are able to update the stored procedures.

Am I wrong about what these permissions allow? Is there someplace I can look to find out about permissions that may not being showing up in the UI? Or do I need to explicitly deny alter on the stored procedures for that user?

share|improve this question
add comment

2 Answers

Do you see anything that has been granted or role membership that shouldn't exist?

EXECUTE AS USER = N'your user';
GO
SELECT * FROM sys.fn_my_permissions(NULL, NULL);
GO
REVERT;
GO

SELECT * FROM sys.database_permissions
WHERE grantee_principal_id = USER_ID(N'your user');
GO

SELECT r.name
FROM sys.database_role_members AS m
INNER JOIN sys.database_principals AS p
ON m.member_principal_id = p.principal_id
INNER JOIN sys.database_principals AS r
ON m.role_principal_id = r.principal_id
WHERE p.name = N'your user';

SELECT r.name
FROM sys.server_role_members AS m
INNER JOIN sys.server_principals AS p
ON m.member_principal_id = p.principal_id
INNER JOIN sys.server_principals AS r
ON m.role_principal_id = r.principal_id
WHERE p.name = N'your login';

It is possible these developers are in the db_owner role without your knowledge or have been granted ALL on the dbo or another schema.

Make sure you execute these statements in the right database, and also validate that your users are logging in as who they say they are logging in as. Perhaps they have the sa password and you don't know it. Finally, you should make sure that the database principal maps to the correct server-level login.

SELECT dp.name, sp.name
FROM sys.database_principals AS dp
FULL OUTER JOIN sys.server_principals AS sp
ON dp.sid = sp.sid
WHERE 
(
  dp.type <> 'R' 
  OR sp.type NOT IN ('C', 'K')
)
ORDER BY 
  dp.name COLLATE DATABASE_DEFAULT + sp.name COLLATE DATABASE_DEFAULT DESC;
share|improve this answer
    
Aaron thanks for the hand. fn_my_permissions returned an empty record set. The select query returned db_datareader and db_datawriter. They haven't been granted db_owner that I'm aware of and I'm not running any custom roles. –  Lumpy Oct 3 '12 at 19:44
    
What about sysadmin at the server level? I'll add a query to check server-level roles. –  Aaron Bertrand Oct 3 '12 at 20:12
add comment

Found it. Turns out there is a custom role that the user was added to and that custom role had given the user access. Unfortunatly that role didnt show up in the query above.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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