locked
Only one user can access the database include sa don't access the database RRS feed

  • General discussion

  • Only one user can access the database include sa don't access the database

    how to restrict the all users except one user
    Wednesday, September 14, 2016 8:04 AM

All replies

  • Only one user can access the database include sa don't access the database

    how to restrict the all users except one user

    Wednesday, September 14, 2016 8:03 AM
  • You can not limit the permissions of a login which is member of the SysAdmin server role; those user(s) always can access any database.

    And for all other Login: Simply don't grant permissions, then they can't access the database


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, September 14, 2016 8:07 AM
  • I am not sure that is going to work properly for you, test it before applying to the prod.

    reate login AuditLogin with password = ‘AuditLoginPswd’
    go
    /*Create a very simple login trigger */
    create trigger AuditLogin_Demo
    /* server means instance level*/
    on all server
    with execute as self
    /* We specify the logon event at this stage
    – If there are more than one connections,
    – Issue a rollback*/
    for logon
    as begin
    IF ORIGINAL_LOGIN()= ‘AuditLogin’ AND
    (SELECT COUNT(*) FROM sys.dm_exec_sessions
    WHERE is_user_process = 1 AND
    original_login_name = ‘AuditLogin’) > 1
    ROLLBACK;

    end
    go


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, September 14, 2016 8:39 AM
  • If you're wanting to limit access to a database so that only any one user can access it at any one time then you can set the Database to Single User Mode.

    However this can cause issues when performing database maintenance, e.g. backups so if you're performing Transaction Log Backups then this is not a viable option.


    Please click "Mark As Answer" if my post helped. Tony C.

    Wednesday, September 14, 2016 8:57 AM
  • When you install SQL Server, at least one person is granted access as a sysadmin. If you don't want more people to access the SQL Server, don't make logins for anyone else. Any member of the sysadmin fixed server role will be able to access all databases. They bypass all the permission checks.

    If you don't want the sa account to have access, either don't enable SQL Server authentication, or disable the sa account. But this is a SQL Server wide setting. There is no way to allow sa to access all databases except one.

    And administrators on the computer (members of the local administrators group) can always get access, though they might have to work at it. See Troubleshooting: Connecting to SQL Server When System Administrators Are Locked Out.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Wednesday, September 14, 2016 4:44 PM