zsarv (2/16/2013)
Thanks for the response.The role for the administrators is not a problem as sysadmin would be what I'd want and is done at the server level. For the general users, I also wanted a server level permission. The reason for that is so when restoring databases to other environments permissions wouldn't have to be redefined on each database. Basically, trying to mimic SQL 2012's user-defined server roles in SQL 2008 R2.
SQL 2012 User-defined Server Roles are not what you are thinking they are. They are a collection of server-level permissions available in Role form, analogous to how a User-defined Database Role is a collection of database-level permissions. Neither a User-defined Server or Database Role cross the instance-database boundary in the way you are thinking.
In order to grant database-level permissions you have to grant them to a Database Role or User. When you restore a database to a new instance all the permissions still exist in that database for the Database Roles and Users but you need to link the Users to new Logins, or have Logins on both instances with the same SID so the re-linking is implicitly done when you restore the database.
If you want complete portability of a database from instance-to-instance with no pre- or post-restore work then you may want to look into Contained Databases once you get to SQL 2012.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato