security issue

Question

hi,

can u tell me the command how to find out orphand users ASAP

Regards

bnrao

Answer 1

/* To identify them */

USE yourbase
EXEC sp_change_users_login 'REPORT'

/* To fix one of them */

USE yourbase
EXEC sp_change_users_login 'UPDATE_ONE','OrphanedLogin','CorrectLogin'

/* Usually OrphanedLogin and CorrectLogin are the same

---

Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

Answer 2

USE [TestDB];
GO
EXEC sp_helpuser;
GO

Check for the users with no Login Name

To map the user to a login, check this post: http://sudeeptaganguly.wordpress.com/2012/04/18/user-mapping-with-alter-user/

---

SKG: Please Marked as Answered, if it resolves your issue. (b:http://sudeeptaganguly.wordpress.com )

Answer 3

Hi,

To get a list of database users that have no mapping to a login:

SELECT a.name AS OrphanUserName, a.type_desc AS UserType
FROM sys.database_principals a
LEFT OUTER JOIN sys.server_principals b
ON a.sid = b.sid
WHERE b.sid IS NULL
AND a.type In ('S', 'U', 'G')
AND a.name NOT in ('sys', 'INFORMATION_SCHEMA', 'guest')

As stated above, run the following to get a list of orphaned SQL logins:

Use MYDB;
GO
EXEC sp_change_users_login 'REPORT'

---

Thanks, Andrew

Answer 4

Please run follwing query , It lists out orphaned users on the database

SELECT a.name AS OrphanUserName, a.type_desc AS UserType

FROM sys.database_principals a

LEFT OUTER JOIN sys.server_principals b

ON a.sid = b.sid

WHERE b.sid IS NULL

AND a.type In ('S', 'U', 'G')

AND a.name NOT in ('sys', 'INFORMATION_SCHEMA', 'guest')

----------------------
Thanks,Suhas Vallala

Answer 5

Hi

EXec sp_helpuser:

this is fine but it's shows the outputs out of range

means suppose total users is 10 but when we run this command it's shows 20 i want to know exatcly howmany users is there that many users need to display in output pls reply me ASAP

Regards

b nrao

Answer 6

sp_helpuser is an older command, and you better off with:

SELECT suser_sname(sid) AS Login, *
FROM   sys.database_principals
ORDER  BY type, name

---

Erland Sommarskog, SQL Server MVP, [e-mail]