Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
ZeeAtl
ZeeAtl
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 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
mike mcneer
mike mcneer
SSC-Enthusiastic
SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)

Group: General Forum Members
Points: 101 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
ZeeAtl
ZeeAtl
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 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.
mike mcneer
mike mcneer
SSC-Enthusiastic
SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)

Group: General Forum Members
Points: 101 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
ZeeAtl
ZeeAtl
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 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
mike mcneer
mike mcneer
SSC-Enthusiastic
SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)

Group: General Forum Members
Points: 101 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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search