Solved

SQL Server User exists without Login

Posted on 2009-05-19
5
326 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
It seems bizarre, but your article perfectly describes what I'm seeing.  Thanks!
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Resolve DNS query failed errors for Exchange
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…

762 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

11 Experts available now in Live!

Get 1:1 Help Now