Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Revoke Grant permission to DB_OWNER Expand / Collapse
Author
Message
Posted Friday, April 19, 2013 10:37 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 11, 2013 5:56 AM
Points: 20, Visits: 57
Hi,

How can I 'REVOKE', the 'GRANT' permission to DB_OWNERS members on SQL SERVER 2008 R2 ?
Post #1444511
Posted Friday, April 19, 2013 11:54 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 7:11 PM
Points: 12,876, Visits: 31,789
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1444550
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse