Re: does sql user need to be an admin?
From: Dan Guzman (danguzman@nospam-earthlink.net)
Date: 12/10/02
- Next message: Alvin Zhao[MSFT]: "RE: Exporting data to another server"
- Previous message: Dan Guzman: "Re: Error 21776: after transferring databases"
- In reply to: Dan C.: "does sql user need to be an admin?"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
From: "Dan Guzman" <danguzman@nospam-earthlink.net> Date: Mon, 9 Dec 2002 22:16:45 -0600
User accounts need to be granted access to SQL Server in order to
connect; file permissions do not control database access. By default,
members of the local Administrator group access SQL Server via the
'BUILTIN\Administrators' login. This account is a member of the
powerful sysadmin server role and should not be used for routine
application access.
Below are scripts for the basic SQL Server security administration tasks
needed to grant an account permissions to connect and use a database
object. See the SQL Server Books Online
<adminsql.chm::/ad_security_05bt.htm> for details and instructions for
the Enterprise Manager equivalents.
Grant a Windows account access to SQL Server:
EXEC sp_grantlogin 'MyDomain\MyAccount'
GO
Grant a Windows account access to a SQL Server database:
USE MyDatabase
EXEC sp_grantdbaccess 'MyDomain\MyAccount'
GO
Add a database role:
USE MyDatabase
EXEC sp_addrole 'MyRole'
GO
Grant object permissions to a role:
USE MyDatabase
GRANT SELECT, INSERT, UPDATE, DELETE
ON MyTable TO MyRole
Add a user to a database role:
USE MyDatabase
EXEC sp_addrolemember 'MyRole', 'MyDomain\MyAccount'
GO
-- Hope this helps. Dan Guzman SQL Server MVP ----------------------- SQL FAQ links (courtesy Neil Pike): http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800 http://www.sqlserverfaq.com http://www.mssqlserver.com/faq ----------------------- "Dan C." <danc@nospam.com> wrote in message news:057701c29fec$669c9030$d3f82ecf@TK2MSFTNGXA10... > I have an application that logs onto SQL server using > integrated Windows authentication. > > On client machine: > Created an account called "APPUSER" that belongs to > the "Users" group. The application (actually a web app) > uses impersonation to create a database connection using > that account. > > On server machine: > Created the same account and also added it to SQL server > with rights to the desired table. > > The problem I have is that if that account does not belong > to the "Administrators" group, I get a message that the > user is not a member of a trusted SQL connection. If the > account belongs to the administrators group then > everything works perfectly. > > I do not want clients to use an administrative account to > access the database. Does anybody know what I am > missing? "Everyone" has "full control" to the sql server > directory. > > Thanks in advance. > > Dan
- Next message: Alvin Zhao[MSFT]: "RE: Exporting data to another server"
- Previous message: Dan Guzman: "Re: Error 21776: after transferring databases"
- In reply to: Dan C.: "does sql user need to be an admin?"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
