Post reply

User creates DB on DEV server with what seems like no permissions.

  • July 20, 2011 at 1:42 pm

    #253965

    I have notification on all my servers that sends me an e-mail when a database is created so I can keep track of backups, security and maintenance requirements. Only DBAs can create databases, even on the DEV server - or so I thought. The other day, I received notification that a developer created a database on a DEV server and, for the life of me, I couldn't figure out how she did it. The server is running SQL Server 2008, Developer Edition 64 bit, version 10.0.4000.

    She is a member of a group that is DBO on several of the databases on the server. In addition, she has an individual login for the server that allows her DBO on the two databases she created. She created the two databases using a SQL script that included the CREATE Database, CREATE Tables and INSERt statements. Neither the login for the group that she is a member of nor her individual login has DB Create permissions, as far as I can tell. She is not a member of the server lever role DBCREATOR. Neither the group or her individual login has a 'CREATE ANY DATABASE' securable attached.

    I am confused as to how she could create the databases. She shouldn't be able to... yet she did. I'm not too worried, since it's DEV, and I had her attempt to create a database in PROD - she couldn't - but I'd like to understand how she managed to do this.

    Thanks for any clues. Willem

  • July 20, 2011 at 2:52 pm

    #1356035

    Are you absolutely certain that the users Login was the one that created the database? Could it have been an application login that created the database? I am shocked that you would allow an app login those type of permissions. Did you check the SQL logs to see who actually create the database?

    I would probably go in and drop the database. Since it is Dev, you will not hurt anything, and then see if the database appears once again. Also, was the database created using the default location?

    Start with answering these questions first.

    Andrew SQLDBA

  • July 20, 2011 at 3:54 pm

    #1356080

    Andrew,

    Thanks for the probing question. The databases were not created using an application login but rather by a user running a script. The script is one from Microsoft that creates and populates the PUBS and NORTHWIND databases - the files are in the default location and are the default size.

    I've taken a look in the SQL Error logs, the Windows Application event log as well as the SQL Server Logs under the Managment folder in SSMS but can't find any CREATE Database statement. I see entries that state 'Starting up the PUBS database' that correspond to the time that the script was executed but nowhere is there a reference to CREATE Database so I can't see which login did it - I must be missing something. But I've watched the user run the script so I'm pretty sure that she is the one that created the databases.

    At any rate, one thing that intrigues me is the fact that this DEV server is running the SQL Server Developer's Edition. This version was installed on the server by a DBA before me and I haven't gotten around to upgrading it to a 'proper' edition - so far, the fact that SQL Server is running the Developers Edition hasn't caused any problems. Is it possible that this is causing my problem?

    Willem

  • July 20, 2011 at 4:13 pm

    #1356086

    No, Developer Edition is the same as all the others. It only allows a certain number of concurrent connections.

    Could be the user was admin on the box, or in the administrators group. And if SQL was setup to allow the Administrators Group to be SQL admin, then that could have happened. All it takes is running the app, and it will install both of those development databases.

    I would just delete them, and watch the box for a little while.

    Andrew SQLDBA

  • July 20, 2011 at 4:32 pm

    #1356093

    And the prize goes to the man in the DBA hat!! That was it - one of the groups of which this user is a member is included in the Administrator on the server and the BuiltIn/Adminstrators group has SysAdmin rights on the SQL Server instance.

    Thanks a lot for your suggestions! Both of these issues will be fixed in short order.

    Thanks again, Andrew, for your timely responses.

    Willem

  • July 26, 2011 at 1:08 am

    #1358265

    Willem Tilstra-440027 (7/20/2011)

    And the prize goes to the man in the DBA hat!! That was it - one of the groups of which this user is a member is included in the Administrator on the server and the BuiltIn/Adminstrators group has SysAdmin rights on the SQL Server instance.

    Thanks a lot for your suggestions! Both of these issues will be fixed in short order.

    Thanks again, Andrew, for your timely responses.

    Willem

    Having you builtin\administrators as sysadmins on your SQL Server is not best practices. Maybe you want to check who is sysadmin or security admin. Brent Ozar wrote a very handy script called Blitz you can find it here. From that script you can use this part to check who has sysadmin or security admin rights on your servers SELECT name, denylogin, isntname, isntgroup, isntuser

    FROM master.sys.syslogins l

    WHERE sysadmin = 1 or securityadmin = 1

    ORDER BY isntgroup, isntname, isntuser. good luck!



    My blog

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

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