0

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?

Triynko
  • 18,766
  • 21
  • 107
  • 173
  • I would first check for any DATABASE-level triggers that are attached to the MODEL database – RichardTheKiwi Jan 25 '11 at 19:17
  • There are no triggers on the "model" database, because in SSMS the node at [SQLServer]->[Databases]->[System Databases]->[model]->[Programmability]->[Database Triggers] is empty. – Triynko Jan 25 '11 at 19:26

1 Answers1

1

"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

gbn
  • 422,506
  • 82
  • 585
  • 676
  • The login 'foo' created the database, so they already have the 'db_owner' role in the database. Specifically, the user 'dbo' in the database has the 'db_owner' role and user 'dbo' is associated with login 'foo'. Like I said, since 'foo' created the database, shouldn't 'foo' be able to create tables in it, since they own it completely (at both the server-level and have the db_owner role in it)? – Triynko Jan 25 '11 at 19:57
  • I'm really trying to figure out or get ideas about what else could be preventing a script from creating tables in a database the user created and owns. – Triynko Jan 25 '11 at 20:03
  • @Triynko: CREATE DATABASE *does not set* the user or roles in the database: which is why I gave this answer. Relying on the dbo mapping is unreliable: set permissions explicitly – gbn Jan 25 '11 at 20:07
  • This is not making any sense. 'dbo' user is mapped to 'foo' login. Login 'foo' cannot create a user named 'foo' in the database, because login 'foo' is already associated with user 'dbo'. The error is "The login already has an account under a different user name. (Microsoft SQL Server, Error: 15063)". The mapping IS reliable. If user 'dbo' has 'db_owner' role and is mapped to login 'foo', then login 'foo' is owner and functions as user 'dbo' with all permissions of 'dbo', which as 'db_owner' is ALL PERMISSIONS. – Triynko Jan 25 '11 at 21:29
  • If a user has the 'db_owner' role, and the user is mapped to the current login, then doesn't the current login, being mapped to a user that is 'db_owner' have permission to do anything at all in the database? Seems pretty straightforward and reliable to me. – Triynko Jan 25 '11 at 22:30
  • I may end up deleting this post, because I got some more info and it seems the problem is entirely unrelated to permissions. The script is just trying to do something stupid like create a user for a login that doesn't exist. Geez. But I'd still like to clarify this whole thing about being 'db_owner' isn't enough.... – Triynko Jan 25 '11 at 22:32