Press "Enter" to skip to content

Dealing With Limited Rights In Biml

Shannon Lowder walks through a scenario where he wants limited rights to process metadata changes, separate from any data transfer:

My development environment has a local instance of SQL Server with AdventureWorks2014 on it.  I’m going to use that as my source.  I also created a database on this instance called BimlExtract to serve as my destination database.

To create a user that can only read the schema on the source system, I created a login and user named ‘Biml’.  I granted this user VIEW DEFINITION in AdventureWorks2014. I also added this user to the db_owner group in BimlExtract.  Now, this user can read the schema of the source, and create tables in the destination. I’ve included the T-SQL to set the permissions in Database Setup.sql.

Now, we’re ready to walk through the solution.

Click through for the solution and also a GitHub repo with all of Shannon’s code.