SQL Server Migration Assistant
A Microsoft tool designed to automate database migration to SQL Server from Access, DB2, MySQL, Oracle, and SAP ASE.
The security model has not changed in SQL 2019.
does having permission to script a table create in 2019 require anything more than read permissions?
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(41.6, 70%, 14%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ED%3C/text%3E%3C/svg%3E)
db042190
1,521
Reputation points
hi we just upgraded our oltp to 2019 where we have to be careful about how much security i have.
in the past i've always been able to script tables there and still be restricted to read permissions.
can i still do that in 2019?
SQL Server Migration Assistant
Developer technologies | Transact-SQL
Developer technologies | Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
Answer accepted by question author
2 additional answers
Sort by: Most helpful
-
db042190 1,521 Reputation points
2021-12-15T16:19:55.403+00:00 thx tom and erland. that was interesting.
-
db042190 1,521 Reputation points
2021-12-08T16:18:36.34+00:00 Tom, if you are still reading this thread, can someone have table/index script permissions while being limited to read access in production? double checking in case i'm fooling myself.
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(67.2, 1%, 16%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ETP%3C/text%3E%3C/svg%3E)
Tom Phillips 17,786 Reputation points2021-12-08T18:01:11.327+00:00 SQL 2008+ added a permission "VIEW DEFINITION" which controls that. However, many things require this right, so if you revoke it you will need to test your specific use case.
-
Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator2021-12-08T22:39:06.38+00:00 Adding to what Tom says: Normally if you have SELECT permission on a table, that should be sufficient to script the table, because SELECT implies VIEW DEFINITION. But you could DENY VIEW DEFINITION on a user, in which case the user would still be able to run SELECT queries, but not be able to script. (However, this is very likely to cause problems, as many applications relies on being able to read metadata.)
Sign in to comment -