Sql-server – Permissions on sys.syslogins

permissionsSecuritysql serversql-server-2008-r2

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
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!

Best Answer

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

Related Solutions

When creating views, why do users need direct object permissions if they already have the same permissions via a role

While you may have a lot of users, it would be unusual for them to require their own views. The views should be in one schema (possibly the one owning the tables) and the users should query them by either prefixing the schemaname (eg vwowner.view) or using the

ALTER SESSION SET_CURRENT_SCHEMA=vwowner

Roles are transient. You can do a SET ROLE NONE to turn them all off. You can have multiple sessions for the same account with different roles enabled. That isn't compatible with the way that Oracle handles objects (where they are either valid or they are not; they can't be valid for some sessions and not for others).

SQL Server – How to GRANT SELECT on Hidden Resource Database mssqlsystemresource

Not sure where you've stumbled along the way, but this works for me:

CREATE LOGIN permtest WITH PASSWORD = 'x', CHECK_POLICY = OFF;
GO
USE somedatabase;
GO
CREATE USER permtest FROM LOGIN permtest;
GO

According to this page, the user needs SELECT permission on sys.sql_expression_dependencies, and VIEW DEFINITION on the database.

In my experimentation, the following allowed the user to select from the view, but it returned 0 rows, because they don't have the ability to view definition (which includes dependency chains):

GRANT SELECT ON sys.sql_expression_dependencies TO permtest;

In order to actually see any relationships in somedatabase, I also had to add the following:

GRANT VIEW DEFINITION ON DATABASE::floob TO permtest;

I could not find any way to make that more granular (VIEW/DENY definition worked for individual objects, but without the database-level right, I still couldn't see any rows in the catalog view, and DENY did not prevent the objects from showing up in the catalog view nor did it even prevent me from viewing the definition). I feel like SQL Server would have a hard time resolving that granularity anyway - if you had a view that referenced a table, how should the catalog view look if you have grant on the view and deny on the table, or vice versa?

If you don't want to grant VIEW DEFINITION on the database, then create procedures that use EXECUTE AS OWNER, select (filtered?) rows from the catalog view, and give the users (and of course, that could also be a role) execute permissions on the procedure.

CREATE PROCEDURE dbo.GetDependencies
WITH EXECUTE AS OWNER
AS
BEGIN
  SELECT is_schema_bound_reference --, ...
    FROM sys.sql_expression_dependencies;
END
GO

GRANT EXECUTE ON dbo.GetDependencies TO permtest;

Best Answer

Related Solutions

When creating views, why do users need direct object permissions if they already have the same permissions via a role

SQL Server – How to GRANT SELECT on Hidden Resource Database mssqlsystemresource

Related Question