• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 394
  • Last Modified:

SQL Server User exists without Login

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
jdana
Asked:
jdana
  • 2
  • 2
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
Chris LuttrellSenior Database ArchitectCommented:
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
 
jdanaAuthor Commented:
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
 
Chris LuttrellSenior Database ArchitectCommented:
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
 
jdanaAuthor Commented:
It seems bizarre, but your article perfectly describes what I'm seeing.  Thanks!
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now