June 22, 2011 at 1:24 am
Dear friends,
Hi...to all
i need your suggestion to block the user to acces the SQL server,coz, they are allowed to do some works, but i want to give limited access to them,
with SQL authentication,and windows authentication
please help me out.
Thanks in advance
June 22, 2011 at 5:27 am
I would either create a server or database role depending on the scope of work, then assign the nessesary access the person is allowed to complete to that role. Once done add the login to the role and they can only do what you have given them access to do.
This is good as well, as if you have a high level of staff turnover or contractors etc starting all the time, you will need to manually grant the access per user account, with the role you define the access once, then just add as many people to that role as they require the access.
This is the way that we manage developer security to the databases, in each DB there is a role called dev to which all the developer windows accounts have select and view definition to all objects, there is one SQL login for UAT environment which each development team leader has access to, to perform DDL changes and in DEV envornment developers have db_owner.
July 13, 2011 at 1:00 pm
A more thorough description of the problem you are trying to solve might get you more precise help.
Craig Outcalt
July 15, 2011 at 12:01 am
Hi,
I would suggest you first to get the details of the task user wants to perform against any DBs in SQL server. These would help you to identify what server or database level roles can be mapped to the user.
Below are the DB level roles available:
db_owner : Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database, and can also drop the database.
db_securityadmin : Members of the db_securityadmin fixed database role can modify role membership and manage permissions. Adding principals to this role could enable unintended privilege escalation.
db_accessadmin : Members of the db_accessadmin fixed database role can add or remove access to the database for Windows logins, Windows groups, and SQL Server logins.
db_backupoperator : Members of the db_backupoperator fixed database role can back up the database.
db_ddladmin : Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database.
db_datawriter: Members of the db_datawriter fixed database role can add, delete, or change data in all user tables.
db_datareader : Members of the db_datareader fixed database role can read all data from all user tables.
db_denydatawriter : Members of the db_denydatawriter fixed database role cannot add, modify, or delete any data in the user tables within a database.
db_denydatareader: Members of the db_denydatareader fixed database role cannot read any data in the user tables within a database.
Below are the server level roles available:
sysadmin : Members of the sysadmin fixed server role can perform any activity in the server.
serveradmin : Members of the serveradmin fixed server role can change server-wide configuration options and shut down the server.
securityadmin : Members of the securityadmin fixed server role manage logins and their properties. They can GRANT, DENY, and REVOKE server-level permissions. They can also GRANT, DENY, and REVOKE database-level permissions if they have access to a database. Additionally, they can reset passwords for SQL Server logins.
Security NoteThe ability to grant access to the Database Engine and to configure user permissions allows the security admin to assign most server permissions. The securityadmin role should be treated as equivalent to the sysadmin role.
processadmin : Members of the processadmin fixed server role can end processes that are running in an instance of SQL Server.
setupadmin : Members of the setupadmin fixed server role can add and remove linked servers.
bulkadmin : Members of the bulkadmin fixed server role can run the BULK INSERT statement.
diskadmin : The diskadmin fixed server role is used for managing disk files.
dbcreator : Members of the dbcreator fixed server role can create, alter, drop, and restore any database.
public : Every SQL Server login belongs to the public server role. When a server principal has not been granted or denied specific permissions on a securable object, the user inherits the permissions granted to public on that object. Only assign public permissions on any object when you want the object to be available to all users.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply