July 7, 2009 at 2:02 am
So I'm considering using an application role, never had any experience woth this. I've been reading about it and my original idea on how to use it does not seem to work. Also I wonder how save this is.
This is my situation: students following a course need to create a database based on a some master database(not the systemDB). So I restore a backup created for this situation and restore it with a different name. Not a problem. We dont wish the students to have rights which they need to restore a database, so I created a SP and thought to have this SP execute the restore operation with an application role. I thought I could execute the restore from this SP under an application role, but it seems I'm mistaken: an application role is used to connect to the server/database and cannot be used to change a connection from within a SP. This is correct?
I have an alternative. This restore operation is started from an application, which connects to the SQL Server, so I could use the application role from this application to connect and execute the restore action. But I am confused here: if I understand correctly you have to send the login AND password(!) to use the application role as your connection with the database. This means the developer(s) will know about this login and could use it for other connections.
If my interpretation is correct, securitywise this would be unacceptable. Do I miss something here?
EDIT: I've looked at the EXECUTE AS option and this is an interesting alternative for an applicationrole. However, my question remains: securitywise speaking, what are the implications? If I create an SP like WITH EXECUTE AS 'sa', what is to prevent anybody to execute it? I see there are difficulties to overcome, but still.
Greetz,
Hans Brouwer
July 9, 2009 at 4:51 am
OK, with the help of some friendly MS experts thru the Internet and an article or 2 on the subject I have constructed what I needed with the EXECUTE AS option.
Tnx all.
Greetz,
Hans Brouwer
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply