Solved

SQL 2008 R2 - Create a Database

Posted on 2011-03-08
14
812 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 500 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 69

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
extended monitor print screen 8 31
SQL Server 2012 r2 - Sum totals 2 23
convert null in sql server 12 31
GPO not showing IE10 in GP Preferences 14 38
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

786 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