Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
peva13
Starting Member
2 Posts |
 Posted - 2011-01-14 : 10:55:30
|
Hi ALL!I am new to SQL, and maybe the question is stupid, but please help.I have database1 (DB1) with stored procedure (StP) who select data from other DB2. It was work fine. But I have SmallUser with only executer rights to DB1 and without any rights to DB2. Therefore I put in StP EXECUTE AS BigUser. BigUser is full rights to DB1 and DB2. When SmallUser execute StP receive error:Msg 916, Level 14, State 1, Procedure SP, Line 11The server principal "BigUser" is not able to access the database "DB2" under the current security context.  Maybe there is another way to let SmallUser execute StPSorry for the languageRegardspeva13 |
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-01-14 : 13:27:13
|
| I think what you should do is create a SCHEMA with full authorization for both biguser and smalluser and then create the SP (Stp) under the newly created schema.PBUH |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2011-01-14 : 15:22:18
|
| EXECUTE AS in a stored procedure only allows you to execute in the context of the database user, not as server principle (login) to give it acces to another database.You can give a stored procedure access to another database by signing it with a certificate: SQL Server 2008 Books OnlineModule Signing (Database Engine)http://msdn.microsoft.com/en-us/library/ms345102(v=SQL.100).aspxCODO ERGO SUM |
|
|
|
peva13
Starting Member
2 Posts |
Posted - 2011-01-17 : 06:50:47
|
| Thanks!Regardspeva13 |
|
|
|
|
|
|
Stored procedure with EXECUTE AS