Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Grant Users Permissions at Server Level Expand / Collapse
Author
Message
Posted Friday, February 15, 2013 4:03 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 8:39 AM
Points: 5, Visits: 128
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!!
Post #1420822
Posted Saturday, February 16, 2013 6:51 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 1:40 PM
Points: 85, Visits: 400
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
Post #1420890
Posted Saturday, February 16, 2013 7:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 8:39 AM
Points: 5, Visits: 128
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.
Post #1420892
Posted Sunday, February 17, 2013 7:39 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:34 AM
Points: 7,098, Visits: 12,606
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
Post #1420980
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse