How to secure SQL Server Management Studtio

Question

I have SQL Server 2008 R2 Express installed on client systems. We got information that some people just open the Management Studio and change the data.

How can we stop from this happening and can we make a log of what is being changed by them?

An additional measure you could take would be a [logon trigger that checks `APPNAME`](http://www.erichumphrey.com/category/sql-triggers/) but this is settable by the client so cannot be relied upon except to thwart some people! — Martin Smith, Dec 24 '12 at 18:04

score 4 · Answer 1 · answered Dec 24 '12 at 13:08

4

how can we stop from this happening

Change the logins on your SQL Server. The users can only connect to databases and servers that they have the credentials for.

If you are using windows login for authentication, you will need to run whatever application that needs the database using its own login (using runas or such), again, only giving access to the application.

answered Dec 24 '12 at 13:08

Oded

489,969
99
883
1,009

is there possibility that we can **disable** Windows authentication in sql server management studio and when our tech need to login he may **enable** windows authentication. – faheem khan Dec 24 '12 at 13:15
1

@faheemkhan - I am not aware of such a possibility. – Oded Dec 24 '12 at 17:43

score 0 · Accepted Answer · answered Dec 28 '12 at 06:02

i could not get the idea of runas .But i used @Martin Smith idea and build solution which is below.

It will be used to disable all non sa user doing any activity in any login. I did not test it in active directory.

Deny Write
Deny Read
Deny Any Database

Deny Sql to connect.

Create TRIGGER DisableSMSSEntry_trigger
ON ALL SERVER  WITH EXECUTE AS 'sa'
FOR LOGON

AS

    BEGIN
    DECLARE @NtUserName AS NVARCHAR(100)

    IF ( (SELECT DISTINCT RTRIM(LTRIM(nt_user_name)) AS LogedUser  FROM sys.dm_exec_sessions WHERE is_user_process = 1 AND login_name !='sa') >0 )
    BEGIN
    SET @NtUserName= (SELECT DISTINCT RTRIM(LTRIM(nt_domain+'\'+nt_user_name)) AS LogedUser  FROM sys.dm_exec_sessions WHERE is_user_process = 1 AND login_name !='sa')



    DECLARE @membername  AS varchar(100),@rolename AS varchar(100)
    SET @membername=@NtUserName
    set @rolename ='DB_DenyDataWriter'
    EXEC sp_addrolemember @membername,@rolename
    set @rolename = 'DB_DenyDataReader'
    EXEC sp_addrolemember @membername,@rolename

    exec  (' DENY VIEW ANY DATABASE TO '+ @NtUserName )
    exec  ('DENY SQL CONNECT ' + @NtUserName )
    ROLLBACK
    END

END

How to secure SQL Server Management Studtio

2 Answers2