View Designer Role and Security

From: Phil Leitch (anonymous_at_discussions.microsoft.com)
Date: 02/23/04

  • Next message: anonymous_at_discussions.microsoft.com: "Re: Lock out 1 user within Enterprise Manager" 
    Date: Mon, 23 Feb 2004 14:21:06 -0800

    We have a database where all access to the data is done through stored procedures with appropriate exec. persmissions set to user defined roles. There are no permissions on any table in order to restrict access to tables directly. We also have views that can be used in custom queries by various user groups to perform ad hoc queries.

    We need to setup a user to be able to create and alter views but not to have any other sysadmin permissions. I've tried granting create view and alter view permissions to the user which works, except the user is the owner of the view and the tables are all owned by the dbo user. I can grant other users or groups select permissions on these views but selecting from the view fails because the users do not have select permissions on the tables and the table owner (dbo) is different from the view owner.

    Is there a way to do this without making the view designer user a member of the sysadmin role?

    Phil Leitch

  • Next message: anonymous_at_discussions.microsoft.com: "Re: Lock out 1 user within Enterprise Manager"

    Relevant Pages

    • Re: Getting the sa password with SQLDMO
      ... SQL Server stores passwords using a one-way hash so you can't read the ... conditional password change by attempting a connection using the expected sa ... password and, if it fails, connect using a different sysadmin role member ... member can change the sa password. ...
      (microsoft.public.sqlserver.programming)
    • Re: DTS Sucess but fails to go to next step when scheduled
      ... If == a member of the sysadmin role then log in as the account under which the ... Now try the package ... If!= A member of the sysadmin rol then log in as the proxy accunt and try the ... Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.SQLDTS.com - The site for all your DTS needs. ...
      (microsoft.public.sqlserver.dts)
    • Re: sp_change_users_login use after restore
      ... The user was a member of the sysadmin role, once I took the role out, ... In that case the login will will be 'dbo' in the ... > SQL Server MVP ...
      (microsoft.public.sqlserver.security)
    • RE: is_member
      ... If the webappman account is a member of the sysadmin role this behavior ... This posting is provided "as is" with no warranties and confers no rights. ...
      (microsoft.public.sqlserver.security)
    • Re: Check User in Role?
      ... print 'Current user''s login is NOT a member of the sysadmin role' ... Software Consultant ...
      (microsoft.public.sqlserver.programming)