Solved

SQL Server User exists without Login

Posted on 2009-05-19
5
329 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

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…
Disabling the Directory Sync Service Account in Office 365 will stop directory synchronization from working.
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…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

815 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