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
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 GO GRANT VIEW ANY DEFINITION TO "<username>"
This one works, but is completely uncomfortable, because every user has to be added separately.
USE master GO GRANT VIEW DEFINITION ON master.sys.syslogins TO ocmb_admin
Error - Definition privilege is not compatible with object.
USE master GO 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!