January 13, 2005 at 2:37 am
Hi Folks,
I'm trying to grapple with DB permissions across multiple DBs. In particular, I have two databases
1) Database1 - which contains a data input table used by a job processing application within our company. Jobs are added to the ImportData table, and then - by way of a trigger - used to generate jobs in the client database.
2) Database2 - which contains the client specific data associated to each job (one DB per client)
I have created 2 logins
- DBOwner1 - which is the dbowner of Database1
- DBOwner2 - which is the dbowner of Database2
In Database2 I have a SP called AddJob which is used to add new jobs to the client database. It is quite complex and involves SELECT, INSERT, UPDATE and EXECUTE statements.
I want the DBOwner1 login to have the minimum possible permissions associated to the execution of the Database2.AddJob SP, including SELECT permissions.
By successive iterations, what I have come up with is the following:
USE Database1
EXEC sp_grantdbaccess N'DBOwner2', N'DBOwner2'
GO
EXEC sp_addrolemember N'db_owner', N'DBOwner2'
GO
USE Database2
EXEC sp_revokedbaccess 'DBOwner2'
EXEC sp_grantdbaccess N'DBOwner2', N'DBOwner2'
GO
--Added to allow SELECT statements
EXEC sp_addrolemember N'db_datareader', N'DBOwner2'
GRANT EXECUTE ON AddJob TO DBOwner2
GRANT INSERT ON Component TO DBOwner2
GRANT INSERT ON ComponentCaption TO DBOwner2
GRANT INSERT ON ComponentJobs TO DBOwner2
GRANT INSERT ON ComponentLeaf TO DBOwner2
GRANT UPDATE ON Component TO DBOwner2
GRANT UPDATE ON ComponentJobs TO DBOwner2
GO
My questions are:
a) Is this the best way to obtain the required result - apart from custom DB roles in MasterDB
b) Why do I NOT need to Grant EXECUTE permissions on the SPs called from within Database2.AddJob
Thanks for your help
Mauro
January 13, 2005 at 3:33 am
Sorry, forgot one thing. It appears that I only need to grant permissions to the tables againt which I am executing Dynamic SQL, as shown below.
SET @newsql = N'INSERT INTO ' + @leafTable + N'(' + @leafprefix + N'HierarchyId, ' + @leafprefix + N'LevelId,' + @leafprefix + N'NodeId, ' + @leafprefix + @Stable + N'Id)' + N'VALUES (@hierarchyID,@levelID,@nodeID,@autoId)'
EXECUTE sp_executesql @newsql, N'@hierarchyId int, @levelId int, @nodeId int, @autoId int', @hierarchyId , @levelId, @Id, @autoId
Are there special permissions required for sp_executesql?
Thanks Again
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply