Problem
I have a group of developers that I support and they are reporting they cannot see columns within their tables. I have granted them db_datareader permissions which is a standard at my company for QA environments. Why can't they see their column definitions? Check out this tip to learn more.
Solution
The issue is the development team is taking advantage of user-defined data types, which by default is not encompassed as part of the db_datareader security permissions. Since user-defined data types are compiled code, they assume the same permissions needed by stored procedures and functions. Below is a screen shot of the example data type that I will be using for this demo.
The following code is what I used for this tip to create my objects that were used for testing the security of the user-defined data types.
Next, I will create a user with just db_datareader access to the database.
Now, I am going to log in with my new user 'TestUser' and see what I can view regarding the column information. As you can see, the table 'Customer' which uses the user-defined data type, shows no column data type information for the zip code.
When I attempt to script out the 'Customer' table, which uses the user-defined data type, I get an error as well.
To resolve this issue, I am going to use the GRANT VIEW DEFINITION T-SQL statement. This allows the user to see the metadata about the securable on which the permission was granted. This permission can be granted at the object level, schema level, or database wide which is the choice I am going to use.
If you wanted to secure it at the object level you could use the following statement to allow access to just that securable.
After granting this permission, you can see I can now see the column data type information, as well as the ability to script out the table.
The VIEW DEFINITION permission is very handy when other teams, such as developers, need access to script objects and see certain metadata about stored procedures, functions, and types within SQL Server.
Source Collected from MSSQLTIPS.COM
No comments :
Post a Comment