asked
how to make a sql login to act as SA and excute a stored proc on OS level
here is my scenario, there is a sql server login (Sector_1) with dbo and EXCUTE privileges on the db/server. we are trying to login as Sector_1 and excute a stored procedure which checks if a file exists on a disk at OS level which is from a linked server. but if this login was SA or windows authenticated login, it returned the results but with its current permission which is non-sa it returnes false, as if the file didn't exist on the remote
server.so obviously i can not give them SA permission but i really need a way to solve this issue,any help is much appreciated. Thanks
I had similar problem some time ago, i wanted a user to be able to execute stored procedure that interior required at least 'securityadmin' role, without giving him more than execute premission on procedure. One way is to give login ability to impersonate SA login i.e: EXECUTE ('SELECT SUSER_SNAME()') AS LOGIN = 'sa' but this is a bit dangerous EXECUTE ('DROP TABLE dbo.table') AS LOGIN = 'sa' Other way (and definetly safer) is to sign procedure with certificate, i found the following link very helpfull in doing that:
http://sommarskog.se/grantperm.html#Certificates
thanks a lot, that link was helpful as well, but still, what are the possible risks of using EXECUTE AS to impersonate the login?
15 People are following this question.
