Functional Account SA No Connection

Miss understanding the functional has sysadmin security permission within
SQL Server. Need to make sure this account cannot be log into SQL Server
database server but can used to run SSIS packages.

Please help me resolve this issue.

Thanks


"Uri Dimant" wrote:

> Joe
>
> create trigger AuditLogin_sa
> /* server means instance level*/
> on all server
> /* We specify the logon event at this stage
> – If there are more than one connections of sa,
> – Issue a rollback*/
> for logon
> as begin
> IF ORIGINAL_LOGIN()= 'sa' AND
> (SELECT COUNT(*) FROM sys.dm_exec_sessions
> WHERE is_user_process = 1 AND
> original_login_name = 'sa') > 1
> ROLLBACK;
>
> end
> go
>
>
> "Joe K." wrote in message
>
> >
> > I have SQL Server 2005 database server that I have set up to running
> > several
> > SSIS packages and other processes.
> >
> > How can I modify this account by t-sql script or using SSMS to make sure
> > no
> > user has the ability to log with this account?
> >
> > I have SQL Server 2000 database server that I have set up to running
> > several
> > DTS packages and other processes.
> >
> > How can I modify this account by t-sql script or using SQL Server
> > Enterprise
> > Manager to make sure no user has the ability to log with this account?
> >
> > Thanks,
>
>
>
 >> Stay informed about: Functional Account SA No Connection 

Joe K. (JoeK@discussions.microsoft.com) writes:
> I have SQL Server 2005 database server that I have set up to running
> several SSIS packages and other processes.
>
> How can I modify this account by t-sql script or using SSMS to make
> sure no user has the ability to log with this account?
>
> I have SQL Server 2000 database server that I have set up to running
> several DTS packages and other processes.
>
> How can I modify this account by t-sql script or using SQL Server
> Enterprise Manager to make sure no user has the ability to log with this
> account?

In SQL 2005, you could do this with a login trigger as Uri suggestion,
but you would have to do it differently, you would need to check if the
application is SSIS. Then again, it's trivial to forge the application
name you login, as this is just part of the connection string.

In SQL 2000 there is definitely no way to do this.

--
Erland Sommarskog, SQL Server MVP, esquel.DeleteThis@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 >> Stay informed about: Functional Account SA No Connection