locked
Granting select privs to Sql Server user for sql_logins (system) table RRS feed

  • Question

  • User1047454725 posted

    I have written a stored proc in SS2008 which queries the following views via an ASP.Net SqlCommand:

    [<database>].[sys].[sysusers], [<database>].[sys].[sql_logins] and [<database>].[sys].[database_principles]

    When I execute the proc logged in as a Windows user which has DBO privs, the results are correct. But when I execute it logged in as a SQL Server user, the results are incorrect. This is because that user doesn't have Select privs pn the views. So off I went and ran the following successfully logged in as the DBO user in the specific database:

    GRANT SELECT ON [CaseSchedule].[sys].[sysusers] TO sitting_sched_user

    GRANT SELECT ON [CaseSchedule].[sys].[database_principals] TO sitting_sched_user

    The following resulted in an error however:

    GRANT SELECT ON [CaseSchedule].[sys].[sql_logins] TO sitting_sched_user

    Permissions on server scoped catalog views or system stored procedures or extended stored procedures can be granted only when the current database is master.

    So I changed the database to master and upon re-execution of:

    GRANT SELECT ON [sys].[sql_logins] TO sitting_sched_user

    the following error resulted:

    Cannot find the user 'sitting_sched_user', because it does not exist or you do not have permission.

    The user is present as a login at the database level and the user I am executing as has DBO privs. Any insight on where I can troubleshoot would be greatly appreciated.

    Best Regards.

    Monday, November 20, 2017 10:11 PM

All replies

  • User991499041 posted

    Hi Kj27,

    Cannot find the user 'sitting_sched_user', because it does not exist or you do not have permission.

    Make sure that you have user mappings for your login in the master table. 

    You can simply tick 'Map' for the database master under 'User Mapping' in the properties for the login 'UserName'.

    Regards,

    zxj

    Tuesday, November 21, 2017 2:02 AM