?
Solved

SQL 2008 R2 - Create a Database

Posted on 2011-03-08
14
Medium Priority
?
821 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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
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 70

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

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

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

This article explains how to install and use the NTBackup utility that comes with Windows Server.
Resolving an irritating Remote Desktop connection that stops your saved credentials from being used.
This tutorial will walk an individual through configuring a drive on a Windows Server 2008 to perform shadow copies in order to quickly recover deleted files and folders. Click on Start and then select Computer to view the available drives on the se…
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…

771 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