November 1, 2009 at 10:16 pm
Hi Friends,
Do anyone know how to create a new "User" in SQL Server, and give him such privileges that he can access the Databases via .NET application. But the "User" should not be able to access the SQL Server using SSMS.
What I mean is are there such user privileges that lets the "User" run queries from .NET Application against the SQL Server Database, but the "User" can not directly use SSMS to view or query the Database?
Cheers,
Bhavesh
November 2, 2009 at 12:30 am
Usually you will have a seperate database account which will be used to connect to the database from your .net application (which users will not be aware of).
If you are looking for a SSMS kind of feature where user can 'Write' their own query and execute it, i think you would have to come up with your own implementation of that in dot net.
Not sure if I understand your requirement correctly!
---------------------------------------------------------------------------------
November 2, 2009 at 8:15 am
Suppose you have an db account with name 'ta' and pwd as 'ty'. When a user connects to DB through Asp.net, he useses the same account details in connection details and access the db. But if the user opens the SSMS and try to log on to SSMS with the same credentials, then he should not be allowed.
please correct me if I am wrong.
November 2, 2009 at 8:22 am
siddartha pal (11/2/2009)
Suppose you have an db account with name 'ta' and pwd as 'ty'. When a user connects to DB through Asp.net, he useses the same account details in connection details and access the db. But if the user opens the SSMS and try to log on to SSMS with the same credentials, then he should not be allowed.please correct me if I am wrong.
Personally I think that is not possible! lets see if someone has anything better to offer. Thanks.
---------------------------------------------------------------------------------
November 2, 2009 at 8:28 am
the answer is yes and no.
once I have a username and password, I'm good to connect with anything i can get my grubby fingers on...SSMS, a program i write, Access,Excel, Oracle's SQL Developer...even a vbs script...you name it.
the only way to prevent users from using a specific application to connect is to use a logon trigger...but that is based on the application name, which can be faked by any developer who has access to modifying the connection string.
here is an example from a previous thread, where someone wanted to prevent Access or Excel from connecting to the database. all you'd do is change the name to "Microsoft SQL Server Management Studio - Query "
you don't have to test just the application name...you could also make sure they were part of an admin group or some role as to whether they can connect with SSMS or not.
note a logon trigger could prevent EVERYONE from connecting if you mess it up....
-- 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%'
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
November 2, 2009 at 8:37 am
Thats a good one, thanks lowell 🙂
---------------------------------------------------------------------------------
November 2, 2009 at 9:35 pm
Yes Siddartha, that is the solution what I am looking for.
A particular user account can connect to SQL Server via ASP.net but should not be able to use SSMS to log on SQL Server.
I hope it is possible!
November 3, 2009 at 12:24 am
Have you had a chance to look at the post by lowell? I thought it solves your problem!
---------------------------------------------------------------------------------
November 4, 2009 at 2:18 am
Thanks for the help.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply