April 25, 2012 at 2:07 pm
Hello,
I have to revoke all sql server access (DB,SSIS,Agent , SSAS(About to start the service)) for the built in administrator /domain admin. how to get to know who are all have access to SQL server by domain login/Account.
current the all sql services is running on a domain admin account (as it is same account configured for all the servers).
How to achieve this without hampering the production and give access only to the person whom needs to be given.
any link or document will be helpful.
Regards
Durai Nagarajan
April 25, 2012 at 9:34 pm
durai nagarajan (4/25/2012)
Hello,I have to revoke all sql server access (DB,SSIS,Agent , SSAS(About to start the service)) for the built in administrator /domain admin.
There is no way to completely prevent a local admin from gaining access to your SQL server. By default the BUILTIN\administrators group is not granted access. If that group is present it was there prior to an upgrade from SQL 2000 or was added if we're talking about an instance that has only ever been SQL 2005.
how to get to know who are all have access to SQL server by domain login/Account.
You can check all logins with this query:
select * from sys.server_principals
current the all sql services is running on a domain admin account (as it is same account configured for all the servers).
How to achieve this without hampering the production and give access only to the person whom needs to be given.
any link or document will be helpful.
SQL Server 2005 Security Best Practices
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 26, 2012 at 9:34 am
can i add individual accounts in sql and block there access will this work.
If i have the service login though which the SQL service is running with that i can do something for blocking the access.
Tthe query you have given is not giving the administatrors name but still they have access to SQL.
kindly help.
Regards
Durai Nagarajan
April 26, 2012 at 9:39 am
durai nagarajan (4/26/2012)
can i add individual accounts in sql and block there access will this work.Tthe query you have given is not giving the administatrors name but still they have access to SQL.
That's the best you can do. It will prevent the casual system administrator from accessing the SQL Server, but again, this is the general case. If an admin really wants in, they can always get in. It would be a disruptive process, i.e. they would have to take down the instance for a bit, but you cannot prevent the access.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 30, 2012 at 3:43 am
another question if i atleast want to block their select and update access is it possible.
can we track DDL, DML statment updation directly on DB with user name and time(should not track through applications).
Regards
Durai Nagarajan
April 30, 2012 at 8:46 am
durai nagarajan (4/30/2012)
another question if i atleast want to block their select and update access is it possible.
SELECT is not an actionable event in SQL Server, so if you want to block SELECT you have to revoke or DENY the SELECT permission.
can we track DDL, DML statment updation directly on DB with user name and time(should not track through applications).
Sure...as of SQL Server 2008 there is a formalized subsystem call SQL Server Audit that can help you with this type of task. It requires Enterprise Edition or above. Read more here: SQL Server Auditing
I am assuming you're on SQL Server 2005 given the Forum we're in. SQL Server 2005 includes built-in c2 Audit capabilities. For anything custom you can accomplish your result by crafting your own Server Side Trace and reporting from the output.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 30, 2012 at 1:32 pm
durai nagarajan (4/30/2012)
another question if i atleast want to block their select and update access is it possible.can we track DDL, DML statment updation directly on DB with user name and time(should not track through applications).
DDL's - yes in default trace as well as reports(right click on db\reports\Schema changes report)
DML's - AFAIK only through triggers.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply