Siusic Dot Com

The best way to grant user who is not a member of sysadmin the right

to execute xp_cmdshell system store proc is to have xp_cmdshell wrapped into a user store procedure so that users can do just the things you want them to do with xp_cmdshell without granting them explicitly the right to execute xp_cmdshell freely.

In order to do that the user store proc that calls xp_cmdshell has be to created within the master database and it has to be created under the same owner (dbo) of xp_cmdshell. Only in that case permission to execute xp_cmdshell is not checked when user executes the user store proc that wraps an xp_cmdshell call.

For example I want user Tom to be able to enlist the local C: drive. I want him to be able to execute the following statement exec xp_cmdshell ‘dir c:\*.*’ but I don’t want him to execute xp_cmdshell freely. Here is what I can do. I login to SQL server as sa (or as any user that is dbo of master). Create the following store procedure in the master database:

Create Proc usp_DirC as exec master.dbo.xp_cmdshell ‘dir c:\*.*’

After that I add Tom as a user in the master database and grant him the execution right on usp_DirC. Now Tom can execute usp_DirC and see what files and folders are on the C drive but if Tom trys to run xp_cmdshell directly he will get permission deny. Why isn’t the permssion to execute xp_cmdshell checked when it is executed in usp_DirC? That is because I own both usp_DirC and xp_cmdshell and when I grant Tom the right to execute usp_DirC I also grant him to manipulate whatever objects I own in any way that usp_DirC is defined. But if xp_cmdshell were owned by someone else then SQL Server will definitely check Tom’s permission. In that case usp_DirC will look like this

Create Proc usp_DirC as exec master.someone.xp_cmdshell ‘dir c:\*.*’

and Tom will be denied to run someone.xp_cmdshell.

The question comes why can usp_DirC created in a user database instead of the system database master? As I mentioned earlier both usp_DirC and xp_cmdshell have to be under the same owner in order for this to work. The dbo on one database is not the same to the dbo of another database. So if usp_DirC is created in user database then usp_DirC and xp_cmdshell will be under different owner and Tom’s permission to execute xp_cmdshell will be checked.

One exception is when cross database ownership chaining is enabled. In that case dbo across different databases are the same. So master.dbo.xp_cmdshell and pubs.dbo.usp_DirC are under the same owner and in this case and Tom’s permission to execute master.dbo.xp_cmdshell within pubs.dbo.usp_DirC will not be checked. That is why enabling cross database ownership chaining is not a good thing to do in terms of security.

Related Posts:

Grant Truncate?

List All Permissions a User Has in SQL Server Database and Error 4064

Join to Parametrized Table Function - Cross Apply and Outer Apply in SQL Server 2005

Restore the latest database backup automatically and xp_dirtree

SQL Mail vs Database Mail