Post reply

Disable database user

  • July 18, 2013 at 9:49 am

    #280545

    Hello everyone,

    We are working on a migration, a few db's a night for the next week. Part of the migration requires that we shutoff access to the database. This is a 3rd party application and, in their infinite wisdom, they created a single login that maps to a user in each database.

    As such, I cannot disable the login. Is there a way to disable the user or unmap it from the login?

    Note, I am not able to take the DB offline, rename it, etc, which complicates things more.

    Thanks,

    Fraggle

  • July 18, 2013 at 9:57 am

    #1633702

    Have you tried setting the database to Single_User mode?


    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • July 18, 2013 at 11:01 am

    #1633734

    Can't do it. We are using Replication to migrate the databases.

    Thanks,

    Fraggle

  • July 18, 2013 at 12:25 pm

    #1633754

    fraggle i just tested this myself;

    i was hoping i could alter a user and remove their mapping to the existing login, but i found i had to crop and recreate the user without login instead.

    if the user doesn't have a matching user, it wouldn't have access to that specific database anymore.

    this was my test:

    Create LOGIN [ClarkKent] WITH PASSWORD='NotTheRealPassword'

    USE [WHATEVER]

    Create USER [ClarkKent] FOR LOGIN [ClarkKent] ;

    --didn't work:

    --ALTER USER [ClarkKent] WITHOUT LOGIN;

    --remove and put the user back without login, so we can

    DROP USER [ClarkKent];

    CREATE USER [ClarkKent] WITHOUT LOGIN;

    --add back any roles, ie

    CREATE ROLE [AlmostOwners]

    EXEC sp_addrolemember N'db_ddladmin', N'AlmostOwners'

    EXEC sp_addrolemember N'db_datareader', N'AlmostOwners'

    EXEC sp_addrolemember N'db_datawriter', N'AlmostOwners'

    --can the users EXECUTE procedures? comment out if false

    GRANT EXECUTE TO [AlmostOwners]

    --allow the users to see view proc and function definitions

    Grant View Definition ON SCHEMA::[dbo] To [AlmostOwners]

    --finally add our user to the role:

    EXEC sp_addrolemember N'AlmostOwners', N'ClarkKent'

    if needed then later we can ALTER USER [ClarkKent] FOR LOGIN [ClarkKent] to put him back.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 18, 2013 at 3:03 pm

    #1633803

    How about putting the user in the db_denydatareader Database security Role?

  • July 19, 2013 at 1:57 am

    #1633873

    Fraggle-805517 (2013-07-18)

    Is there a way to disable the user or unmap it from the login?

    REVOKE CONNECT TO thisuser

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • July 20, 2013 at 10:17 am

    #1634258

    There is no way to "disable a database user" per se. The way i do this is to remap the User i want to "disable" to a different Login, a temporary one that no one could ever usemto connect to the instance. Then when i want to "enable" the User again, i remap it to the original Login thereby reinstating access.

    ALTER USER...WITH LOGIN =

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply