protect users from using xp_cmdshell

[masds]

How to protect xp_cmdshell from being executed by a user, say the user running the SQL Server serive.

 

[gradley]

By default, only System administrators should have rights to run xp_cmdshell.

You can however, go to xp_cmdshell in the extended stored procedures section of the master database and explicitly "DENY" rights to a specifiy user.

Is the registered server for that individual connected with a user that has Admin rights?

 

[mrdenny]

If I user logs in with the SQL Server service account they will have sysadmin rights, and will be able to run xp_cmdshell. The only way to remove the ability for sysadmins to run xp_cmdshell would be to remove it from the database.

However any sysadmin can put it back in.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[masds]

If I user logs in with the SQL Server service account they will have sysadmin rights

So the user who runs the SQL Server service must have sysadmin fied server role? In my server I set this user doesn't have sysadmin role, is there any problem with this setting?

 

[Catadmin]

What Denny is saying is the SQL Server service account or the SA account both have SysAdmin rights. If your user connects to SQL with either of these logins, he / she will be able to execute xp_cmdshell and will have SysAdmin rights.

You can make the user account a non-Sysadmin user account and as long as this user doesn't have the pwd to either of the above accounts, you'll be fine. If that user DOES have the pwd to either of the above account, I would advise changing that password and then checking all jobs, replication setups, and SQL Server to SQL Server connections to change the passwords there too.



Catadmin - MCDBA, MCSA
Beware the error of pre-emptive poultry inventory!

 

[masds]

As a sysadmin to set up a job, I know the SQLAgent service run in account A, then create a job and change the owner of the job to account B, what level of the permission this job has? same as account A or account B?

 

[Catadmin]

Usually, the job has the permissions of the owner. In this case, account B. I'm sure there are exceptions, but I can't think of them off hand.



Catadmin - MCDBA, MCSA
Beware the error of pre-emptive poultry inventory!