What "login" am I using? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

What "login" am I using?

Author

Topic

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2010-09-14 : 17:24:54

I have seen some ways to tell what user you are on a database, and I have also seen some ways to show what you logged in as - your active directory account name. But when I am logged in, even though I can show my domain login information (MyDomain\MyAccount), I cannot figure out how to:
1) Show the AD group account that actually logged in, AND
2) What login I used when looking at the SQL Server logins. When I try to show that, I just get my personal domain\login.

I actually think I know which one I am using, because I see the SA SQL Server login account, which I know I am not using, and another one which when I look at the logins, is a Windows group account. But if there are more than one Windows group account login, I can't tell which one I have used. I have tried these following queries, NONE of which shows the Login in the security section of the server object browser:

SELECT SUSER_ID() SUSER_ID
SELECT SUSER_NAME() SUSER_NAME
SELECT SUSER_SID() SUSER_SID
SELECT SUSER_SNAME() SUSER_SNAME
SELECT USER [USER]
SELECT USER_ID() USER_ID
SELECT USER_NAME() USER_NAME
SELECT USER_SID() USER_SID
SELECT CURRENT_USER [CURRENT_USER]
SELECT @@CONNECTIONS CONNECTIONS
SELECT DEFAULT_DOMAIN() DEFAULT_DOMAIN
SELECT HOST_ID() HOST_ID
SELECT HOST_NAME() HOST_NAME
SELECT ORIGINAL_LOGIN() ORIGINAL_LOGIN
SELECT PUBLISHINGSERVERNAME() PUBLISHINGSERVERNAME
SELECT SCHEMA_ID() SCHEMA_ID
SELECT SCHEMA_NAME() SCHEMA_NAME
SELECT SESSION_USER [SESSION_USER]
SELECT SYSTEM_USER [SYSTEM_USER]

This should be basic information, and if I am going to create and administer a database, I should at least be able to view my own login info. I know many of these queries are unrelated, but I wanted to show what I have looked at. If anything, they give me my personal domain\account, but not the true login as I can script from SSMS.

Duane

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-14 : 17:27:54

This may help you: http://weblogs.sqlteam.com/tarad/archive/2008/06/23/How-to-get-information-about-Windows-users-and-groups-using.aspx

That is what I use to get information about Windows logins and groups.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-14 : 17:28:30

Do you have sysadmin privileges on the database server? You'll need it to run the queries from my blog post.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2010-09-14 : 18:15:40

Thank you again for your response. I have sysadmin on a couple of them, not nearly all. So, I am tried it on the ones I do, and that answered my question. I wish there was a way on the others at least to view my own, but it is not really that important. I was just trying to understand how things work.

Duane

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources