Solved

User/Login to database instance

Posted on 2012-04-10
5
182 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

772 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