Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Multiple logins w. sysadmin (multiple databases/apps)? Expand / Collapse
Author
Message
Posted Tuesday, March 2, 2010 8:24 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, June 23, 2012 9:48 AM
Points: 59, Visits: 259
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
Post #875206
Posted Tuesday, March 2, 2010 8:52 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, October 2, 2012 10:19 AM
Points: 99, Visits: 209
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
Post #875230
Posted Tuesday, March 2, 2010 11:30 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, June 23, 2012 9:48 AM
Points: 59, Visits: 259
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.
Post #875413
Posted Tuesday, March 2, 2010 11:57 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, October 2, 2012 10:19 AM
Points: 99, Visits: 209
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
Post #875428
Posted Wednesday, March 3, 2010 8:43 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, June 23, 2012 9:48 AM
Points: 59, Visits: 259
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
Post #876049
Posted Wednesday, March 3, 2010 9:27 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, October 2, 2012 10:19 AM
Points: 99, Visits: 209
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.
Post #876098
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse