Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2002
    Location
    Omaha, NE
    Posts
    25

    Unanswered: REVOKE CREATE ANY DATABSE From SYSADMIN Role

    In our development environment we give the developers SYSADMIN authority but this has resulted in a lot of databases where no one knows what they're for.

    The SYSADMIN Server Role is valid because they are doing a lot of work in SSIS, Reporting Services, etc but we were looking to take control of the database creation process to make sure we could document what they're doing.

    Is that possible? In my testing (DENY/REVOKE CREATE ANY DATABASE) I haven't been able to get it to work.

    Thanks :-)

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    No, it is not possible to remove the ability to create databases from the sysadmin and it wouldn't be a good idea even if it were possible.

    A good "second best" option would be to automatically track the creation of the databases. Depending on what version of SQL Server you are using, you can use either Extended Events or SQL Profiler to log who created each database, and maybe some details if that suited you.

    A very talented and determined developer might be able to "slip the leash", but that would be a whole different problem.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    I don't monitor the development environment and could care less if the developers created fifty databases. I fail to see the problem here. Now if it were QA or PROD then I'd put a quick stop to any new DDL without DBA implementation.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I agree, but different strokes for different folks... If djschmitt wants to monitor the dev servers, then I'm fine with that and it is quite easy to do.

    I'd probably take a wholly different approach and establish some threshold (database count, size, etc) that would trigger a dev database server rebuild and only the documented databases would get migrated. This would give the developers 100% freedom and only negatively impacts the ones that fail to document.

    That means that the DBAs and the Developers only need to follow the rules. Simple, trivial to manage, and fair.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    I don't have the time or resources to track everything that goes on in development environment. There are no databases created in QA or PROD without us DBA's doing the work. There are procedures to follow and strict adherence to QA before anything goes into production. Now I do wish we had the time to review what gets promoted to PROD, but unfortunately with just two DBA's and the amount of work we have on our plate that is a luxury.

  6. #6
    Join Date
    Nov 2002
    Location
    Omaha, NE
    Posts
    25
    Thanks for the conversation :-)

    We were trying to think of ways that we would be able to document who owns what database in our development environment. Currently, I've been tasked with upgrading the entire SQL Server environment here and without knowing who owns what (dbs exist with a service account owner and no users created inside them because everyone has SYSADMIN) I don't know who I can contact to test a migration.

    I have kind of implemented Pat's idea already, so we receive a regular report on new databases created. We were brainstorming some other ideas to test and see what works as well as what knowledge we could gain.

    I agree with corncrowe too, that we are also only two DBAs and don't have the resources to track everything created in DEV.

    Unfortunately I've found that there are few, if any, rules in the organization so it feels a bit like the Wild West in the SQL environment and I'm trying to come in as the FNG and pull everyone back to get a handle on this beast :-)

    Cheers!

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I just hate being the FNG, but as a consultant it happens pretty often.

    A lot depends on how muich management support you've got. If you are seen as helping the business, then managment will support you and things are easy. If you are viewed as a necessary evil, then you may never get control of the beast. You want to help the business at almost any cost, it can be painful up front but it is the only long term way to get things running smoothly and keep your life sane!

    There are many approaches that you can use, all with their own costs and benefits. The key that I've found is to create a policy that only impacts the "ill behaved" databases or developers, make that policy known with a generous "grace windon" before it goes into effect, then start to enforce the policy. We only have a few clients with identical policies, but every one of them has some policy.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by djschmitt View Post
    Thanks for the conversation :-)
    We were trying to think of ways that we would be able to document who owns what database in our development environment.
    First thing I'd do is see who is a user on these databases. Once you have an idea who are the users then get their AD email and send along a nice note asking the purpose of said database. Send constant reminders that this information is important and databases not identified on corporate list of authorized or in compliance with policy will be taken offline.

    Code:
    DECLARE @LoginName sysname 
    DECLARE @sql NVARCHAR (2000) 
    DECLARE @LOGIN_OUT AS TABLE (account_name varchar(255), [type] varchar(50), [privilage] varchar(50),[mapped_login_name] varchar(255),[permission_path] varchar(255))
    
    BEGIN 
       DECLARE cur_Loginfetch CURSOR FOR 
        
       SELECT [name] FROM master.sys.server_principals WHERE TYPE = 'G' and substring([name],1,2) != 'NT' 
        
       OPEN cur_Loginfetch 
        
       FETCH NEXT FROM cur_Loginfetch INTO @LoginName 
       WHILE @@FETCH_STATUS = 0 
           BEGIN 
                         INSERT INTO @LOGIN_OUT
                         EXEC xp_logininfo @LoginName , 'members' 
                         FETCH NEXT FROM cur_Loginfetch INTO @LoginName 
           END
                         SELECT *
                         FROM @LOGIN_OUT 
       CLOSE cur_Loginfetch 
       DEALLOCATE cur_Loginfetch 
       RETURN 
    END
    Currently, I've been tasked with upgrading the entire SQL Server environment here and without knowing who owns what (dbs exist with a service account owner and no users created inside them because everyone has SYSADMIN)
    Don't you use NT Domain accounts? I would consider using the Network policy to enforce security.

    I don't know who I can contact to test a migration. <-- See above

    Unfortunately I've found that there are few, if any, rules in the organization so it feels a bit like the Wild West in the SQL environment and I'm trying to come in as the FNG and pull everyone back to get a handle on this beast :-)
    I'm am always the FNG...

  9. #9
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    If you have the luxury of building a new machine for dev, there is no reason to bring all of the databases over. Send an email to all of the developers saying that only claimed databases will be brought over, and have them explain what the database is for at that time. This works best, if you include a hard end date for the original dev machine. You would want backups of those databases handy, because you are almost guaranteed to have a 'johnny come lately" show up asking for one or more of those.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •