Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Consider this code sample:

            var sourceDatabaseConnectionString = "Data Source=MyServer;Initial Catalog=MyDatabase;User Id=MyLogin;Password=MyPassword";
            var serverConnection = new ServerConnection
            {
                ConnectionString = sourceDatabaseConnectionString
            };

            var server = new Server(serverConnection);
            var builder = new SqlConnectionStringBuilder(sourceDatabaseConnectionString);
            var database = server.Databases[builder.InitialCatalog];
            var transfer = new Transfer(database);

            // setting transfer options
            transfer.CopyAllObjects = true;
            transfer.CopyAllSynonyms = true;
            transfer.CopyData = false;

            transfer.Options.WithDependencies = true;
            transfer.Options.DriAll = true;
            transfer.Options.Triggers = true;
            transfer.Options.Indexes = true;
            transfer.Options.SchemaQualifyForeignKeysReferences = true;
            transfer.Options.ExtendedProperties = true;
            transfer.Options.IncludeDatabaseRoleMemberships = true;
            transfer.Options.Permissions = true;
            transfer.PreserveDbo = true;

            // scripting database
            var script = transfer.ScriptTransfer();

It tries to get a blank from existing database.

Some objects in this database depend from objects, located on linked servers. When this code tries to make a script for such objects, ScriptTransfer fails with "Login failed for user 'MyLogin'".

Log analysis shows, that error happens when MyLogin tries to log into liked server. But what is the need for this? After all, I just want to get a bunch of CREATE ... scripts for MyDatabase, I don't want to read any data from linked server.

My SQL Server and SMO version is 2012 SP1.

share|improve this question

1 Answer 1

You can only get data from a SQL Server that you are logged into. The metadata of table definitions, stored procedures, and so forth is also data.

So, your login through the linked server must be successful. This can be done by granting your login rights on the other server. Or, the security of the linked server could be through a specific login that has the needed rights.

However, getting rights to the metadata of a database does not imply that you need rights to the data in the database. For example:

USE DBName;
GRANT VIEW DEFINITION ON DATABASE::DBName TO [YourDomain\YourLogin]

This will allow you to script the definitions from the database without having access to the user data. (Although that can also be granted separately if needed.)

EDIT: SQL Server does not support deferred name resolution across a linked server. Therefore, I am fairly sure that since the reference to the remote table cannot be resolved that is why the scripting is failing.

Of course, if the stored procedure is not encrypted then the text is stored in sys.sql_modules of your local server. You could extract the text from there.

 SELECT definition FROM DBName.sys.sql_modules 
 WHERE object_id = object_id(N'DBName.Schema.ObjectName');
share|improve this answer
    
The problem is that I don't need metadata of linked server's object at all. For example, there's a stored proc in my database, which retrieves something from linked server. I understand, why I need to be logged in for executing this SP. But I don't understand, why I need this for retrieving SP's body, which is stored as a text. –  Dennis Sep 16 '13 at 14:14
    
Edited the response per your comment. –  RLF Sep 16 '13 at 14:35

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.