Sql Server Programming :: CREATE VIEW and permissions


	CREATE VIEW and permissions I'd like all users to be able to create views that are owned by dbo. I have already granted them CREATE VIEW ... but what permissions do they need so they can create them so they are owned by dbo ? Don't you just prefix dbo in the view name? e.g. CREATE VIEW dbo.MyView AS SELECT * FROM Something "Michael Tissington" <mtissing@newsgroups.nospam> wrote in message news:eudw6iHqHHA.3952@TK2MSFTNGP03.phx.gbl... - Hide quoted text - > I'd like all users to be able to create views that are owned by dbo. > I have already granted them CREATE VIEW ... but what permissions do they > need so they can create them so they are owned by dbo ? Hi Michael Only users in the db_owner role can create views owned by the dbo user. -- HTH Kalen Delaney, SQL Server MVP www.InsideSQLServer.com http://sqlblog.com "Michael Tissington" <mtissing@newsgroups.nospam> wrote in message news:eudw6iHqHHA.3952@TK2MSFTNGP03.phx.gbl... - Hide quoted text - > I'd like all users to be able to create views that are owned by dbo. > I have already granted them CREATE VIEW ... but what permissions do they > need so they can create them so they are owned by dbo ? So if the user is not dbo, then any view they create will not be visible to other users ? "Kalen Delaney" <replies@public_newsgroups.com> wrote in message news:uHTFp3HqHHA.716@TK2MSFTNGP05.phx.gbl... - Hide quoted text - > Hi Michael > Only users in the db_owner role can create views owned by the dbo user. > -- > HTH > Kalen Delaney, SQL Server MVP > www.InsideSQLServer.com > http://sqlblog.com > "Michael Tissington" <mtissing@newsgroups.nospam> wrote in message > news:eudw6iHqHHA.3952@TK2MSFTNGP03.phx.gbl... >> I'd like all users to be able to create views that are owned by dbo. >> I have already granted them CREATE VIEW ... but what permissions do they >> need so they can create them so they are owned by dbo ? Actually, a member of the db_ddladmin role can CREATE views and other objects owned by the dbo user. But they cannot GRANT themselves rights to SELECT from the view. (That requires membership in db_owner or db_securityadmin.) RLF "Kalen Delaney" <replies@public_newsgroups.com> wrote in message news:uHTFp3HqHHA.716@TK2MSFTNGP05.phx.gbl... - Hide quoted text - > Hi Michael > Only users in the db_owner role can create views owned by the dbo user. > -- > HTH > Kalen Delaney, SQL Server MVP > www.InsideSQLServer.com > http://sqlblog.com > "Michael Tissington" <mtissing@newsgroups.nospam> wrote in message > news:eudw6iHqHHA.3952@TK2MSFTNGP03.phx.gbl... >> I'd like all users to be able to create views that are owned by dbo. >> I have already granted them CREATE VIEW ... but what permissions do they >> need so they can create them so they are owned by dbo ? No, on two counts.... 1. DBO is a user name. Other users can be in the db_owner role (or the ddl_admin role, as mentioned by Russell) and not be the user DBO to create an object owned by DBO. 2. Any object can be qualified with an owner name. DBO is a default owner name only. When a user references an unqualified object, SQL Server first checks to see if the object is owned by the user, then checks to see if the object is owned by dbo. However, it is recommended that you not rely on default owners. For several reasons, including performance issues, it is recommended that you always specify the owner for every object, even if that owner is the DBO user. -- HTH Kalen Delaney, SQL Server MVP www.InsideSQLServer.com http://sqlblog.com "Michael Tissington" <mtissing@newsgroups.nospam> wrote in message news:eRJk2CIqHHA.4532@TK2MSFTNGP06.phx.gbl... - Hide quoted text - > So if the user is not dbo, then any view they create will not be visible > to other users ? > "Kalen Delaney" <replies@public_newsgroups.com> wrote in message > news:uHTFp3HqHHA.716@TK2MSFTNGP05.phx.gbl... >> Hi Michael >> Only users in the db_owner role can create views owned by the dbo user. >> -- >> HTH >> Kalen Delaney, SQL Server MVP >> www.InsideSQLServer.com >> http://sqlblog.com >> "Michael Tissington" <mtissing@newsgroups.nospam> wrote in message >> news:eudw6iHqHHA.3952@TK2MSFTNGP03.phx.gbl... >>> I'd like all users to be able to create views that are owned by dbo. >>> I have already granted them CREATE VIEW ... but what permissions do they >>> need so they can create them so they are owned by dbo ?