locked
Revoking of DBO permissions with VS2008 GDR2

    Question

  • Hi,

    I've created a brand new DBPROJ from an existing database.

    I have the option to drop objects that don't exist in the target database set to on, and i've given CONNECT grant permissions to DBO in my .sqlpermissions file like so:

    <PermissionStatement Action="GRANT">
        <Permission>CONNECT</Permission>
        <Grantee>dbo</Grantee>
    </PermissionStatement>

    The output of my Deploy is (shortened for brevity)
    GO
    PRINT N'Dropping Permission...';
    
    
    GO
    REVOKE CONNECT TO [dbo]
        AS [dbo];
    
    
    GO
    PRINT N'Creating Permission...';
    
    
    GO
    GRANT CONNECT TO [dbo];
    Could this be a bug with DB PROJ deploy scripts? It kind of fails gracefully when deploying but is this something that I should be worrying about? Sounds a bit dodgy specially if we're deploying to production!

    Thanks for your help in advance,

    Cheers,
    Pedro Costa
    Tuesday, March 02, 2010 5:58 PM

Answers

  • Hi Pedro,

    Its important to get the permission statement right so they compare equal. Try:

      <PermissionStatement Action="GRANT">
        <Permission>CONNECT</Permission>
        <Grantee>dbo</Grantee>
        <Grantor>dbo</Grantor>
      </PermissionStatement>

    Thanks,
    Barclay Hill Program Manager Visual Studio Data Tools (DataDude, DBPro, Database Edition, Database Projects, VS Data Tools) Please mark the responses as the answer if it resolves your question/issue. http://blogs.msdn.com/bahill
    Friday, March 05, 2010 5:03 PM

All replies

  • Hi Pedro,

    Its important to get the permission statement right so they compare equal. Try:

      <PermissionStatement Action="GRANT">
        <Permission>CONNECT</Permission>
        <Grantee>dbo</Grantee>
        <Grantor>dbo</Grantor>
      </PermissionStatement>

    Thanks,
    Barclay Hill Program Manager Visual Studio Data Tools (DataDude, DBPro, Database Edition, Database Projects, VS Data Tools) Please mark the responses as the answer if it resolves your question/issue. http://blogs.msdn.com/bahill
    Friday, March 05, 2010 5:03 PM
  • Hi Barclay,

    That seems to have worked, thanks very much.

    Quick question tho, what is Grantor check against? DB Creator?

    Cheers,
    P.
    Tuesday, March 09, 2010 5:26 PM
  • The Grantor is who will grant the permission.  Could be the active login or another using impersonation through the AS option.

    Thanks,
    Barclay Hill Program Manager Visual Studio Data Tools (DataDude, DBPro, Database Edition, Database Projects, VS Data Tools) Please mark the responses as the answer if it resolves your question/issue. http://blogs.msdn.com/bahill
    Tuesday, March 09, 2010 10:14 PM