Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL 2008 R2 - Create a Database

Posted on 2011-03-08
14
Medium Priority
?
829 Views
Last Modified: 2012-06-27
I have an application that ask a user some details and then creates a database in SQL 2008 R2.

I am aware of security issues when allowing an account on the domain as SYSADMIN in SQL. Therefore, is it possible to create \ read \ write to a database not using SYSADMIN account ? Remember I am using a standard domain user account to create the database. When I add the user account to SYSADMIN in SQL, the database is created I need to all the user account to create \ read \ write without sysadmin privledges.

Is there a way I cna create a SERVER ROLE, as the privledges for what I require seem only to be in the SYSADMIN group !!
0
Comment
Question by:CaussyR
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 4
14 Comments
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35072414
If you want to allow login to read and write in whole db you can use db_datawriter database build in role.
You can restrict permissions to different kind of objects. You can also create your own db role add apropriate permissions to role ad then add your logins (exactly db users) to that role.
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35072511
I may missed your point, you wanted server wide permission to also create databases?
To add server wide priviledge you can add login to dbcreator server role. Login with that permission is able to create databases in which becomes dbo with full access.
0
 

Author Comment

by:CaussyR
ID: 35072714
I added the user account to the DBCREATOR , see attached file for errror.
CropperCapture-12-.Bmp
0
Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35072891
You need to grant access to that database for your login. By revoking sysadmin priviledge you revoked all other priviledges which now needs to be reapplied.
For test add user mapped to that login in test_sage200_RCWH db to db_datareader role :)
0
 

Author Comment

by:CaussyR
ID: 35072959
The database uses a domain account in the SQL security. The account should create and populate the database. I can only add Db-reader when the database is created.
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35073253
Login in dbonwer server role will have dbowner permission in each database created by it, but in all other databases you need to specifically grant permissions.
Error posted above states that login failed due to insufficient priviledges to database.
Please verify that that particular login has access to your database.
0
 

Author Comment

by:CaussyR
ID: 35074320
The user account that is being used, is creating the database.  That's why this is strange, when I add the same user account to create the database in the SYSADMIN group it works as soon as I take the user out of SYSADMIN and only make them a part of DBCREATOR you see the above error.
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35079656
Ok. Which database for this login is it's default database?
Does it properties tab look like in the attached image?
Check your ODBC connection properties and look for database to connect to.

You need to grant access in the database which login is trying to connect to.
Sysadmins have all server wide priviledges, dbcreators don't. So you need add them access to existing database(s). But in new ones created by them sufficient rights are granetd by default.   login
0
 

Author Comment

by:CaussyR
ID: 35082336
I have checked the ODBC settings and this was connecting to TEST_DB, so I gave DBCREATOR to the service account.  But I still get the login error.
0
 
LVL 14

Accepted Solution

by:
Daniel_PL earned 2000 total points
ID: 35082417
DBCREATOR will not give you permission in databases.
Look at the following screen, in login properties you have user mapping tab. There you should add apropriate acces to the database(s).
You need to mark map option for each database you want to grant access, in default account will be in public role. You can choose to pick up fixed database role or to be more specifically you can switch to database security tab in object explorer and there specify access to database objects.
Here you can read about fixed database roles.
http://www.mssqltips.com/tip.asp?tip=1900
Clipboard01.jpg
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35082430
Please let me correct and clarify:
DBCREATOR will not give your account permission in existing databases.
In each new database created by login it will be mapped to that database with db_owner role.
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35148863
Hi,
Is your problem resolved?
0
 
LVL 71

Expert Comment

by:Qlemo
ID: 35446328
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question