July 7, 2009 at 7:25 am
So I have delved into the EXECUTE AS option, and it's mighty interesting stuff with lots of possibilities. A quetion though, which I seem not to find through Google.
I create a SP with this option:
CREATE PROCEDURE AccessMyTable
WITH EXECUTE AS 'dbcreator'
AS SELECT * FROM adventureworks.production.product;
GO
When I run this I receive an error:
Cannot execute as the user 'dbcreator', because it does not exist or you do not have permission.
When I run it with the option SELF instead of 'dbcreator' I have no problem. The SP works as intended.
I wish to use DBCreator, for that is the permission I need for a restore operation. When I create teh SP with SELF I give basically SA-rights to the procedure, which is not neccessary.
Greetz,
Hans Brouwer
July 7, 2009 at 7:27 am
Execute As is expecting a user, not a permission name. Create an account that has dbCreator permissions and try executing as that user.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 7, 2009 at 9:10 am
Followed your advise, but no. Let me describe:
I have a SP to execute a RESTORE DATABASE action. This works fine without the WITH EXECUTE AS option. I've created a new SQL Login UserSA and gave this the sysadmin serverrole. I assigned it no default database. Now I compile my SP like this:
ALTER PROCEDURE [dbo].[USP_ReplaceUserDB]
@DBNaam varchar(30)
WITH EXECUTE AS SELF -- or UserSA, an SQL login with SA permission
I run the statement:
EXEC USP_ReplaceUserDB 'TestDB'
This errormessage appears:
Msg 3110, Level 14, State 1, Procedure USP_ReplaceUserDB, Line 24
User does not have permission to RESTORE database 'TestDB'.
So, I have permission to execute this SP, but not to execute the restore command. This with the UserSA with SysAdmin rights. It does not matter how I execute this SP, it will always give this error. I miss something, and I believe it has to do with the RESTORE DATABASE action. When I use an example with a SELECT * FROM usertable this construction works as expected.
Help?
Greetz,
Hans Brouwer
July 7, 2009 at 9:17 am
Have you tried explicitly granting backup admin to the login being used? I'm guessing now, but might be worth a shot.
I'll try playing around with the issue a bit, but probably won't be able to get to that before tomorrow. Haven't tried this one, so haven't run into it before.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 7, 2009 at 9:30 am
I've done loads of this and it could be any number of issues
when you're dealing with backups etc in 2008 (not sure about 2005) you'll need to make a credential as well and bind that to the sql login you use (or you don't have file permissions)
always remember to issue the REVERT command at the end of the proc as well
MVDBA
July 8, 2009 at 3:16 am
'Have you tried explicitly granting backup admin to the login being used?'
Well no, I have granted SA-rights to the login I use in EXECUTE AS.
What I have found so far:
I have created a login:
/****** Object: Login [db_Demo_dbo] Script Date: 07/08/2009 10:58:31 ******/
/* For security reasons the login is created disabled and with a random password. */
/****** Object: Login [db_Demo_dbo] Script Date: 07/08/2009 10:58:31 ******/
CREATE LOGIN [db_Demo_dbo] WITH PASSWORD=N'¾´+ãbüÄ¥÷?mð·3¬&ý??,o{+kD£s¨', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO
EXEC sys.sp_addsrvrolemember @loginame = N'db_Demo_dbo', @rolename = N'sysadmin'
GO
ALTER LOGIN [db_Demo_dbo] DISABLE
As you can see this Login is added to the SA-role.
I have created this SP:
ALTER PROCEDURE AccessMyTable
WITH EXECUTE AS 'db_Demo_dbo'
AS
SELECT * FROM adventureworks.production.product;
--CREATE TABLE NewTable(data int)
--CREATE DATABASE NewDB
GO
Whatever action I use in this SP, I always receive this error:
The server principal "db_Demo_dbo" is not able to access the database "AdventureWorks" under the current security context.
I find this strange, for this login has SA-rights. This error also appears when I explicitly map the Adventureworks to this login, despite the SA-role already granted. However, when I make the default database Adventureworks, then the SELECT statement and the CREATE TABLE works... When I use the CREATE DATABASE I receive this message:
CREATE DATABASE permission denied in database 'master'.
I am at a loss and for the time being I'm going to use an Application-role for what I want.
I would like to use this construction, for then I don't have to sent a password over the net. Any insight would be appreciated.
Greetz,
Hans Brouwer
July 9, 2009 at 4:50 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.
Tnx all.
Greetz,
Hans Brouwer
July 9, 2009 at 4:50 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.
Tnx all.
Greetz,
Hans Brouwer
July 22, 2009 at 7:55 pm
How did you resolve this?
I have a very similar issue using the EXECUTE AS statement with sysadmin rights and getting the same error message.
July 31, 2009 at 9:19 am
My experience with doing restores and permission problems with "EXECUTE AS" is where the database is coming from. If it is from a different server you have SID mismatch. Try running this (not sure where I found this code... I did not develop it but it works great) - this would probably be better as a cursor-less solution, but works in a pinch. 😉
DECLARE @user SYSNAME
DECLARE @SQL NVARCHAR(300)
DECLARE cur_Users CURSOR FOR
SELECT name
FROM sysusers
WHERE islogin = 1
AND isntname = 0
-- AND NAME NOT IN ('guest', 'dbo', 'sys', 'INFORMATION_SCHEMA') -- all users in DB
AND NAME IN ('SpecificUserName') -- specific user name
ORDER BY name
OPEN cur_Users
FETCH NEXT
FROM cur_Users INTO @user
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQL = 'EXEC sp_change_users_login ' + '''' + 'UPDATE_ONE'
+ '''' + ', ' + '''' + @user + '''' + ', ' + '''' + @user + ''''
EXEC sp_executesql @SQL
FETCH NEXT
FROM cur_Users INTO @user
END
CLOSE cur_Users
DEALLOCATE cur_Users
The other problem is with database ownership. Not sure who owns the database, but the account used to restore the database is typically listed as the owner. Try this also:
use master
go
alter authorization on database::RestoredDBname to sa
The final alternative (last resort) is to remove all SP(s) and other objects owned by that user, delete and re-add the User to the database, then re-add the objects/SP(s).
Good luck
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply