Login exists, member of role, access to database
-
Is_Member indicates whether the current users is a member of a role. I would like to check using SQL whether another specific user is member of a role. Also whether they have a login and whether they have access to a named database.
-
Well im not writing it for you
... but check these objects out:mydb..sysmembers: contains a row for each member of a database role
master..syslogins: hooks to mydb..sysusers over the sid column
master..sysdatabases: loop for all these
Cheers
Ck
Chris Kempster
Author of "SQL Server 2k for the Oracle DBA"
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA" -
Another way might be to use SETUSER - have to be a member of sysadmins.
Andy
-
That's great. The following might not be elegant but it seems to work OK.
Bearing in mind that I am a newbie to SQL Server, if there are any improvements I should make to this code please let me know.
CREATE FUNCTION fUserOK
(@UserId varchar(7))
RETURNS bit AS
BEGIN
DECLARE @UserOk varchar(7)
SELECT @UserOk =(
SELECT master.dbo.syslogins.name
FROM master.dbo.syslogins INNER JOIN
dbo.sysusers ON master.dbo.syslogins.sid = dbo.sysusers.sid AND
master.dbo.syslogins.name = dbo.sysusers.name COLLATE Latin1_General_CI_AS INNER JOIN
dbo.sysmembers ON dbo.sysusers.uid = dbo.sysmembers.memberuid INNER JOIN
dbo.sysusers sysusers2 ON dbo.sysmembers.groupuid = sysusers2.uid
WHERE (sysusers2.name = N'kbuser') AND (master.dbo.syslogins.name = @userId)
)
RETURN cast(len(@UserOk) as Bit)
END
... but check these objects out:Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply