Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

A:

USE master
GO
GRANT VIEW ANY DEFINITION TO "<username>"

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

B:

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

Error - Definition privilege is not compatible with object.

C:

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

Same as B

D:

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!

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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

share|improve this answer
    
Great! Works like a charm! Is there a similar way to retrieve if a role exists? ATM, I access sys.database_principals! –  Nico Jun 20 '13 at 9:15
1  
Use DATABASEPRINCIPAL_ID: msdn.microsoft.com/en-us/library/ms187319.aspx –  gbn Jun 20 '13 at 9:21
    
Thank you a lot! –  Nico Jun 20 '13 at 10:19

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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