[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SQL Server User exists without Login

Posted on 2009-05-19
5
Medium Priority
?
384 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 27

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 27

Accepted Solution

by:
Chris Luttrell earned 1000 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Uncontrolled local administrators groups within any organization pose a huge security risk. Because these groups are locally managed it becomes difficult to audit and maintain them.
Active Directory can easily get cluttered with unused service, user and computer accounts. In this article, I will show you the way I like to implement ADCleanup..
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 …
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

656 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