Sql-server – Prevent updating table using procedure in different database

Yes, it should be working how you think. Below is an example proving this:

-- create the dummy login
create login TestLogin1
with password = 'p@$$w0rd';
go

use TestDB;
go

-- create the test user
create user TestUser1
for login TestLogin1;
go

-- create the proc to create a table
create procedure dbo.CreateTableProc
as

    create table dbo.SomeTestTable(id int);

go

-- give TestUser1 perms to execute CreateTableProc
grant execute
on dbo.CreateTableProc
to TestUser1;
go


-- execute CreateTableProc under the security context of TestUser1
execute as user = 'TestUser1';
go

exec dbo.CreateTableProc;
go

revert;
go
-- unsuccessful, permission denied

Msg 262, Level 14, State 1, Procedure CreateTableProc, Line 4
CREATE TABLE permission denied in database 'TestDB'.

-- alter the proc to execute under my security context (with CREATE TABLE perms)
alter procedure dbo.CreateTableProc
    with execute as owner
as

    create table dbo.SomeTestTable(id int);

go

-- execute CreateTableProc under the security context of TestUser1
execute as user = 'TestUser1';
go

exec dbo.CreateTableProc;
go

revert;
go
-- successful, table created

Command(s) completed successfully.

In this example, TestUser1 does not have the permissions to create the table. We see that when the original version of CreateTableProc is called, as the CREATE TABLE DDL executes under the security context of TestUser1. But then by modifying the CreateTableProc stored procedure definition and including the WITH EXECUTE AS OWNER clause, now TestUser1 can successfully call the proc and create a table because now the CREATE TABLE DDL executes under the security context of the owner (my database user, in the db_owner role).

I can guess you have the AutoClose option for database set to True. This is the default behavior when you create a database with Express Editions.

The mentioned error can occur exactly in this case. Actually the complete error message 615 states: "Could not find database ID %d, name '%.*ls'. The database may be offline. Wait a few minutes and try again." ... So it points that database could be closed during dropping.

So, go to DB properties, switch it to False and try again dropping it or use below script before dropping

ALTER DATABASE [MyDB] SET AUTO_CLOSE OFF 
GO

Many point out that is better to have AutoClose set to False. I found this article explaining a bit more about AutoClose: http://sqlmag.com/blog/worst-practice-allowing-autoclose-sql-server-databases

Small extension of the answer:

-- this works in standard SQL Server Editions, but NOT with Express Editions:
CREATE DATABASE [MyDB]
GO
DROP DATABASE [MyDB]
GO

-- this works in ALL SQL Server Editions
CREATE DATABASE [MyDB]
GO
ALTER DATABASE [MyDB] SET AUTO_CLOSE OFF 
GO
DROP DATABASE [MyDB]
GO