locked
How do i restrict the db_owner from altering any login for a specific database? RRS feed

  • Question

  • I have granted one of the junior dba's db_owner rights for a production database called prod1 and also have been ask to prevent the ability to modify and/or alter any of the logins for that database.

    I logged in as sysadmin and tried the following for the testdbo user login

    use master
    DENY ALTER ANY LOGIN TO testdbo

    I logged out and logged in as testdbo and was still able to change the login for any user of prod1 database.

    Any help is appreciated.

    thanks

    M

    Thursday, January 2, 2014 6:20 PM

Answers

  • I think this will do it:

    grant select, insert, update, delete, execute to testdbo
    grant create table, create procedure, create view, create function, create type to testdbo
    grant alter any schema to testdbo

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Marked as answer by kingpoop1 Saturday, January 4, 2014 5:29 PM
    Thursday, January 2, 2014 9:56 PM

All replies

  • Hi King,

    First you need to ensure that the testdbo account is not mapped to any server level role except public. If testdbo is not mapped to any server level roles like sysadmin, securityadmin etc then testdbo will not be able to do any alteration to any logins because SQL Logins are server level object.

    To ensure that testdbo is not able to alter any of the users within the prod1 database you can execute the below script:

    use [Prod1]
    GO
    DENY ALTER ANY ROLE TO [TestDbo]
    GO
    use [Prod1]
    GO
    DENY ALTER ANY USER TO [TestDbo]
    GO


    Thanks and Regards Alankar Chakravorty MCITP Database Administrator SQL Server 2008 MCITP Database Administrator SQL Server 2005

    Thursday, January 2, 2014 6:38 PM
  • This won't work, at least not as real security.

    If you don't want the user to have db_owner rights, don't grant the role.  SQL Server has simple and granular permissions you can grant instead.

    Here's one of the many ways a db_owner member can sidestep a DENY:

    use master
    drop database prod
    drop login testdbo
    go
    create login testdbo with password = 'P@ssword1'
    create database prod
    go
    
    use prod
    create user testdbo for login testdbo
    alter role db_owner add member testdbo
    DENY ALTER ANY USER TO [TestDbo]
    go
    
    execute as login='testdbo'
      create user fred without login
    --  Msg 15247, Level 16, State 1, Line 1
    --User does not have permission to perform this action.
      execute as user='dbo'
        grant alter any user to TestDbo
      revert
      
      create user fred without login
      --Command(s) completed successfully.
    revert

    David


    David http://blogs.msdn.com/b/dbrowne/

    Thursday, January 2, 2014 7:18 PM
  • I agree w/David.  I just tested this and even though i ran the following:

    use Prod1
    GO
    DENY ALTER ANY ROLE TO [testdbo]
    GO
    DENY ALTER ANY USER TO [testdbo]

    verified that testdbo had only db_owner rights.

    I was still able to elevate testrw to db_owner using testdbo login.

    What are my options?  can i accomplish this by creating a restricted schema?

    The company i am supporting wants this guy to have db_owner rights, but NOT be able to go in and alter someone's login such as a real db_owner or administrator.  Maybe MSSQL will offer a RedTape role in the future, LOL.

    Thursday, January 2, 2014 7:47 PM
  • "The company i am supporting wants this guy to have db_owner rights, but NOT be able to go in and alter someone's login such as a real db_owner or administrator.  Maybe MSSQL will offer a RedTape role in the future, LOL."

    DB_OWNER is a database role and doesn't allow you to change anything about logins, only database users and roles.  So db_owner may work.  Otherwise just ask what things the user needs to be able to do, and grant just those.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Thursday, January 2, 2014 7:55 PM
  • well, what i need to accomplish is that the testdbo has db_owner rights for Prod1, but i am requested to enforce that testdbo cannot go in and elevate a user from db_datawriter to db_owner.  Currently, that's exactly what testdbo can do.  Any ideas would be appreciated.  It's crazy really, it's like a company saying, give this guy access, but we all know he's made blunders in the past with other logins so, restrict his ability to modify any logins, but give him almost enough power to bring the system down.

    Reminds me of peewee herman and francis...(I want you're cool bike even though i don't deserve it).

    Thursday, January 2, 2014 8:19 PM
  • Sounds like it's just not really possible.  I thought SQL Server allowed very granular control, but i am getting the feeling that's not possible. 
    Thursday, January 2, 2014 8:43 PM
  • SQL Server _DOES_ have very granular security.  But you have opted not to use it by adding the user to the DB_OWNER role.

    Instead you can just GRANT individual permissions on objects, schemas or the whole database.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Thursday, January 2, 2014 8:48 PM
  • Sounds like it's just not really possible.  I thought SQL Server allowed very granular control, but i am getting the feeling that's not possible. 

    SQL Server does offer very granual control. But, both for legacy reasons and to not drive everyone insane, there are also carte-blanche permissions and roles, and db_owner is one of them.

    You need to analyse more precisely what this user should be permitted to do. db_owner is definitely not right. You could grant him CONTROL on database level, and then DENY him from altering users and roles, and you must also DENY him IMPERSONATE, if you don't want to leave a loophole. Would that be safe? I am not going to say this here and know.

    Maybe we will go along fine with CONTROL on the dbo schema. (Assuming that you don't use other schemas, that is.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, January 2, 2014 8:55 PM
  • Hi King,

    Can you please confirm, what server role is the login testdbo mapped to?


    Thanks and Regards Alankar Chakravorty MCITP Database Administrator SQL Server 2008 MCITP Database Administrator SQL Server 2005

    • Proposed as answer by wstein Sunday, March 19, 2017 9:28 PM
    Thursday, January 2, 2014 8:57 PM
  • David,

    So if i wanted to grant testdbo

    SELECT, INSERT, UPDATE, DELETE

    plus

    ABLE TO CREATE/MODIFY/DELETE VIEWS, INDEXES, TRIGGERS, etc.

    plus

    ABLE TO RUN DBCC integrity checks

    but, restrict the ability to change any login or role from say db_datawriter to db_owner

    Is this possible?  if so, where do i start with TSQL.

    Thank you very much for your assist.

    Mark

    Thursday, January 2, 2014 8:59 PM
  • Hi King,

    Can you please confirm, what server role is the login testdbo mapped to?


    Thanks and Regards Alankar Chakravorty MCITP Database Administrator SQL Server 2008 MCITP Database Administrator SQL Server 2005


    testdbo is granted public under server roles and db_owner and public under user mapping.
    Thursday, January 2, 2014 9:06 PM
  • Hi King,

    I have tested the below script and it is working fine for me:

    USE [Prod1]
    GO
    CREATE USER [testrw] FOR LOGIN [testrw]
    GO
    USE [Prod1]
    GO
    EXEC sp_addrolemember N'db_datareader', N'testrw'
    GO

    Please ensure that the Testdbo login in not mapped to any server level role except public.


    Thanks and Regards Alankar Chakravorty MCITP Database Administrator SQL Server 2008 MCITP Database Administrator SQL Server 2005

    Thursday, January 2, 2014 9:11 PM
  • Here is what i have for testdbo:

    I am still able to change and successfully elevate testrw from datawriter to db_owner.

    I am trying to post images, but:

    Body text cannot contain images or links until we are able to verify your account.

    how do i get my account verified?

    Thursday, January 2, 2014 9:30 PM
  • I think this will do it:

    grant select, insert, update, delete, execute to testdbo
    grant create table, create procedure, create view, create function, create type to testdbo
    grant alter any schema to testdbo

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Marked as answer by kingpoop1 Saturday, January 4, 2014 5:29 PM
    Thursday, January 2, 2014 9:56 PM
  • I think this will do it:

    grant select, insert, update, delete, execute to testdbo
    grant create table, create procedure, create view, create function, create type to testdbo
    grant alter any schema to testdbo

    That does not cover DBCC which requires db_owner permission. To that end Mark could package the commands needed in a stored procedure, which is signed with a certificate, and from that certificate you have created a user which you have granted membership in db_owner.

    The Junior DBA will be able to change the code of this procedure, but as soon as he meddles with it, the signature is lost, and thereby its powers. To resign it, he need CONTROL permission on the certificate and knows it password. (Which no one should know; it should be a throwaway password.)

    You can find more information about certificate signing in this article on my web site:
    http://www.sommarskog.se/grantperm.html

    On another note: To verify your account, go this forum:
    http://social.msdn.microsoft.com/Forums/en-US/home?forum=reportabug


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by kingpoop1 Saturday, January 4, 2014 5:29 PM
    • Unmarked as answer by kingpoop1 Saturday, January 4, 2014 5:29 PM
    Thursday, January 2, 2014 10:30 PM
  • Hi King,

    Execute the below script and then try again. It should be working for you cause it is working for me. :)

    USE [master]
    GO
    CREATE USER [testdbo] FOR LOGIN [testdbo]
    GO
    USE [master]
    GO
    EXEC sp_addrolemember N'db_datareader', N'testdbo'
    GO

    DENY EXECUTE ON [sys].[sp_addrolemember] TO [testdbo]

    DENY EXECUTE ON [sys].[sp_droprolemember] TO [testdbo]
    GO


    Thanks and Regards Alankar Chakravorty MCITP Database Administrator SQL Server 2008 MCITP Database Administrator SQL Server 2005

    Thursday, January 2, 2014 10:56 PM
  • The following did not work.  I am logged in as sysadmin and tried executing against testdbo

    USE MASTER

    DENY EXECUTE ON [sys].[sp_addrolemember] TO [testdbo]
    DENY EXECUTE ON [sys].[sp_droprolemember] TO [testdbo]

    Msg 15151, Level 16, State 1, Line 1

    Cannot find the user 'testdbo', because it does not exist or you do not have permission.

    Msg 15151, Level 16, State 1, Line 3

    Cannot find the user 'testdbo', because it does not exist or you do not have permission.

    Friday, January 3, 2014 3:08 AM
  • Here is something which actually works (as I did test it):

    USE master
    go
    CREATE LOGIN testdbo WITH PASSWORD = 'sdf09sdlkbg'
    CREATE USER testdbo
    DENY EXECUTE ON [sys].[sp_addrolemember] TO [testdbo]
    DENY EXECUTE ON [sys].[sp_droprolemember] TO [testdbo]
    go
    USE somedatabase
    go
    CREATE USER testdbo
    EXEC sp_addrolemember 'db_owner', testdbo
    go
    EXECUTE AS LOGIN = 'testdbo'
    go
    EXEC sp_addrolemember 'somegroup', someuser
    go
    REVERT
    go
    DROP USER testdbo
    go
    USE master
    DROP USER testdbo
    DROP LOGIN testdbo

    However, it is of dubious value. As you can see, we need to explicitly add testdbo as a user in the master database to be able deny the user access. I am not sure that I like adding users directly to master.

    Next, this is only good on SQL 2008 or earlier. On SQL 2012, you can use ALTER ROLE to add/drop members from group. And of course, testdbo can always do GRANT CONTROL to any user he likes.

    So it's useless clutching at straws. This user should not be a member of db_owner, period.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, January 3, 2014 8:18 AM
  • Hi King,

    It seems you did not execute the total script. The first part of the script creates a user for testdbo on the master database and provides it with datareader permissions on the master database. Please execute the total script as below else it won't work:

    USE [master]
    GO
    CREATE USER [testdbo] FOR LOGIN [testdbo]
    GO
    USE [master]
    GO
    EXEC sp_addrolemember N'db_datareader', N'testdbo'
    GO

    DENY EXECUTE ON [sys].[sp_addrolemember] TO [testdbo]

    DENY EXECUTE ON [sys].[sp_droprolemember] TO [testdbo]
    GO



    Thanks and Regards Alankar Chakravorty MCITP Database Administrator SQL Server 2008 MCITP Database Administrator SQL Server 2005

    Friday, January 3, 2014 1:12 PM
  • I think this will do it:

    grant select, insert, update, delete, execute to testdbo
    grant create table, create procedure, create view, create function, create type to testdbo
    grant alter any schema to testdbo

    David


    David http://blogs.msdn.com/b/dbrowne/

    Thanks Dave,

    This seems to be the only method that works without jumping through alot of unnecessary hoops.

    Saturday, January 4, 2014 5:30 PM