Solved

User/Login to database instance

Posted on 2012-04-10
5
185 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
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.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

813 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

16 Experts available now in Live!

Get 1:1 Help Now