Open SQL Server database with read-only access - making sure

Question

I have an application written in C# that has a SQL Server backend. We shall give power users the ability to create reports by writing and executing their own SQL scripts against the systems database.

Now, obviously, I cannot use the SQL Server user that the system uses for connection as the users would easily be able to modify data in the database. I know that I cannot force a connection to be opened as read-only from a C# application, so I must use a user that has read-only privileges to the database.

I was planning on testing whether the user which connects with reporting purposes has read only access to the database. If the access is read-only the the query will be executed, otherwise execution will be denied. How can I test this from the C# application or by using T-SQL?

This really depends on how you are implementing your application's security. you can roll your own and use a read only account (or group) for instance. You could use SQL's security and trap exceptions. If you are using SSRS that is managed there if you are using its interface. I like to use the built in security for stuff if possible and look for various exceptions. — Mike Cheel, Jul 26 '16 at 20:22
May I just point out that this is a very very bad idea. You are essentially giving "Power Users" the capability of bringing your database server to its knees with a poorly written query. — Kevin, Jul 26 '16 at 20:26
Kevin: it would be, however the select few allowed to execute these queries are knowledgeable enough to know what they're doing. Besides we do not have such big masses of data at any time in the system that could potentially harm performance even if the queries - which will be rather simple - select all data in the entire DB. All I want is make sure that the connections power users select have read-only access. — Daniel, Jul 26 '16 at 20:33

score 1 · Accepted Answer · edited Apr 13 '17 at 12:42

You can check if the user has any user mapping other than db_datareader for the database in question. I found a good TSQL query but it fails to run on my system due to a collation conflict. Here is an updated version from that post that runs for me. You can run it in SQL Server Management Studio to get an idea of the data returned.

DECLARE @name SYSNAME = N'ReadonlyUserNameHere'; -- input param, presumably

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'UNION ALL SELECT N''' + REPLACE(name,'''','''''') + ''',
  p.name COLLATE DATABASE_DEFAULT, p.default_schema_name COLLATE DATABASE_DEFAULT, STUFF((SELECT N'','' + r.name 
  FROM ' + QUOTENAME(name) + N'.sys.database_principals AS r
  INNER JOIN ' + QUOTENAME(name) + N'.sys.database_role_members AS rm
   ON r.principal_id = rm.role_principal_id
  WHERE rm.member_principal_id = p.principal_id
  FOR XML PATH, TYPE).value(N''.[1]'',''nvarchar(max)''),1,1,N'''')
 FROM sys.server_principals AS sp
 LEFT OUTER JOIN ' + QUOTENAME(name) + '.sys.database_principals AS p
 ON sp.sid = p.sid
 WHERE sp.name = @name '
FROM sys.databases WHERE [state] = 0;

SET @sql = STUFF(@sql, 1, 9, N'');

PRINT @sql;
EXEC master.sys.sp_executesql @sql, N'@name SYSNAME', @name;

When rewriting the query for C#, be sure to use a parameterized query to inject the username.

Open SQL Server database with read-only access - making sure

1 Answers1