Multiple logins w. sysadmin (multiple databases/apps)?

Question

Multiple logins w. sysadmin (multiple databases/apps)?

ZeeAtl

SSCommitted

Points: 1569
More actions
March 2, 2010 at 8:24 am

#145538

I have 3 logins with sysadmin on multi-db instance of SS2005. 2 sysadmin logins require that fixed server role for their respective applications and database access/usage.
How do I lock out the non-DBA sysadmins from accessing the other DBs and
system DBs?
Thanks,
Zee Atlanta
Accidental DBA

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

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

mike mcneer Ten Centuries Points: 1189 More actions · Answer 1

You cannot lock out a system admin from the other databases. Your better off solving the root problem which is figuring out what access those 2 application accounts really need and removing them from sysadmin. There are several roles that can be granted to the application accounts that will certainly meet the security requirements they need besides making them a system admin.

Thanks,

Mike McNeer

ZeeAtl SSCommitted Points: 1569 More actions · Answer 2

Believe it or not, I've tried all the other ones aside from sysadmin. The applications do not work properly with any of them. I'm still researching this with both app vendors (Umbraco and Quest/Blackberry Enterprise Server for Exchange).

I'll post back when solved.

Zee Atl.

mike mcneer Ten Centuries Points: 1189 More actions · Answer 3

I have seen on occasion where a third party app does something like access the master DB to pull sysinfo out of system tables and the app account required permissions on master to work. Kinda of giving away the keys but at least it I was able to only restrict them to the master and the user database and they did not have ability to start/stop instance and other things of that nature. (although a knowledgable person could prolly find a way through master procs or something but this is an app account so...) The only real thing I can think of is to maybe take a profile trace and then do a search on "Use" or something to find the database use within the profile and you might be able to track down what the app account is doing outside of the user database.

Thanks,

Mike McNeer

ZeeAtl SSCommitted Points: 1569 More actions · Answer 4

Mike,

When I setup my custom Profiler trace, which Events should I select to help pin down what the application is doing? (I'm listing all events to see what I have available aside from the minimal Default trace events.)

SIDENOTE:

Interestingly I fixed the Umbraco (content management system) use of sysadmin. Now I have another web app that is offending this best practice for logins to the MSSQL databases it connects to.

Thanks,

Zee Atlanta

mike mcneer Ten Centuries Points: 1189 More actions · Answer 5

I would just filter by the app account you are wanting to look at, make sure dbname is one of the columns.

Maybe add:

SQl: StmtCompleted and SQL: StmtStarting

SP: StmtCompleted and SQL: StmtStarting

Test a transaction, At a high level look for any part of showing use of a database outside of what you would expect and then further into the SQL or SP statements if needed.