June 9, 2008 at 11:00 am
I need to be able to view jobs,logs,script all database objects on SQL 2000 server. At the same time because it's a production box
DBA doesn't want me to do any
INSERT,UPDATE,DELETE
Is there any way to set me up with
these kind of permissions?
It's kind of "Restricted DBO" or "Limited DBO" maybe...?
June 10, 2008 at 2:04 pm
Check the following syntaxes, please.
GRANT READ ...
GRANT EXE ...
June 10, 2008 at 2:14 pm
Check syntaxes
GRANT READ ...
GRANT EXE ...
where?...
I'm doing it through GUI.
Do you mean I should create an account
through T-SQL?
I've never done it.
Can you give me examples?
June 10, 2008 at 3:05 pm
You need to be a member of db_datawriter(& db_datareader in case your delete and update statements include WHERE clause) database role for performing any such DML operations.
Manu
June 11, 2008 at 7:22 am
MANU,
So to be able to view the jobs and all database objects you
say I need to be db_datawriter & db_datareader.
But that means I will be able to do INSERT and this is a problem.
What we are looking for is to be able to somehow restrict
any INSERT/DELETE/UPDATE
while giving access to see all the objects including jobs.
June 11, 2008 at 9:11 am
Nope. To view jobs you just need membership of targetserver role under msdb database.
Manu
June 11, 2008 at 2:16 pm
It's undocumented, but to be able to view the jobs, you do need to be a member of the targetserver role in the MSDB database. It gives you the ability to execute, as well, though, if I remember right.
To script databases, in SQL Server 2000 you need to be a member of the db_datareader role in every database you want to script. You do NOT need to be a member of the db_datawriter role in any of the databases. This would give you INSERT, UPDATE, and DELETE on all tables and views.
K. Brian Kelley
@kbriankelley
June 11, 2008 at 2:38 pm
"...a member of the targetserver role in the MSDB database."
Member of which role?
Systems Administrators
Security Administrators
Or is any role?
June 11, 2008 at 2:50 pm
If you look in the msdb database, there is a database role called targetserver. It is not at the server level. The role is actually intended for multi-server administration (think SQL Server agent jobs), but has been used to grant folks access to the jobs even though they don't own the jobs.
K. Brian Kelley
@kbriankelley
June 11, 2008 at 2:56 pm
I got it.
Thank you so much K. Brian Kelley!
(Long Live SQLServerCentral.com)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply