Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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).

share|improve this question
2  
What other privileges did you assign besides that? Security is a fine-grained beast, and is not meant to be applied in broad sweeping strokes. –  jcolebrand Jun 13 '11 at 22:24
add comment

3 Answers

up vote 3 down vote accepted

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.

share|improve this answer
add comment

While all good answers, the issue here lied a bit more with SharePoint, the account given was not properly configured and mapped to all the SharePoint databases. Giving the account dbowner to all SharePoint db's like it has fixed the issue, thanks all for trying!

share|improve this answer
1  
My answer did exactly that, giving you the script to do it. Stan's answer gave you another solution, giving the lowest enough permission to create tables. What exactly is different in your answer? :-) –  Marian Jun 14 '11 at 20:30
    
actually I just realized that, you're right :-) I just did in a GUI what you did in a SQL Statement...ok you can have the answer –  tekiegreg Jun 14 '11 at 21:28
add comment

Try the following:

USE db_name_that_you_created;
GRANT CREATE TABLE TO db_user_that_needs_to_create_table;
GO
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.