September 22, 2005 at 8:35 am
I've got a new boss who wants to be set up (using windows authentication) to view/monitor database management and maintenance. He doesn't want to have database administrator rights, but merely to be able to look around.
How would I set him up?
Thanks.
September 22, 2005 at 8:42 am
Create a login for him using Windows Authentication. Assign him an appropriate default database (the one he will use the most). Assign him access to that database and DATAREADER only.
The easiest way to do this is via Enterprise Manager (Security>Logins). However, even if you go that way, take time to look up how to do via TSQL in the BOL.
-SQLBill
September 22, 2005 at 8:44 am
Oh, most importantly...shake your boss's hand and tell him THANK YOU. Most bosses demand system admin privileges, even though they don't need it and it's a bad idea - they still want it. To have a boss that says 'just give me what I need and I don't need admin privileges' is awesome.
-SQLBill
September 22, 2005 at 8:50 am
Will he be able to look at maintenance plans, logs, and the like with the DATAREADER role? Basically, he needs to be able to see (from his workstation) that I'm doing my job properly as a DBA.
September 22, 2005 at 9:09 am
Presumably if he is going to check that you are doing your job correctly he should know what he is doing. If that is the case he should be a dba, if he is not a dba then why not set him up some reports that give him a limited view of what activities he is chacking on.
September 23, 2005 at 6:12 am
I don't believe there is a way to do this in SQL2K.
I've searched for the same kind of security. I need to be able to allow application managers to look at the jobs, backups, replication, etc. on a server to research application issues but not have update privileges - no matter who owns the jobs. But I came up empty handed.
If you come up with any good ideas, I'd love to hear them.
September 23, 2005 at 8:09 am
It might be more useful to generate reports for your boss that show that things are getting done. For instance, run some queries that display backup history for your databases showing the day & time they were backed up. If you're doing replication, run some comparison queries showing that the rowcounts at the subscriber match the row counts at the publisher. If you reindex periodically, run some "before" & "after" showcontig reports. etc...
September 23, 2005 at 11:51 am
In order to monitor things (what you are doing and more importantly what is going on) you have no choice but to make him a sysadmin <period>. After all your a DBA, they're a manager (or whatever title) of the DBA(s). What's the big deal here ? If they could not walk and talk a little SQL then how can they manage those responsible for it ? I've been to places with 'working' (hands on) managers and places wher the managers cannot spell SQL. Personally, a 'working' manager is better by far to have. Again, what's the big deal ?
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
September 23, 2005 at 1:25 pm
I failed to mention that I've been on the job here for about 3 weeks now. The 2 developers that were left have quit due to high turnover. My manager was just hired this week. So basically we're trying to assess the current state of IT here. What are the apps being used, who uses them, which databases are used for which apps, etc.
Question: what should I be using to do these reports? What do you guys use?
Thanks!
September 23, 2005 at 1:35 pm
If your site uses SMS then almost everything you need is present 'out of the box' from a windows perspective on both the client and server side if the admins did their jobs. Now all you have to do is understand the SQL Server schema and WMI to extract the information. If not, then check into a package called AuditWizard it's cheap ($649 for an enterprise) and quick (initially discovered 1500+ computers on our network in 15 minutes !).
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
September 27, 2005 at 3:23 am
Perhaps this script can help you out:
http://www.sqlservercentral.com/scripts/contributions/1301.asp
September 27, 2005 at 8:22 am
Once I got around the following error:
Syntax error converting datetime from character string.
I could see that this is useful.
September 27, 2005 at 8:25 am
I just got that error too .... Didn't bother to troubleshoot it yet. I guess it got past the proofreader !
It's hard to believe that MS stores the dates in such a crappy format. I went through fixing them in another script I did a while back .... What a pain.
September 28, 2005 at 3:06 am
The error is caused by next run date which can be 0
a simple workaround
CASE
WHEN msdb..sysjobschedules.next_run_date =0 THEN NULL
ELSE cast(cast(msdb..sysjobschedules.next_run_date as varchar(15)) as datetime)
END AS next_run_date
,
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply