Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Server Login Database User Mapping Expand / Collapse
Author
Message
Posted Thursday, September 18, 2008 1:39 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, November 01, 2013 5:42 AM
Points: 60, Visits: 165
Hi,

Please let me know where to find information regarding User-Login Mapping inside an instance of SQL Server 2005. As part of a SQL Server Consolidation I am moving dbs from n Sql Server instances and also transferring logins using sp_heaxadecimal and sp_help_revlogin scripts. I want to make sure that the original User-Login mapping is intact.
Post #571524
Posted Thursday, September 18, 2008 2:00 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 6:56 AM
Points: 1,330, Visits: 1,434
Hello,

The DB level Catalogue View sys.database_principals will give you details regarding DB Users (per DB). This can be jopined with the Server level (Login) information contained in sys.server_principals

Regards,

John Marsh


www.sql.lu
SQL Server Luxembourg User Group
Post #571537
Posted Thursday, September 18, 2008 2:29 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, November 01, 2013 5:42 AM
Points: 60, Visits: 165
What to join these tables on? How to find the User-Login Mapping from these tables?

TIA
Post #571552
Posted Thursday, September 18, 2008 2:40 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 6:56 AM
Points: 1,330, Visits: 1,434
Hello Again,

sys.server_principals gives you the Server Logins and sys.database_principals gives you the DB Users in the selected DB.

Joining on the sid gives you the mapping (for one DB) e.g. Select * From sys.server_principals ssp Inner Join sys.database_principals sdb On (sdb.sid = ssp.sid)

Regards,

John Marsh


www.sql.lu
SQL Server Luxembourg User Group
Post #571556
Posted Thursday, September 18, 2008 3:09 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, November 01, 2013 5:42 AM
Points: 60, Visits: 165
Hello Sir,

Yes, that works Precisely .


Thanks a lot :)
Post #571571
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse