Grant Users Permissions at Server Level

Question

Post reply

Grant Users Permissions at Server Level

zsarv

SSC Veteran

Points: 287
More actions
February 15, 2013 at 4:03 pm

#268661

Hello,
I am looking for a way to restrict access to a SQL Server where only a small group would have admin and another large group have EXECUTE and SELECT. Ideally I would want to do this at the instance level, instead of creating a database role in each database.
Is this possible? Any help greatly appreciated!!

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

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

SQLAJ Ten Centuries Points: 1359 More actions · Answer 1

Group A - Grant admin permissions - Server and Database level permissions

Group B - Grant Select / Execute - Database level permissions.

Using database mapping for the group only needing Select and Execute to specific databases.

You may want to do some reading so you gain a better understand the different levels of permissions.

http://msdn.microsoft.com/en-us/library/ms188659(v=sql.105).aspx

______________________________
AJ Mendo | @SQLAJ

zsarv SSC Veteran Points: 287 More actions · Answer 2

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.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 3

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