Sql-server – Deny a Sql Server user from a specific hostname

In my view Windows Authentication is still more secure. Just.

With windows authentication you can allow a group of people (via a Windows Group) to have access to the database. Only those users can use an application, any application, to connect to the database.

If you use a SQL Server account then anyone who knows the credentials (and invariably these always leak out) can connect to your database. Again with any application.

You are absolutely correct that with Windows authentication can connect directly to the database and issue any query they see fit - due to the model your app requires.

A third option

What I would suggest in this situation is the use of an application role: http://msdn.microsoft.com/en-us/library/ms190998.aspx

The problem you have here is you have to be able to code the application to be able to use the application role - that's not always easy. Afraid I'm not an Access expert to be able to advise here specifically.

The advantage of an application role is that you can restrict which applications can perform certain commands. Its similar to a database role but has a password assigned to it that is needed before the rights of the role can be inherited. Using an application role with a Windows group you could limit the users who can connect to a database and the application that they can use to query the database.

From the documentation:

• Although any user with access to a database can create a diagram, once the diagram has been created, the only users who can see it are the diagram's creator and any member of the db_owner role.
• Ownership of diagrams can only be transferred to members of the db_owner role. This is only possible if the previous owner of the diagram has been removed from the database.
• If the owner of a diagram has been removed from the database, the diagram will remain in the database until a member of the db_owner role attempts to open it. At that point the db_owner member can choose to take over ownership of the diagram.

So it seems that you won't be able to do it with lower roles like db_datareader.

Behind the scenes, here is what Management Studio is calling to drive the list:

CREATE PROCEDURE dbo.sp_helpdiagrams
(
    @diagramname sysname = NULL,
    @owner_id int = NULL
)
WITH EXECUTE AS N'dbo'
AS
BEGIN
    DECLARE @user sysname
    DECLARE @dboLogin bit
    EXECUTE AS CALLER;
        SET @user = USER_NAME();
        SET @dboLogin = CONVERT(bit,IS_MEMBER('db_owner'));
    REVERT;
    SELECT
        [Database] = DB_NAME(),
        [Name] = name,
        [ID] = diagram_id,
        [Owner] = USER_NAME(principal_id),
        [OwnerID] = principal_id
    FROM
        sysdiagrams
    WHERE
        (@dboLogin = 1 OR USER_NAME(principal_id) = @user) AND
        (@diagramname IS NULL OR name = @diagramname) AND
        (@owner_id IS NULL OR principal_id = @owner_id)
    ORDER BY
        4, 5, 1
END

So you can see this matches the documentation.

Now a couple of workaround ideas:

• In a Logon trigger, update the principal_id of all diagrams to be the current login. This means they will have access to all diagrams until the next person logs in. Not optimal.
• Use a trigger on the sysdiagrams table itself (it's not really a system table), and whenever a diagram is created or updated, add / update a copy for each principal (with their user name appended). Not optimal either, and you could have people overwriting each other's diagrams all day long.

Here is an idea of the second workaround - all you really have to maintain here is a list of the database principals that you want to be able to access the diagrams (you'll also want to have something to clean up diagrams that have been deleted, and also some periodic maintenance that deletes diagrams for principals that have been deleted):

CREATE TRIGGER dbo.sysdiagrams_distribute
ON dbo.sysdiagrams
WITH EXECUTE AS N'dbo'
FOR INSERT, UPDATE
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @p TABLE(principal_id INT, name SYSNAME);

  INSERT @p SELECT principal_id, name
    FROM sys.database_principals
    -- change this list:
    WHERE name IN (N'test_blat_user', N'test_blat_user2', N'dbo');

  UPDATE d 
    SET [version] = i.version, definition = i.definition
  FROM inserted AS i
  CROSS JOIN @p AS p
  INNER JOIN dbo.sysdiagrams AS d
  ON d.name = i.name
  AND d.principal_id = p.principal_id;

  INSERT dbo.sysdiagrams(name, principal_id, version, definition)
    SELECT i.name, p.principal_id, i.version, i.definition
    FROM inserted AS i
    CROSS JOIN @p AS p
    WHERE NOT EXISTS 
    (
      SELECT 1 FROM dbo.sysdiagrams WHERE name = i.name
      AND principal_id = p.principal_id
    );
END
GO

After creating a couple of diagrams, here is what an abridged version of Object Explorer looked like for these users:

Now, dbo will collect a whole bunch of copies of diagrams, which maybe isn't necessary, but you probably want those to be the "master" in most circumstances.