Script permissions per object

[mika76]

This is great - but you're missing grants for each object, especially if you're going to drop and recreate every time...

[justinlettau]

What do you mean? The connection to SQL is with user provided credentials, so we should have whatever permissions needed to execute.

[mika76]

The user you used to read the db is ok, but it is not the only user that that object might have grants to. You might have set up roles for example and given different roles different access permissions.

For example I might have a role/user called ReportRunner and that might only have SELECT permissions on some tables. Currently you lose all that info. The only info you keep is the "owner" of the object - which in this case is the user you are logged in as.

Plus with sql you can give deny permissions too where you explicitly DENY a permission to a specific user/role.

See this for some info...

[justinlettau]

It seems SQL Server 2016 introduced the CREATE or ALTER syntax, which avoids having to drop each object (and losing permissions). Will add this to idempotent options soon!

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/11/17/create-or-alter-another-great-language-enhancement-in-sql-server-2016-sp1/