Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Posts
    25

    Lightbulb Unanswered: Export Database Users

    Hi, guys

    I has a question for you:

    How can i Export the users and permisions and restore it again?

    Thanks in advance

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    In Enterprise Manager, right-click on a database, and look for Generate SQL Script. You may have to play around with it to get it to your liking, but you should get what you need.

  3. #3
    Join Date
    Feb 2004
    Posts
    25

    Arrow

    Thanks for your answer MCrowley, but i think i'm not explained very well (my english is not too good, so sorry for any writting-mistake), i want to export the user logins of the entrie Sql Server, they are located on the master DB, but i can't rigth-click on that db and select Generate SQL Script, also i can make a backup of the master db but can't restore it again.

    Thanks

  4. #4
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    In reverse order

    2. Yes you can restore the master database. Read all about it here: http://msdn.microsoft.com/library/de...kprst_4g4w.asp

    1. The server logins are located in the sysxlogins table or you can use the syslogins view. The following snippet of code uses the view:

    Code:
     
    -- Script all logins on a server 
    set nocount on
    /* create logins script */
    select 'use master; if not exists(select name from syslogins where name = ''' + name + ''') exec sp_addlogin @loginame = ''' + name + ''', @passwd = ', 
    convert(varbinary(256), password), ', @defdb = ''' + dbname + ''', @deflanguage = ''' + 
    language + ''', @sid = ', sid ' ', ', @encryptopt = ''skip_encryption''' 
    from syslogins
    /* create server roles script */
    select 'exec sp_addsrvrolemember @loginame = ''' + name + ''', @rolename = sysadmin' from syslogins where sysadmin = 1 union
    select 'exec sp_addsrvrolemember @loginame = ''' + name + ''', @rolename = securityadmin' from syslogins where securityadmin = 1 union
    select 'exec sp_addsrvrolemember @loginame = ''' + name + ''', @rolename = serveradmin' from syslogins where serveradmin = 1 union
    select 'exec sp_addsrvrolemember @loginame = ''' + name + ''', @rolename = setupadmin' from syslogins where setupadmin = 1 union
    select 'exec sp_addsrvrolemember @loginame = ''' + name + ''', @rolename = processadmin' from syslogins where processadmin = 1 union
    select 'exec sp_addsrvrolemember @loginame = ''' + name + ''', @rolename = diskadmin' from syslogins where diskadmin = 1 union
    select 'exec sp_addsrvrolemember @loginame = ''' + name + ''', @rolename = dbcreator' from syslogins where dbcreator = 1
    set nocount off

    -- This is all just a Figment of my Imagination --

  5. #5
    Join Date
    Feb 2004
    Posts
    25

    Thumbs up

    thanks tomh53, the script you posted look very fine, i'll test it and think will help me.

    thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •