Extended Stored Procedures - Changing Permissions

Question

Extended Stored Procedures - Changing Permissions

CavyPrincess

Hall of Fame

Points: 3336
More actions
August 18, 2010 at 9:49 am

#228082

I have been requested by our auditors to remove execute permission from public for stored proc sp_unprepare. Since I strongly suspect, but haven't confirmed, that this stored proc is being used by the application generic login, I'm trying to grant exec to that login or preferably a role.
When I try to grant access to the user, it fails with error - "Cannot find the user 'AppUser', because it does not exist or you do not have permission.". When I add the user to the master database, it works, but I don't want these users in master.
I'm running this command:
GRANT EXECUTE ON [sys].[sp_unprepare] TO AppUser
Any suggestions on how I can accomplish getting sp_unprepare out of public-execution, but still allow it to work for specified users/roles?
Thanks,
Cindy

Viewing 5 posts - 1 through 4 (of 4 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

a quick google for me seems to show that sp_prepexec and sp_unprepare are used internally by SQL to create/use a cursor; most of the posts i found are related to ADODB and blocking.

my knee jerk reaction is i would think that those permissions should not be changed, but i never had to tighten/harden a server to that kind of degree before.

removing access to any sys.* would potentially mess up any user, since that is really meta data and functions, right?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

CavyPrincess Hall of Fame Points: 3336 More actions · Answer 2

Lowell,

Exactly! Most of the other changes requested were easy - revoking access to extended procs that we aren't using. I'm not at all comfortable with this change and will express my concern and see if anyone cares.

However, for now, it's DEV, so I guess I'll elevate the user to master, then grant the permissions to that user, then revoke from public. Maybe try a proxy - I don't know. It's ugly in so many different ways and will be a maintenace nightmare going forward.

Thanks for input.

Cindy

Lowell SSC Guru Points: 323518 More actions · Answer 3

i know there are some built in sp_* functions that use cursors, so i ran this query to see if you made the change, would it be easy to identify an obvious point of failure.

select object_name(object_id),* from sys.all_sql_modules

where definition like '%cursor%'

and left(object_name(object_id),3) = 'sp_'

order by object_name(object_id)

the only thing that jumped out at me was sp_helptext , since it uses a cursor.

I'd love to hear any testing/results you find after doing this on your dev server;

i'm wondering if a user would have trouble with some commands like sp_helptext , or a procedure or something that had a cursor in it ;

Thanks Cindy!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

CavyPrincess Hall of Fame Points: 3336 More actions · Answer 4

Lowell,

Thanks for the query. I was hoping it would give me more ammunition to make my case, but alas, it did not. I was able to run the commands with the non-privileged user. Since the sp_prepare mentioned ODBC connections, I went as far as creating an Access DB on a remote workstation and ran pass-through queries via the ODBC connection. Still all of the commands worked. I ran just an sp_unprepare in SSMS and it did get the permissions error, so I'm confident it was truly denied.

Moving onto the COTS user - also non-privileged - the application did not fail when sp_unprepare was denied. I was running profiler at the time and sp_unprepare is executed all over the place. Maybe because it's "after the fact", the command still completes successfully? I have no idea.

Despite that there were no documentable failures, I'm not comfortable making this change. I talked to the requestor. Since this is a closed server with no outside access and only internal users, they were okay leaving the command granted.

Still don't have as many answers as I would like, and if I didn't have a dozen other things to do, I might have pursued it, but it's now a non-issue for the moment.

I appreciate your advice with this!

Cindy