I've got the following scenario: ( loggend in on SQL-Server with full admin-privileges )

Creating LogIn, User, Role and associate them.

IF NOT EXISTS( SELECT "name" FROM "master"."dbo"."syslogins" WHERE "name" = 'ocmb_admin' ) BEGIN CREATE LOGIN ocmb_admin WITH PASSWORD = 'pw', CHECK_POLICY = OFF; END

IF NOT EXISTS ( SELECT 1 FROM "sys"."database_principals" WHERE "name" = N'ocmb_admin' ) BEGIN CREATE USER ocmb_admin FOR LOGIN ocmb_admin;END

if not exists ( select 1 from "sys"."database_principals" where "name" = N'ocmb_grp_admin' ) begin CREATE role "ocmb_grp_admin"; end

EXECUTE sp_addrolemember N'ocmb_grp_admin', N'ocmb_admin';

Works fine. Needed permissions on objects for created login ( ocmb_admin ) are granted in separate queries, not completely neccessary for this question.

Now, logging in with this login I have to query sys.syslogins which is possible but only returns data for current login and sa.

I tried several ways to grant explicit permissions to the group ( ocmb_grp_admin ), but none of them leaded to the fact, that I can query the complete content of the view.

I need to determine, whether some logins exist while logging in with an random account, which is assigned to the prior created role 'ocmb_grp_admin'. Permissions can and SOHULD be granted to this role, to avoid having to grant them for each new user.

What I tried so far:


USE master

This one works, but is completely uncomfortable, because every user has to be added separately.


USE master
GRANT VIEW DEFINITION ON master.sys.syslogins TO ocmb_admin

Error - Definition privilege is not compatible with object.


USE master
GRANT VIEW DEFINITION ON master.sys.syslogins TO ocmb_grp_admin

Same as B


USE master GO GRANT VIEW DEFINITION ON LOGIN::ocmb_admin TO ocmb_admin

Can be executed, if ocmb_admin is also created on master-db, but does not have any effects on querying.

Thanks for hints!

1 Answer 1


You can use SUSER_ID which will give you the SID from sys.server_principals regardless of metadata visibility

Also note that syslogins has been deprecated since SQL Server 2005

  • Great! Works like a charm! Is there a similar way to retrieve if a role exists? ATM, I access sys.database_principals!
    – Nico
    Commented Jun 20, 2013 at 9:15
  • 1
    Use DATABASEPRINCIPAL_ID: msdn.microsoft.com/en-us/library/ms187319.aspx
    – gbn
    Commented Jun 20, 2013 at 9:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.