Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
define "modify permissions" a bit more, please –  swasheck Sep 27 '12 at 3:04
    
I don't think there is a reliable way to setup security based on the client connecting. After all, if he has a login for SQL Server he could use any client to connect. –  a_horse_with_no_name Sep 27 '12 at 6:59

4 Answers 4

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

share|improve this answer

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.

share|improve this answer

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........

share|improve this answer

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.

share|improve this answer
    
I think the user wants to prevent write-access in SSMS only - he says the user has other programs that need write access. –  Max Vernon Sep 27 '12 at 4:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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