EasyCFM.COM ColdFusion Forums / MS SQL Server Related Issues / Configuring SQL 2008 Login/User

   Reply to Discussion | New Discussion << previous || next >> 
Posted By Discussion Topic: Configuring SQL 2008 Login/User

book mark this topic Printer-friendly Version  send this discussion to a friend  new posts last

hmmThatsWeird
08-29-2012 @ 11:30 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Member
Posts:
Joined: Aug 2012

Can someone help deal with a new SQL 2008 R2 setup?

I have recently stood up Win2008-Server/CF10/SQL2008R2 box to replace my Win2000-Server/CF6/SQL2000 box.  Yay, me!

I've made a backup of my SQL2008 production DB and restored it to the new development server, but I think there's a problem in how I've setup login/user/schema.

I've set the DSN up and it verifies, but when I hit the site, I get the CF error: "[Macromedia][SQLServer JDBC Driver][SQLServer]The INSERT permission was denied on the object 'logins', database 'sbc', schema 'dbo'. "  

In the DSN setup, I'm using the login 'safetybasecamp'.  It's user in SQL is 'safetybasecamp'.  It's schema is 'safetybasecamp'.  All the tables are owned by 'dbo'.

It's working in the shared production environment with the same DSN setup.  I've tried to configure the safetybasecamp login to point to the dbo user, but that fails.  SQL2000 was much easier Frown   I'm guessing this is something obvious to an experienced SQL2008 admin.

Any help is greatly appreciated.  Thanks in advance.

hmmThatsWeird
09-03-2012 @ 8:10 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Member
Posts:
Joined: Aug 2012

Maybe I can simpfly my question.  It is still about logins/users/schema.

My production server  and my development server both have a login named "User1".

My production server maps the login to user "dbo" with a schema of "dbo"

After I backup the production database and restore it to my development server, I try to map "User1" to the database with user "dbo" and schema "dbo" but I get the following error:
------------------------------
TITLE: Microsoft SQL Server Management Studio

Create failed for User 'dbo'.  (Microsoft.SqlServer.Smo
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

User, group, or role 'dbo' already exists in the current database. (Microsoft SQL Server, Error: 15023)
------------------------------

How do I get the dev server to mirror the production server and use the dbo user?

This message was edited by hmmThatsWeird on 9-3-12 @ 8:11 AM

Webmaster
09-05-2012 @ 1:03 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Administrator
Posts: 4542
Joined: Jan 2002

Are you using a particular user on the datasource? (A
user/pass)... if so... does that user have permissions on
that database?

dbo will only work if you are using "sa" as the
datasource user.. if you have different credentials,then
you need to make sure that that user is setup as a dbo
also..



Pablo Varando
Senior Application Architect
EasyCFM.COM, LLC.

904.483.1457 \\ mobile
webmaster@easycfm.com \\email

\m/ (>.<) \m/
--- rock on ---

VanessaMeacham
09-17-2012 @ 11:38 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Member
Posts:
Joined: Sep 2012

I think you have to add a server login and database user that map a Windows Group to a SQL 2008 R2 instance using the following script, with the names changed for anonymity:

USE master
go
CREATE LOGIN [DOMAIN\AppUsers] FROM WINDOWS
WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
go
USE AppDb
go
CREATE USER [DOMAIN\AppUsers] FOR LOGIN
[DOMAIN\AppUsers]
go
EXEC sp_addrolemember N'db_owner', N'DOMAIN\AppUsers'
go


Website Designed and Developed by Pablo Varando.