December 17, 2009 at 8:41 pm
I would like to create a user who cannot open enterprise manager and query analyzed, but can access sql server through .net application.
Is this possible.
December 18, 2009 at 2:13 am
Create a appliction user and give him proper rights for application.
December 18, 2009 at 4:02 am
can you please describe this in more detail, how to do this.
Thanks in advance
December 23, 2009 at 10:44 am
To prevents the user from seeing the databases in SQL SSMS try following command
DENY VIEW ANY DATABASE TO <UserName>
User still have access the database thru application but he cant see the database by SSMS.
But still he can run query in Query Analyser but if he open the Query window the databases will not appear in the drop down.
For application user my suggestions are
do not allow any user direct access to the tables, grant all the access thru store procedures so they cannot change anything directly to table.
May be this helps !
December 23, 2009 at 11:06 am
it's a tricky problem; if you give me a username and password, I can use any application i can get my grubby fingers on to connect with.
the easiest solution is to have an application use a username and password that the end user does not see/use and cannot discover.storing the password as a hash in an ini file, in the registry, or even hardcoded into the applicaiton itself is one way to do it.
once the cat is out of the bag, so to speak, the only way to prevent specific applications is with a logon trigger, and that only works by checking the APP_NAME() function; a decent developer can include a false/fake app name in their connection string, so it would not keep me from creating a new app and putting your application 's name in my connection info;
here is an example of a logon trigger that prevents excel from connection; you can see where you can change it to either prevent other apps, or only allow a specific app to connect:
be carefull with this! if you said something like
IF APP_NAME() NOT LIKE '%my application%'
ROLLBACK
you could lock out yourself from connecting to the server with SSMS. you'd have to connect witht he DAC or emergency mode to undo a gaff like that.
-- THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF
-- ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO
-- THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
-- PARTICULAR PURPOSE.
-- Frederik Vandeputte - http://www.vandeputte.org
--TESTED ON SQL 2005 SP2 CTP
USE MASTER
GO
--Clean up old logging db
IF DB_ID ('Logging_demoSP2') IS NOT NULL
BEGIN
ALTER DATABASE Logging_demoSP2 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE Logging_demoSP2
END
GO
--Create Logging DB and table
CREATE DATABASE Logging_demoSP2
GO
USE Logging_demoSP2
CREATE TABLE Logging (
LoggingID int IDENTITY(1,1),
EventTime DATETIME,
EventType VARCHAR(100),
LoginName VARCHAR(100),
HostName VARCHAR(100),
AppName VARCHAR(255),
FullLog XML
)
GO
--The LOGON Trigger
CREATE TRIGGER logon_trigger
ON ALL SERVER
FOR LOGON
AS
BEGIN
DECLARE @message_body XML
SET @message_body = eventdata()
INSERT INTO Logging_demoSP2.dbo.Logging (
EventTime,
EventType,
LoginName,
HostName,
AppName,
FullLog )
VALUES
(
CAST(CAST(@message_body.query('/EVENT_INSTANCE/PostTime/text()') AS VARCHAR(64)) AS DATETIME),
CAST(@message_body.query('/EVENT_INSTANCE/EventType/text()') AS VARCHAR(100)),
CAST(@message_body.query('/EVENT_INSTANCE/LoginName/text()') AS VARCHAR(100)),
CAST(@message_body.query('/EVENT_INSTANCE/ClientHost/text()') AS VARCHAR(100)),
APP_NAME(),
@message_body)
END
GO
--Open demo connections
select * from logging
--Prevent acces from Excel
CREATE TRIGGER logon_trigger_not_from_excel
ON ALL SERVER FOR LOGON
AS
BEGIN
IF APP_NAME() LIKE '%excel%' --or like %Access% or like %Query Analyzer%
ROLLBACK
END
--Try to connect from Excel
--Clean DB
USE MASTER
GO
ALTER DATABASE Logging_demoSP2 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE Logging_demoSP2
DROP TRIGGER logon_trigger ON ALL SERVER
DROP TRIGGER logon_trigger_not_from_excel ON ALL SERVER
Lowell
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply