Sql-server – What permission is needed to set Database read-only

Posting this as an answer just because it is too long for a comment, and because it will be relevant to other users pretty soon.

SQL Server 2014 adds some new server-level permissions that will assist with exactly this type of scenario - they were designed with auditing in mind, but this type of requirement seems to fit that bill as well. You could simply add the following two permissions to a server-level login:

CONNECT ANY DATABASE

SELECT ALL USER SECURABLES

The former, like it sounds, allows the login to connect to any database. The nice thing about this is that it will allow this even for databases that are created in the future (provided you don't set explicit deny, which is how you can protect certain user databases from logins that have this permission). The latter allows the login to perform read operations on any database they have access to - so they can SELECT from tables, views, UDFs etc. but they will not be able to perform any UPDATE operations (I haven't tested if this permission understands when a stored procedure performs DML). These work great in combination if you want to give a login wide-open read access to the entire server, or to be more fine-grained you can grant traditional CONNECT privileges to certain databases, and the SELECT ALL USER SECURABLES right will only function for those databases where the login has explicit access to.

The 2014 security changes are documented here - well, partially; they forgot about the database-level permission ALTER ANY DATABASE EVENT SESSION - though that's not relevant here.

Linked Servers are not database specific, but provide a pipeline between two servers. Of course, the rights granted to the users will control which databases they can access. The account you are using naturally needs to be able to logon to both servers with enough permissions to execute your query.

Yes, creating a Linked Server is an administrative function and not available to all. Doing it yourself requires the ALTER ANY LINKED SERVER permission.

The parameters for the sp_addlinkedserver stored procedure are found at: http://msdn.microsoft.com/en-us/library/ms190479(v=sql.110).aspx

Also, under that link are instructions for several different ways to connect the servers together. If Server X and Server A are both SQL Servers, the setup is quite simple.

You can use sp_addlinkedsrvlogin to manage how the logins authenticate across the linked server. The details are at: http://msdn.microsoft.com/en-us/library/ms189811(v=sql.110).aspx

Depending on your landscape and if you are using Windows Logins, you may need to have Kerberos configured properly on your landscape so that the Kerberos ticket (allowed permissions) can be forward to the other server. See: Configuring Linked Servers for Delegation:

http://technet.microsoft.com/en-us/library/ms189580(v=sql.105).aspx