Post reply

Managing user and their access to database after edtion change in sql2005

  • June 18, 2009 at 3:55 am

    #206037

    Hi,

    We are downgrading to SQL Server Standard Edition from Enterprise

    Edition.After installing Enterprise Edition how can i make sure that

    all the users are created and the users have the same role and access

    to the database.

    Regards

    Prasanna.

  • June 18, 2009 at 6:29 am

    #1011678

    Hi Prasanna,

    If you're downgrading SQL from Ent to Std, then you must first get a backup of all your DBs (which I assume you have). Then, you can script all your accounts using sp_help_revlogin sp. When you restore all your databases into the new standard SQL, you run the script provided by the sp_help_revlogin sp and that's it. All your accounts will have the same permission level as before.

    Hope this helps,

    Alejandro

    Alejandro Pelc

  • June 18, 2009 at 6:52 am

    #1011699

    Thanks a lot!! Alejandro

    But if i have a user to which i have granted exec permission on few

    Stored Procedures and Insert right on some tables how can i ensure

    that the user will have the same rights after the activity.

    Regards

    Prasanna.

  • June 18, 2009 at 7:18 am

    #1011718

    Hi Prasanna,

    All the security of the database is stored on the db itself, so if you restore / attach a database, all the permissions are restored as well. The usual problem is that the SIDs on the DB are not the same as the SID on the SQL server (when you're moving from one server to another), and that's why you need the sp_help_revlogin sp. This stored procedure will recreate the users on the new SQL std server with the same SIDs as the ones in the previous Ent SQL.

    Cheers,

    Alejandro

    Alejandro Pelc

Viewing 4 posts - 1 through 3 (of 3 total)

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