Post reply

Revoke Grant permission to DB_OWNER

  • April 19, 2013 at 10:37 am

    #271531

    Hi,

    How can I 'REVOKE', the 'GRANT' permission to DB_OWNERS members on SQL SERVER 2008 R2 ?

  • April 19, 2013 at 11:54 am

    #1608347

    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

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply