April 23, 2010 at 5:54 am
I need to create an audit database that only a specific user can access. I'm creating triggers and procedures to access that database and using "With Execute As" to enable them to get to the restricted database.
Even if I give the user of the restricted database SysAdmin access, I still get the message 'The server principal "BarLogin" is not able to access the database "Bar" under the current security context.' (in the example below)
I'm sure I'm missing something simple, but I'm open to suggestions (or an alternative approach).
This is a stripped down example of the code that I'm trying to run.
Use Master
GO
Drop database foo
go
drop database bar
go
Create database Foo
GO
If not exists (Select * from sys.sql_logins where name = 'BarLogin')
BEGIN
CREATE LOGIN BarLogin WITH PASSWORD = 'BarToTheBone!'
END
GO
Create Database Bar
alter authorization on DATABASE::bar to BarLogin
GO
USE Foo
EXEC sp_adduser @loginame = 'BarLogin'
,@name_in_db = 'BarLogin'
,@grpname = 'db_datareader'
GO
USE Bar
If NOT Exists(Select * from Bar.sys.Schemas where Name = 'BarLogin')
BEGIN
-- Apparently, create schema must be the first line in the batch - but that's another question.
Declare @SQLCmd Varchar(Max)
Set @SQLCmd = 'Create Schema BarLogin'
Exec (@SQLCmd)
END
Use Bar
Create Table BarLogin.TestTable
(
PKGUID uniqueidentifier primary key default newid(),
TestValue varchar(100)
)
GO
Use Foo
GO
Create Procedure TestBar
WITH EXECUTE AS 'BarLogin'
As
BEGIN
Insert into Bar.BarLogin.TestTable(TestValue)
Values ('Success!')
END
GO
Exec TestBar
April 23, 2010 at 7:26 am
After lots of research (and many dead-ends), the solution is to "Alter database FOO Set Trustworthy ON"
It does not seem to be necessary to make BAR Trustworthy as well.
Of course, this does open up some additional security holes. Any feedback on the potential issues this would create?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply