SQL Server Performance

how to script users along with their permission roles etc

Discussion in 'SQL Server 2005 General DBA Questions' started by raagi2000, Feb 26, 2008.

  1. raagi2000 New Member

    Hello folks
    I am on SQL2K5 enterprise edition. I want to refresh staging database with production. Before I do that I want to script out the users with their roles/permission so I can restore the staging permission after database restore.
    I tried to use generate script but it scripts only the users NOT their respective roles / permissions.
    Thanks in advance.
  2. Arman New Member

    EXEC master..sp_help_revlogin in the source server and save the output to run on the target server.
  3. Greg Larsen New Member

    sp_help_revlogin is a great way to script out logins, but I think the question is how to script our "DATABASE" users and permissions, like roles.
  4. dba.mani@gmail.com New Member

    Install this proc on master db and this proc can be executed from any db..will generate the script of the existing permisssions of the dbusers in a db as a output..
    ----------------------------------------------------------------
    CREATE procedure sp_dba_extractuser
    @user sysname = null
    as
    begin
    -- extract user script in a database
    -- if no input parameter, extracts all users
    -- exception: will not generate script if user is an alias or DB has alias users
    set nocount on
    set ansi_warnings off
    declare @sql nvarchar(500)
    -- grant db access
    print 'use ' + quotename(db_name()) + char(13) + char(13)
    if @user is null
    begin
    select 'exec sp_grantdbaccess ' + quotename(l.name) + ' , ' + quotename(u.name)
    from sysusers u , master..syslogins l
    where u.sid = l.sid and u.name not in ('dbo')
    if exists ( select 1 from sysusers where isaliased = 1)
    begin
    print 'exception! aliased user. can not generate script '
    goto final
    end
    end
    else
    begin
    select 'exec sp_grantdbaccess ' + quotename(l.name) + ' , ' + quotename(u.name)
    from sysusers u , master..syslogins l
    where u.sid = l.sid and u.name not in ('dbo') and u.name = @user
    if exists ( select 1 from sysusers where isaliased = 1 and name = @user)
    begin
    print 'exception ! aliased user. can not generate script'
    goto final
    end
    end
    -- table to hold sp_helpuser
    create table #user
    (
    zusername sysname collate database_default null
    ,zgroupname sysname collate database_default null
    ,zloginname sysname collate database_default null
    ,zdefdbname sysname collate database_default null
    ,zuid smallint null
    ,zsid varbinary(85) null
    )
    if @user is null set @user = ''
    set @sql = 'insert into #user execute sp_helpuser ' + @user
    exec sp_executesql @sql
    --grant role
    select 'exec sp_addrolemember ' + quotename(zgroupname) + ' , ' + quotename(zusername)
    from #user
    where zgroupname not in ('public') and zusername not in ('dbo')
    --cleanup
    delete from #user
    if (object_id('tempdb..#user') is not null)
    drop table #user
    --final
    final:

    end
    --
    GO
    --------------------------
    thks,
    Mani
    CCNA,MCP,MCDBA,MCTS

Share This Page