CODEWITHSUNDEEP
securitysql-server-2008
Magnus Smith
Magnus Smith

Reputation: 5963

Need permission for stored proc on database A to select from table on database B

I have two databases on the same SQL2008 server, and many stored procedures on one database (call it A) need to access the tables on the other database (B). I made sure the SQL user on A had permission to EXEC the procedure on A, but I also ran DENY ALL ON mytablename to attempt a touch of security. I am being dumb somewhere though...

I get this error when running myproc on A:
Microsoft OLE DB Provider for ODBC Drivers error '80040e09'
[Microsoft][ODBC SQL Server Driver][SQL Server]The SELECT permission was denied on the object 'mytablename', database 'B', schema 'dbo'.

Whilst I could GRANT SELECT access to the tables in question this isn't really the solution, is it?! I'd like the procs on either database to have SELECT access to the tables, without the user being able to SELECT from the tables directly.

Database A has:
Table - mytablename
User - myuserA (member of db_datareader, linked to a login called 'bob')

Database B has:
Proc - myproc (which SELECTs from A..mytablename)
User - myuserB (member of db_datareader, linked to same login 'bob')

I realise we really need a DBA, but we only have two employees!

Upvotes: 0

Views: 194

Answers (1)

HardCode
HardCode

Reputation: 6756

By running DENY ALL ON table, you are effectively denying permissions regardless if you explicitly grant SELECT. DENY trumps all.

What you want to do is then create views on Database B, and grant SELECT to the users who can run the SP on Database A. Security will propagate from DB A to DB B when the SP on DB A is run.

Upvotes: 1

Related Questions