Solved

SQL 2008 R2 Connectivity / Permissions Issues

Posted on 2011-09-05
4
308 Views
Last Modified: 2012-05-12
I'm sure this will be a simple question for someone.  I have a new Sql 2008 R2 server with a database running on it.  My domain administrator account has rights to the databse.  I need to be able to create an ODBC connection from a workstation to the database.  When I log in as administrator i can connect fine.  When I log in as my domain admin account it will not let me connect.  How do I add domain users to the list of accounts than can access this database?

Thanks!
0
Comment
Question by:Mohonk
  • 2
4 Comments
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 36484820
Within SQL Server Management Studio, you need to make sure you have windows authentication enabled for the server.

You can then add users/groups to the logins for the server.

You can then add those logins to the database.
0
 

Author Comment

by:Mohonk
ID: 36484886
Where in the management studio would i grant domain users permission?
0
 
LVL 6

Accepted Solution

by:
jorgedeoliveiraborges earned 500 total points
ID: 36485085
>> Where in the management studio would i grant domain users permission?  <<
start > All Programs > Microsoft SQL Server 2008 > SQL Server Management Studio

How to: Create a SQL Server Login
http://msdn.microsoft.com/en-us/library/aa337562.aspx
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 36485765
Windows only authentication :
USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 1
GO

Open in new window


Windows and SQL authentication :
USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2
GO

Open in new window


Create domain group user with public access to a database :
USE [master]
GO
CREATE LOGIN [RICHARD-TOSH\HomeUsers] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[British]
GO
USE [ReportServer]
GO
CREATE USER [RICHARD-TOSH\HomeUsers] FOR LOGIN [RICHARD-TOSH\HomeUsers]
GO

Open in new window


These scripts are generated by the MSSMS (I'm using SQL 2008 R2 Dev Ed - excellent value for money for a sole developer).
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Possible fixes for Windows 7 and Windows Server 2008 updating problem. Solutions mentioned are from Microsoft themselves. I started a case with them from our Microsoft Silver Partner option to open a case and get direct support from Microsoft. If s…
This tutorial will walk an individual through the steps necessary to configure their installation of BackupExec 2012 to use network shared disk space. Verify that the path to the shared storage is valid and that data can be written to that location:…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

820 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