Solved

SQL Server User exists without Login

Posted on 2009-05-19
5
327 Views
Last Modified: 2012-05-07
I'm working with a SQL Server database with the following security structure:

domain users --> domain groups --> SQL logins --> database users --> database roles --> permissions.  

The database is associated with a municpal utility called CarteGraph.  CarteGraph occasionally creates "on-the-fly" views to support GIS functionality.  Here's the security construct associated with the views.

domain users --> MISSING LOGINS --> database users --> database schema --> view ownership.

CarteGraph generates the views using scripts and it may have generated the associated database users and database schema using scripts as well.  The missing logins, however, make no sense.  I looked at the properties for one of the database logins named COBNTOMAIN\bakes1.  The properties dialog shows it to be associated with a SQL Server login named COBNTOMAIN\bakes1.  Yet, if I look at the list of SQL logins, COBNTOMAIN\bakes1 is simply not there.  

 
0
Comment
Question by:jdana
  • 2
  • 2
5 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24425620
from BOL

Mapping a database user to a new SQL Server login
In the following example, a database user is associated with a new SQL Server login. Database user MB-Sales, which at first is mapped to another login, is remapped to login MaryB.

 Copy Code
--Create the new login.
CREATE LOGIN MaryB WITH PASSWORD = '982734snfdHHkjj3';
GO
--Map database user MB-Sales to login MaryB.
USE AdventureWorks;
GO
EXEC sp_change_users_login 'Update_One', 'MB-Sales', 'MaryB';

 
0
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 24425692
if you can look at the users on the database at the database level but do not see the login for that user at the server security level, then you are probably connected with dbowner permission on the database but do not have either sysadmin or secruityadmin permissions at the server level.
0
 

Author Comment

by:jdana
ID: 24427160
While I spelled out the problem, I didn't articulate the question.  Here it is: Is a database user without a corresponding SQL Server login ever legitimate?

CGLuttrell, Good idea, but my account rights are not an issue.  My login is in sysadmin.
0
 
LVL 26

Accepted Solution

by:
Chris Luttrell earned 250 total points
ID: 24427213
Yes it is legitmate to have a user with no login, usually for owning objects and security scenarios.  See http://blogs.msdn.com/raulga/archive/2006/07/03/655587.aspx and http://download.microsoft.com/download/8/5/e/85eea4fa-b3bb-4426-97d0-7f7151b2011c/SQL2005SecBestPract.doc for some insight on this. (the second is a doc download from MS)
0
 

Author Closing Comment

by:jdana
ID: 31583186
It seems bizarre, but your article perfectly describes what I'm seeing.  Thanks!
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

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article, we will see the basic design consideration while designing a Multi-tenant web application in a simple manner. Though, many frameworks are available in the market to develop a multi - tenant application, but do they provide data, cod…
This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles from a Windows Server 2008 domain controller to a Windows Server 2012 domain controlle…

919 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now