SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Grant Users Permissions at Server Level


Grant Users Permissions at Server Level

Author
Message
zsarv
zsarv
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 144
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!!
SQLAJ
SQLAJ
Old Hand
Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)

Group: General Forum Members
Points: 313 Visits: 410
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
zsarv
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 144
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
Orlando Colamatteo
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39016 Visits: 14411
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search