January 5, 2011 at 4:05 pm
Hello all,
I've created a login and user for my database "dbParent".
In this database I have a stored procedure that modifies information in "dbParent.dbo.Table1" and also sends certain information to "dbNode.dbo.TableX".
If I execute this procedure as an administrator/owner it doesn't give me any problems; but if I use the custom user I made (which it should, since that user is going to have certain privileges) it throws me an error where the "remote insert" part starts [The server principal "myuser" is not able to access the database "-" under the current security context].
I read an article about creating certificates in both databases so I can cross-database but in my case the insert part isn't working..
Should it work? or am I doing something wrong?
I'm trying to follow the following example: http://social.msdn.microsoft.com/Forums/en/sqlsecurity/thread/319601a0-196a-4422-ad34-044ff7ad4363
Thanks for your help and time!
January 5, 2011 at 4:20 pm
or I was thinking... it should be easier if I grant access to dbNode.TableX insert and/or update
what would be the best approach?
January 5, 2011 at 4:22 pm
Please post the code for the insert that's failing. The "-" in the error looks a little odd. Have you confirmed that the user has insert rights on the table in the other database? Has cross-database ownership chaining been enabled?
January 6, 2011 at 8:33 am
Hi David, thanks for your response.
The "-" it was meant for my database name
The user doesn't have privileges to that table yet. At first I thought that if I only granted access to my stored procedure it would be ok but then I realized it needs more work...so I tried creating certificates but I believe that's not the right approach for my case?
My scenario is:
In database1 client user creates "empresa" items. When "empresa" is created, a new database is generated (including tables, procedures and now user). Now some client user interacts with database1 values and I need to update in the "empresa's" database that was modified. Since that login/user that cliente have used doesn't have admin rights..it fails when is trying to execute a procedure that contains: interaction with main database and interaction with that "empresas's".dbo.N database table.
If I grant insert/update access to the "empresa's".dbo."N" table shouldn't be a problem with cross-database access?
or do I need to do something else?
Sorry for my lack of english and I hope i myself a little bit clear
Thanks for your help and time
Edit: here's the code where it fails when user executes procedure with the login/user with certain access:
SET @query =
'UPDATE [' + @StrBaseDatos + '].dbo.TblAdministracionLlave
SET Valor = loc.Valor
FROM TblAdministracionLlave loc
INNER JOIN [' + @StrBaseDatos + '].dbo.TblAdministracionLlave base ON base.IntTipo = loc.IntTipo AND loc.GuidEmpresa = @GuidEmpresa
INSERT [' + @StrBaseDatos + '].dbo.TblAdministracionLlave(IntTipo, Valor)
SELECT IntTipo, Valor FROM TblAdministracionLlave loc WHERE loc.GuidEmpresa = @GuidEmpresa AND loc.IntTipo NOT IN (SELECT IntTipo FROM [' + @StrBaseDatos + '].dbo.TblAdministracionLlave)'
EXEC sp_executesql @query, @params, @GuidEmpresa=@GuidEmpresa
January 7, 2011 at 11:27 am
Here is a great article by Erland Sommarskog, SQL Server MVP.
His coverage of permissions is excellent and very complete. I think it should cover what you're trying to do. EXECUTE AS has issues with cross-database permissions which he explains nicely.
http://www.sommarskog.se/grantperm.html
Cheers,
-DW
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply