Cross reference of User Name to Login Name

Is there a cross reference of User Name to Login Name in SQL Server
somewhere? In its system tables? It doesn't appear so? The user
table for a database doesn't contain the login name.
Does anybody know if this cross reference is accessible via system
tables?
ScottHello,
There should be definely mapping between Login and User. A user can not be
created with out a Login. This chain will not be there if you restore a
database from a diferent SQL Server machine. In this case the appropriate
Login will not be there for the database user. To recreate the login chain
you can use SP_CHANGE_USERS_LOGIN system stored procedure. See books online
for its usage.
Thanks
Hari
"scotty" <scasti1@.cox.net> wrote in message
news:1175554544.489300.144210@.d57g2000hsg.googlegroups.com...
> Is there a cross reference of User Name to Login Name in SQL Server
> somewhere? In its system tables? It doesn't appear so? The user
> table for a database doesn't contain the login name.
> Does anybody know if this cross reference is accessible via system
> tables?
> Scott
>|||How you get the login names for database users depends on
what version of SQL Server.
If this is just to check a particular database user, you can
execute sp_help_user 'YourUser' and it will return the user
name in the database as well as the login name at the server
level.
If you just want to know how the tables are related, for SQL
Server 2000 the syslogins and sysusers tables are related by
SID.
If you are on 2005, look at sys.server_principals and
sys.database_principals.
-Sue
On 2 Apr 2007 15:55:44 -0700, "scotty" <scasti1@.cox.net>
wrote:
>Is there a cross reference of User Name to Login Name in SQL Server
>somewhere? In its system tables? It doesn't appear so? The user
>table for a database doesn't contain the login name.
>Does anybody know if this cross reference is accessible via system
>tables?
>Scott|||On Apr 2, 6:57 pm, Sue Hoegemeier <S...@.nomail.please> wrote:
> How you get the login names for database users depends on
> what version of SQL Server.
> If this is just to check a particular database user, you can
> execute sp_help_user 'YourUser' and it will return the user
> name in the database as well as the login name at the server
> level.
> If you just want to know how the tables are related, for SQL
> Server 2000 the syslogins and sysusers tables are related by
> SID.
> If you are on 2005, look at sys.server_principals and
> sys.database_principals.
> -Sue
> On 2 Apr 2007 15:55:44 -0700, "scotty" <scas...@.cox.net>
> wrote:
> >Is there a cross reference of User Name to Login Name in SQL Server
> >somewhere? In its system tables? It doesn't appear so? The user
> >table for a database doesn't contain the login name.
> >Does anybody know if this cross reference is accessible via system
> >tables?
> >Scott
sp_help_user info is just what I need, but I'm having trouble
executing it from .aspx page. I'll keep trying.|||I'm somewhat new to ASP and SQL Server, but in the applications that
I've designed the connection to the SQL Server was through a connetion
string and logging in as a generic user name that we called ASP_User.
Therefore sp_help_user would not return the information that you are
looking for.
I think you're looking for information that would be contained on the
client side in the Environment variables.
System.Environment Properties:
http://msdn2.microsoft.com/en-us/library/system.environment_properties.aspx
Cheers,
Jason Lepack
On Apr 3, 11:45 am, "scotty" <scas...@.cox.net> wrote:
> On Apr 2, 6:57 pm, Sue Hoegemeier <S...@.nomail.please> wrote:
>
>
> > How you get the login names for database users depends on
> > what version of SQL Server.
> > If this is just to check a particular database user, you can
> > execute sp_help_user 'YourUser' and it will return the user
> > name in the database as well as the login name at the server
> > level.
> > If you just want to know how the tables are related, for SQL
> > Server 2000 the syslogins and sysusers tables are related by
> > SID.
> > If you are on 2005, look at sys.server_principals and
> > sys.database_principals.
> > -Sue
> > On 2 Apr 2007 15:55:44 -0700, "scotty" <scas...@.cox.net>
> > wrote:
> > >Is there a cross reference of User Name to Login Name in SQL Server
> > >somewhere? In its system tables? It doesn't appear so? The user
> > >table for a database doesn't contain the login name.
> > >Does anybody know if this cross reference is accessible via system
> > >tables?
> > >Scott
> sp_help_user info is just what I need, but I'm having trouble
> executing it from .aspx page. I'll keep trying.- Hide quoted text -
> - Show quoted text -|||On Apr 3, 9:55 am, "Jason Lepack" <jlep...@.gmail.com> wrote:
> I'm somewhat new to ASP and SQL Server, but in the applications that
> I've designed the connection to the SQL Server was through a connetion
> string and logging in as a generic user name that we called ASP_User.
> Therefore sp_help_user would not return the information that you are
> looking for.
> I think you're looking for information that would be contained on the
> client side in the Environment variables.
> System.Environment Properties:http://msdn2.microsoft.com/en-us/library/system.environment_propertie...
> Cheers,
> Jason Lepack
> On Apr 3, 11:45 am, "scotty" <scas...@.cox.net> wrote:
> > On Apr 2, 6:57 pm, Sue Hoegemeier <S...@.nomail.please> wrote:
> > > How you get the login names for database users depends on
> > > what version of SQL Server.
> > > If this is just to check a particular database user, you can
> > > execute sp_help_user 'YourUser' and it will return the user
> > > name in the database as well as the login name at the server
> > > level.
> > > If you just want to know how the tables are related, for SQL
> > > Server 2000 the syslogins and sysusers tables are related by
> > > SID.
> > > If you are on 2005, look at sys.server_principals and
> > > sys.database_principals.
> > > -Sue
> > > On 2 Apr 2007 15:55:44 -0700, "scotty" <scas...@.cox.net>
> > > wrote:
> > > >Is there a cross reference of User Name to Login Name in SQL Server
> > > >somewhere? In its system tables? It doesn't appear so? The user
> > > >table for a database doesn't contain the login name.
> > > >Does anybody know if this cross reference is accessible via system
> > > >tables?
> > > >Scott
> > sp_help_user info is just what I need, but I'm having trouble
> > executing it from .aspx page. I'll keep trying.- Hide quoted text -
> > - Show quoted text -
The data rows returned when running sp_help_user in
query_analyzer have data that would be useful in a web application.
For instance, If you could attach a user to a custom application role
in the database, then you could have a user log in with their
user_name, run the procedure, and know what application role that
specific user starts out with, because I think its associated with
their groupname, unless I'm mistaken.
The return view has a GroupName column. Can I access this value? I
run EXEC dbo.sp_helpuser 'user' in the analyzer.
I think I can successfully run the stored procedure from .aspx but I
don't think the sp_help_user procedure was defined to return these
view column values. Apologies for bad terminology I may be using.
One of the defined parameters for that procedure is @.RETURN_VALUE(int,
Return Value)? Want the GroupName column.