1

I created a SQL Login (Login01), mapped to DB user (User01), member of db_owner database role. When connected locally with this user in SSMS, i try to script out the database (Script Database As -> CREATE TO) and it works as expected. When connected from a remote machine (same domain, same LAN), it fails (Could not read metadata, possibly due to insuficient access rights). Only way i found to permit it is to grant "View any definition" to the Login.

I dont understand why it only fails when connecting remotely, is this an expected behaviour?

Edit: As said in comments, the issue here has nothing to do with local/remote connection but is related to the SSMS version. I edited title also so the question/answer is easier to find.

Improve this question
5
  • Just to confirm, Login01 is a SQL authenticated login (with a password)? If so, permissions will be the same. Verify Object Explorer is connecting to the same instance with the remote versus local connection and with the same credentials.
    – Dan Guzman
    Commented Jun 27, 2016 at 11:54
  • Yes it s a SQL authenticated login (with password). I confirm that permissions are the same as it s the same SQL Login connecting to the same Instance. Only difference is from where i run SSMS ... I know it doesn't sound right and my first answer was also "it s not possible, i m sure the user is not connecting with same Login, or to the same instance", but i just reproduced it on a Lab ... It s confusing for me too that s why i m asking...
    – Tametale
    Commented Jun 27, 2016 at 12:05
  • I just did some more testing in my Lab. In fact, i can only reproduce it from one machine in the LAB (it happend to be the first one i tested ...), running SSMS 2016 (the instance i connect to is a 2014, i ll install SSMS2014 too soon ...). It s the only machine i got in the lab without a running SQL Server Instance (i dont know if it s a possible cause yet ... i m going to create a new one to confirm).
    – Tametale
    Commented Jun 27, 2016 at 12:32
  • It sounds like it's a bug in SSMS 2016, nothing to do with local/remote connection. To summarize it, i connected to SQL2014 and SQL2016 instances, locally or remotely: When using SSMS2014 i was able to script out the database without any issue; when using SSMS2016 i was ablet to script it out only after granting the Login "View any definition" ... Can someone reproduce it?
    – Tametale
    Commented Jun 27, 2016 at 12:52
  • Same issue with SSMS 2008R2, no issue with SSMS 2012.
    – Tametale
    Commented Jun 27, 2016 at 13:32

1 Answer 1

Reset to default
0

I opened a connect feedback here: https://connect.microsoft.com/SQLServer/feedback/details/2869241 as follow:

"A SQL Login, mapped to a database user member of the db_owner role cannot script out the database with "Script Database As" or "Generate Script" task when using SSMS 2008, 2008R2, 2016. It gets the following error message "Could not read metadata possibly due to insufficient access rights. Granting the login "View any definition" solve it but this cannot be a workaround for obvious security reason.

(Tested with SQL2008, SQL2014 and SQL2016 instances installed on VMware virtual machines.)"

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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