April 19, 2013 at 10:37 am
Hi,
How can I 'REVOKE', the 'GRANT' permission to DB_OWNERS members on SQL SERVER 2008 R2 ?
April 19, 2013 at 11:54 am
no.
the role db_owners, being a built in role, cannot be modified.
instead, create your own role , ie '[AlmostOwners]', and assign the privileges you want.
then remove db_owner from the users, and add your new role instead.
something like this:
CREATE ROLE [AlmostOwners]
EXEC sp_addrolemember N'db_ddladmin', N'AlmostOwners'
EXEC sp_addrolemember N'db_datareader', N'AlmostOwners'
EXEC sp_addrolemember N'db_datawriter', N'AlmostOwners'
--can the users EXECUTE procedures? comment out if false
GRANT EXECUTE TO [AlmostOwners]
--allow the users to see view proc and function definitions
Grant View Definition ON SCHEMA::[dbo] To [AlmostOwners]
--finally add our user to the role:
EXEC sp_droprolemember N'db_owner', N'ClarkKent'
EXEC sp_addrolemember N'AlmostOwners', N'ClarkKent'
Lowell
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply