SELECT Statements only please

Question

I have a user that wants to use SQL Server Management Studio (SSMS) to run SELECT statements. I don't want him to be able to modify data.

I can't regulate his permissions through Windows Authentication because his user account needs modify permissions for programs that we run.

I could create a separate Windows account for him with less permissions, however if he does not use this new account, he will be able to change data.

Does anyone else have this problem? Is there a simple solution?

Answer 1

You could ensure they only use their 'special' account for ssms using a logon trigger. Something like this:

CREATE TRIGGER ssms_specific_users_only
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
declare @appname sysname
declare @ucheck integer
select @appname=program_name from sys.dm_exec_sessions where session_id=@@SPID
select @ucheck=COUNT(1) from ssms_user_table where login=SUSER_SNAME()
if (@appname='Microsoft SQL Server Management Studio - Query' AND @ucheck=0)
    BEGIN
        ROLLBACK
    END
END

That's not tested unfortunately as I don't have a box to hand to test it on, but it's roughly correct I believe.

In my experience once one user's got this ability there'll be more, so I've envisioned using a table to hold all the users who are allowed to do this. So it checks for the connecting program's name, and then sees if the user is in a list of allowed users, if they aren't then it rolls back the connection attempt.

msdn for logon triggers - http://msdn.microsoft.com/en-us/library/bb326598.aspx - for more info.

BTW, if the user already has permissions to modify data and connect with their other account, is the only thing stopping them from using SSMS the fact they don't have the app on their desktop? That could be a bit of a security hole if they ever discover the link to download it........

Answer 2

So you realise that he could already connect using SSMS (or any other client), if his Windows account has permission to modify data?

Logon triggers won't work for this - you can't switch context.

Regular DML triggers are nasty for this, because they have added side-effects.

Hopefully your application uses stored procedures to make its changes, and you can make sure the user has EXECUTE permission on them, and then you can DENY the other permissions accordingly, relying on Ownership Chaining (but not Cross-Database Ownership Chaining) to allow the application to do its stuff.

You can put traces in place to audit what he does when connected. Hopefully your applications set the ApplicationName property of their connections, so that you can ignore their activity. Of course, if he knows what he's doing, he could create connections that mimic that same ApplicationName, circumventing your checks.

Answer 3

If you're using SQL Server or Oracle, you can do this:

USE databaseName;

GRANT SELECT TO userName;

Answer 4

Grant the user read-only access and have your programs authenticate as a different user (one that's got modify rights).

Answer 5

Assuming you are using SQL Server, grant the user access to the database then add the user to the role db_datareader. That will provide read-only access to all tables in the database.