CODEWITHSUNDEEP
securitysql-server-2008stored-procedurespermissionsdbo
Triynko
Triynko

Reputation: 19204

Why can't a user create tables in a database (they own) with a script? SP permission issue perhaps?

I granted a user permission to create databases. They were able to create a database, which they now own, but they are getting errors when running a script to create the tables. I don't have a lot of information at this point (sorry!), so I can't diagnose it myself, but perhaps someone more experienced in database permissions could help.

I'm assuming they are using some built-in stored procedures and it's a some kind of permission issue. I assumed that if they can create/own a database, they can do whatever they want to it, but there must be something they don't have access to.

Any advise? Do I need to grant them permissions beyond "create database"? Is there some common/standard set of stored procedures they should have access to? Do they need access to "master" database?

Upvotes: 0

Views: 2376

Answers (1)

gbn
gbn

Reputation: 432261

"Owning" the database at the server level is different to being "db_owner" in the database

After creating the database, run this

CREATE USER foo FOR LOGIN foo
EXEC sp_addrolemember 'db_owner', 'foo'

See CREATE USER for more info

Edit: Relying on any owner to dbo mapping from CREATE DATABASE is unreliable: set permissions explicitly or use sp_changedbowner

Upvotes: 1

Related Questions