Sql-server – SQL Server Permission Denied Error When I’m DBOwner

permissionsSecuritysharepointsql-server-2008-r2

So I created an account, gave him dbcreator and securityadmin privileges and then through that account tried to run CREATE TABLE on a database that I had created and now owned, but was getting a permission denied error. Elevating the account to Sysadmin fixed the problem, but is there a way to do this without endowing this account with the Almighty sysadmin privilege?

This is on SQL Server 2008 R2. This database and table was created via SharePoint 2010 (but applies more to SQL than SharePoint I think).

Best Answer

From what I see you created a server login with server roles (DBCreator and SecurityAdmin are server roles). But this doesn't mean it already has privileges on an existing database.

What you need to do is to create a user in that database and map it to the login:

use your_db
create user x for login y
exec sp_addrolemember 'db_owner', 'x'

This will assign your new user the database role db_owner to the database. Now you should be able to create tables with the login. You can try also other roles, if you don't need to be the db owner.

Related Question