CODEWITHSUNDEEP
c#sql-serverdatabaset-sqlaccess-rights
Daniel
Daniel

Reputation: 1469

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

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?

Upvotes: 0

Views: 670

Answers (1)

Eric J.
Eric J.

Reputation: 150108

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.

Upvotes: 1

Related Questions