User Rights Assignment Sql Server 2008

Server and Database Roles in SQL Server

All versions of SQL Server use role-based security, which allows you to assign permissions to a role, or group of users, instead of to individual users. Fixed server and fixed database roles have a fixed set of permissions assigned to them.

Fixed Server Roles

Fixed server roles have a fixed set of permissions and server-wide scope. They are intended for use in administering SQL Server and the permissions assigned to them cannot be changed. Logins can be assigned to fixed server roles without having a user account in a database.

Important

The fixed server role encompasses all other roles and has unlimited scope. Do not add principals to this role unless they are highly trusted. role members have irrevocable administrative privileges on all server databases and resources.

Be selective when you add users to fixed server roles. For example, the role allows users to insert the contents of any local file into a table, which could jeopardize data integrity. See SQL Server Books Online for the complete list of fixed server roles and permissions.

Fixed Database Roles

Fixed database roles have a pre-defined set of permissions that are designed to allow you to easily manage groups of permissions. Members of the role can perform all configuration and maintenance activities on the database.

For more information about SQL Server predefined roles, see the following resources.

Database Roles and Users

Logins must be mapped to database user accounts in order to work with database objects. Database users can then be added to database roles, inheriting any permission sets associated with those roles. All permissions can be granted.

You must also consider the role, the user account, and the account when you design security for your application.

The public Role

The role is contained in every database, which includes system databases. It cannot be dropped and you cannot add or remove users from it. Permissions granted to the role are inherited by all other users and roles because they belong to the role by default. Grant only the permissions you want all users to have.

The dbo User Account

The , or database owner, is a user account that has implied permissions to perform all activities in the database. Members of the fixed server role are automatically mapped to .

The user account is frequently confused with the fixed database role. The scope of is a database; the scope of is the whole server. Membership in the role does not confer user privileges.

The guest User Account

After a user has been authenticated and allowed to log in to an instance of SQL Server, a separate user account must exist in each database the user has to access. Requiring a user account in each database prevents users from connecting to an instance of SQL Server and accessing all the databases on a server. The existence of a user account in the database circumvents this requirement by allowing a login without a database user account to access a database.

The account is a built-in account in all versions of SQL Server. By default, it is disabled in new databases. If it is enabled, you can disable it by revoking its CONNECT permission by executing the Transact-SQL REVOKE CONNECT FROM GUEST statement.

Important

Avoid using the account; all logins without their own database permissions obtain the database permissions granted to this account. If you must use the account, grant it minimum permissions.

For more information about SQL Server logins, users and roles, see the following resources.

ResourceDescription
Identity and Access Control in SQL Server Books OnlineContains links to topics that describe principals, roles, credentials, securables and permissions.
Principals in SQL Server Books OnlineDescribes principals and contains links to topics that describe server and database roles.

See Also

Securing ADO.NET Applications
Application Security Scenarios in SQL Server
Authentication in SQL Server
Ownership and User-Schema Separation in SQL Server
Authorization and Permissions in SQL Server
ADO.NET Managed Providers and DataSet Developer Center

No. I don't think you were looking at the right section.

The section of interest would be the one that indicates the specific permissions that need to be granted to the user. I just had to do this for a Windows 2008 MSSQL installation and to run it anything other than a domain admin these were the required permissions:

*Adjust memory quotas for a process (SeIncreaseQuotaPrivilege)

*Bypass traverse checking (SeChangeNotifyPrivilege)

*Log on as a batch job (SeBatchLogonRight)

*Log on as a service (SeServiceLogonRight)

*Replace a process-level token (SeAssignPrimaryTokenPrivilege)

I realize this may be slightly different in 2005 though so here's what I would suggest.

  1. Create a simple regular user in the domain e.g DOMAIN\USER.
  2. Before adding it to any groups, assign the above permissions to it via GPO under Computer Configuration > Windows Settings > Security Settings > Local Policies/User rights assignment
  3. Do a gpupdate /force as admin so the policy is refreshed. Then try running the SQL service with that account i.e. DOMAIN\USER. If it doesn't work, add the additional permissions indicated in the link by Graeme such as Adjust memory quotas for a process (SeIncreaseQuotaPrivilege)...Add one at time, refresh and then try. See section labelled Reviewing Windows NT Rights and Privileges Granted for SQL Server Service Accounts for the extra permissions.

Hope this helps.

answered Oct 25 '10 at 21:42

0 Replies to “User Rights Assignment Sql Server 2008”

Lascia un Commento

L'indirizzo email non verrà pubblicato. I campi obbligatori sono contrassegnati *