Solved

User/Login to database instance

Posted on 2012-04-10
5
183 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
  • 3
  • 2
5 Comments
 
LVL 11

Assisted Solution

by:f_o_o_k_y
f_o_o_k_y earned 500 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 500 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 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

920 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