Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

User/Login to database instance

Posted on 2012-04-10
5
Medium Priority
?
190 Views
Last Modified: 2012-04-18
Hello,

  I'm able to login to a database instance using windows authentication and able to access
  any objects/any database in that database instance.

  But I do not see my user id under Security -> Logins.
  Question 1:  I'm wondering how I'm ble to login to the database instance?
  Question 2: How do I verify if a user for my accound exists in the database instance or not?

Thanks!
0
Comment
Question by:sath350163
[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
  • 3
  • 2
5 Comments
 
LVL 11

Assisted Solution

by:f_o_o_k_y
f_o_o_k_y earned 2000 total points
ID: 37830127
First of all I suppose your login is member of domain or local security group that has sysadmin system role.

This is why u don't see your login.

run this :
select user_name(),suser_sname()

Open in new window


Then just check user mappings and you will see if your username is mapped but i gues that you are member of sysadmin so you don't have to be mapped as an user.

Best Regards
FooKy
0
 

Author Comment

by:sath350163
ID: 37830231
Thanks!
On running the below, I get guest for user_name() and xyz\user10 for suser_sname(). Does this mean I'm mapped to guest user? I do not see guest user anywhere in SSMS. Where should I look for it?
select user_name(),suser_sname()

Open in new window



Question 1: From SSMS, how do I find out if my user is a part of Windows group? I do not see my windows user anywhere in SSMS.

Question 2: Also will I be able to Grant select on a particular table in a db to my user using GRANT statement? I tried and it is

EXEC sp_addrolemember [test_READ_ROLE], [xyz\user10]

Open in new window

I tried and it is giving me the error: User or role 'XYZ\User10' does not exist in this database.

Thanks!
0
 
LVL 11

Assisted Solution

by:f_o_o_k_y
f_o_o_k_y earned 2000 total points
ID: 37830270
Hi to be a member of role you need to map login to user.

CREATE USER [xyz\user10]
GO
EXEC sp_addrolemember [test_READ_ROLE], [xyz\user10]
GO

Open in new window


Guest is "special" but you should see it on SSMS under database security.
Guest is for anyone who does not have user mapping in your database.

You can use also
http://msdn.microsoft.com/en-us/library/ms190369.aspx
to check login membership.

Best Regards
Fooky
0
 

Author Comment

by:sath350163
ID: 37830416
I do see Guest undet Master Database -> Security -> Users.
But from SSMS, how can I say that my windows user is logging in as Guest User?
0
 
LVL 11

Accepted Solution

by:
f_o_o_k_y earned 2000 total points
ID: 37831232
You are logged as xyz\User10 but you are accessing database with guest privileges.

I don't know any other method to check it if you are guest.
only this:
select user_name(),suser_sname()

Open in new window

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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.
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

597 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