November 9, 2011 at 10:54 am
We have a custom role in our databases for granting permissions to SPs which is [db_permission]
We have this role created as
CREATE ROLE [db_permission] AUTHORIZATION [dbo]
WE dont use Schemas in SQL 2005 but we use only dbo as schemas so we dont have any user defined schemas. Hence we create all objects under dbo schema.
So every time when I create new database, this role is created there.
Now a developer account called testuser who has db_readaccess, db_writeaccess and db_permission access. The function [dbo].[function_test_name] already exists in the database
When testuser account tries to run this,
GRANT EXECUTE ON [dbo].[function_test_name] TO [db_permission]
GO
He gets this error,
Msg 15151, Level 16, State 1, Line 1
Cannot find the object 'function_test_name', because it does not exist or you do not have permission.
Can anyone tell me why is that? I tried to grant db_ddladmin role also to testuser but same isssue. IF I grant db_owner to testuser then no issues but I cant grant that.
testuser account lacks any permission or what? Please let me know.
November 10, 2011 at 10:10 am
I didn’t hear anything back yet but I found something,
From this page, http://msdn.microsoft.com/en-us/library/ms189612.aspx
It says db_datawriter can DELETE, INSERT, UPDATE and
Db_ddladmin can use any DDL command. (CREATE< ALTER< DROP)
But only db_owner can have grant permission.
Also the document says,
Fixed database roles are not equivalent to their database-level permission. For example, the db_owner fixed database role has the CONTROL DATABASEpermission. But granting the CONTROL DATABASE permission does not make a user a member of the db_owner fixed database role. Members of the db_ownerfixed database role are identified as the dbo user in the databases, but users with the CONTROL DATABASE permission, are not.
So what I tried this command, Grant Control to [Db_permission] and things are fine now.
[Db_permission] is a custom database Role which we use to Exec. Sps
Testuser is a member of [Db_permission] Role and now I granted Control Database permission to [Db_Permission] Role. The disadvantes are With control permission one can grant, deny and revoke as well. This is what I figured out, Please comment\provide feedback if I am wrong somewhere.
December 6, 2011 at 10:54 pm
[testuser] can't GRANT EXECUTE on the proc, because he hasn't been granted the authority to do so. In order for a user to grant privileges to another on an object the user doesn't own, the owner must have first granted the privilege to the user/role using the WITH GRANT OPTION clause. This behavior is standardized across most DBMSs.
Fixed database roles db_owner and db_securityadmin, along with holders of the CONTROL database privilege, have the ability to impersonate the owner for purposes or granting rights to users. I've not found a combination of privileges that will allow a user to manage permissions without allowing all that db_securityadmin offers.
GRANT CONTROL and db_owner provide equivalent levels of access. db_securityadmin allows an account to elevate accounts to owner privileges through he ability to GRANT CONTROL. If your organization doesn't allow you to give db_owner rights to non-DBAs, then you shouldn't be granting CONTROL or adding that user to db_securityadmin either.
It seems the root of what you're trying to do is to allow certain users to run all the stored procs in the database without having to individually grant all those procs to the role containing those users. The best practice is to individually grant just those procs that members of a role require. However, an acceptable solution, assuming proper control of the procs in the database, is to:
GRANT EXECUTE ON SCHEMA::dbo TO [SOMEROLE]
This satisfies the need for a role's member to be able to run all the procs in the dbo schema, without the risk of unintended or malicious privilege elevation.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply