Permissions issues

Question

Permissions issues

Angelindiego

SSCertifiable

Points: 6048
More actions
June 28, 2005 at 10:59 am

#109556

**this was posted by mistake in the wrong forum....sorry!!!**
Good morning, me again!!! OK, here is my issue for today: I have to be very strict about sysadmin privilages on my db's. I have an instance here today, where I need to give one gentleman sysadmin privilages on ONE db that he accesses so that he can run extended sp's on that db to write to files. Under his user name, he has public privilages on all the db's he has access to. If I put him in a sysadmin role, he gets those rights to ALL db's. Is there any way of giving him sysadmin rights to just ONE database????? I am struggling with this one!! Thanks for your help!!!
Thank you!!,
Angelindiego

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

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

Angelindiego SSCertifiable Points: 6048 More actions · Answer 1

Remi, sorry for the confusion...but I thought my original post was in the wrong place. Am I wrong?? Shouldn't it be here in this forum?? I don't want to miss any good feedback!! Thanks a ton!

Thank you!!,

Angelindiego

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

Can't he just use dts to export the data to file?? Would be easier to solve the problem that way.

noeld SSC Guru Points: 96590 More actions · Answer 3

I still don't see the need for sysadmins rights. if the issue is that he needs access to xp_cmdshell, just make sure you set up the proxy account for the agent and grant only the necessary permissions to that proxy account!

and if he nees to go further let him write to some status table an let a JOB with sysadmins priviledges carried the requested actions out

hth

* Noel

Julian Kuiters SSCertifiable Points: 6585 More actions · Answer 4

sysadmin is a system wide role, so it can't be applied at the database level.

While I agree with everyone else in suggesting using DTS, have you not tried just GRANT EXECUTE on the xp's that the user needs access to ?

By default most of them are locked to sysadmins because they pose a huge security risk. Allowing a user to have access to xp_cmdshell for example can quickly lead to lots of problems.

Julian Kuiters
juliankuiters.id.au

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Here's a workaround :

Create dts

Create job that runs the dts

create alert that fires the job when raised

create stored proc that raise the alert

grant exec to the proc.

Document the proc for the whole process

Any questions?

Joe Salvatore SSCrazy Points: 2174 More actions · Answer 6

I am interested in using this approach for a current project.

Let's say I have a sp that ultimately writes records to a temp table named FileOut which has only 1 column named Filedata that contains the information to be written to a file on a network drive \\MyDomain\MyFolder\MyFile.txt.

Could someone expand (with an example) on the following steps outlined by Remi:

1. Create dts

2. Create job that runs the dts

3. Create alert that fires the job when raised

4. Create stored proc that raise the alert

5. Grant exec to the proc.