Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
drop all users in database and recreate them with same permissions

Answered drop all users in database and recreate them with same permissions

  • Thursday, January 03, 2013 4:35 PM
     
     
    Sign In to Vote
    0

    Hello, I think I figured out my problem. I need to drop all users in every database one by one and then add them back to there spcific databases one by one. Is anybody aware of the script to do this? 

    windows nt credentials
    sql 2008

    It should start like this

    USE [Maintenance]
    GO

    /****** Object:  User [xxxxx\xxxxx]    Script Date: 01/03/2013 11:48:40 ******/
    IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'Txxxxx\xxxxx')
    DROP USER [xxxxx\xxxxx]
    GO

    USE [Maintenance]
    GO

    /****** Object:  User [xxxxx\xxxxx]    Script Date: 01/03/2013 11:48:40 ******/
    GO

    CREATE USER [xxxxx\xxxxx] FOR LOGIN [xxxxx\xxxxx] WITH DEFAULT_SCHEMA=[dbo]
    GO

    • Edited by skdoc36 Thursday, January 03, 2013 4:39 PM
    • Edited by skdoc36 Thursday, January 03, 2013 5:49 PM
    •  
     

All Replies

  • Thursday, January 03, 2013 5:15 PM
     
     
    Sign In to Vote
    0

    Hello,

    When you re-create the user with the same permission, then you will have the same situation as before, so it makes no sense. What should it be good for?

    Olaf Helper

    Blog Xing

     
  • Thursday, January 03, 2013 5:37 PM
     
     
    Sign In to Vote
    0

    I do not understand your question. I have to drop the user from the database and add them back to the database. This will fix my problem, but I would rather not do them one by one.

    Thanks

     
  • Thursday, January 03, 2013 10:44 PM
     
     Answered
    Sign In to Vote
    1

    To answer Olaf's question, I guess you need to drop and recreate the users to update their SIDs.

    Dropping and recreating the users is not that difficult:

    SELECT 'DROP USER ' + quotname(name) +
           ' CREATE USER ' + quotename(name)
    FROM   sys.database_principals
    WHERE  type = 'U'

    Copy and execute result.

    But I don't think this is what you should do. You should drop the user - and the add the AD group they are all part of. If there is no AD group, talk with your Windows admin about this. Of course, it depends on what fits your organisation, but most people find it easier to administer group membership in one single place. This assumes, though, that all users have the same set of permissions.

    Whichever route you take, you must also administer membership in database roles and object permissions. This is a little more difficult, so I don't type that on the top of my head. Well, role membership is not too tricky, but object permission can be. On the other hand, if you have a simple model where everyone is part of db_datareader and db_datawriter and nothing is is not too tricky.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed As Answer by vasubabu karempudi Friday, January 04, 2013 10:11 AM
    • Marked As Answer by skdoc36 Friday, January 04, 2013 1:11 PM
    •  
     
  • Friday, January 04, 2013 5:28 AM
     
     
    Sign In to Vote
    0

    This will fix my problem, but I would rather not do them one by one.

    And what for a problem do you have, that would be fixed by re-creating database users? If it's the SID, as Erland guesses, then you have simply "orphaned users" and they can be fixed with an also simple script from TechNet ScriptingCenter: Secure Orphaned User AutoFix

    Olaf Helper

    Blog Xing

     
  • Friday, January 04, 2013 8:31 AM
     
     
    Sign In to Vote
    0

    And what for a problem do you have, that would be fixed by re-creating database users? If it's the SID, as Erland guesses, then you have simply "orphaned users" and they can be fixed with an also simple script from TechNet ScriptingCenter:Secure Orphaned User AutoFix <http://gallery.technet.microsoft.com/scriptcenter/Secure-Orphaned-User-f3aceae0>

    That would apply if they are SQL login, but they are Windows logins. If the users have been dropped and recread in the AD, it's a different matter.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
     
  • Friday, January 04, 2013 1:11 PM
     
     
    Sign In to Vote
    0
    @Olaf Helper I have already ran the auto_fix. I have no orphaned users. My question was:  I need to drop all users in every database one by one and then add them back to there spcific databases one by one.
     
  • Friday, January 04, 2013 10:29 PM
     
     
    Sign In to Vote
    0

    I need to drop all users in every database one by one and then add them back to there spcific databases one by one.

    And did you try the script I posted? And more importantly, did you consider my questions and suggestions?

    It would also be interesting to know why you have come to the conclusion why must drop and recreate the users.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
     
  • Friday, January 04, 2013 10:44 PM
     
     
    Sign In to Vote
    0
    I already ran the same script before I posted. And no your first post I did not consider because it did not answer my question. Thanks
     
  • Sunday, January 06, 2013 10:25 AM
     
     
    Sign In to Vote
    0

    Hmmm perhaps you need only to re-map the use  and logins?

    ALTER USER username WITH LOGIN =loginname

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

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance