(Msg. 1) Posted: Mon Dec 04, 2006 9:45 am
Post subject: Retrieve SQL Server data via MS Query to spreadsheet Archived from groups: microsoft>public>excel>querydao (more info?)
Here is a security question: what way can I restrict the access to SQL
Server 2005 database objects ?
I'm going to let some my users use some tables, views to create their own
MSQuery queries to retrieve data from SQLServer, but not all ones! (I have
hundreds of tables, views, sprocs...)
What is the bast practices both from server side and Excel-side for this
kind of restriction ?
(Msg. 2) Posted: Tue Dec 12, 2006 11:33 pm
Post subject: Re: Retrieve SQL Server data via MS Query to spreadsheet [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
I think your main question is best addressed in a SQL Server group. Remeber
that Excel will only be a front end for creating a query and receiving the
results. All security issues are dealt with by the SQL Server administrator.
From an Excel point-of-view, the best practise I can think of is not to
store your password(s) within your spreadsheet.
"szabti" <tibor.szabo.RemoveThis@iconsulting.hu> wrote in message
news:ewGgV$3FHHA.1080@TK2MSFTNGP05.phx.gbl...
>
> Here is a security question: what way can I restrict the access to SQL
> Server 2005 database objects ?
>
> I'm going to let some my users use some tables, views to create their own
> MSQuery queries to retrieve data from SQLServer, but not all ones! (I have
> hundreds of tables, views, sprocs...)
>
> What is the bast practices both from server side and Excel-side for this
> kind of restriction ?
>
>
> Thanks,
>
> szabti
>
(Msg. 3) Posted: Wed Jan 10, 2007 1:30 pm
Post subject: Re: Retrieve SQL Server data via MS Query to spreadsheet [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
Here is a suggestion for SQL Server security.
1. Create a database role in the database. (ex: db_reportwriters).
2. Grant that role SELECT rights on the necessary table and views.
3. Give the users network login access to the server and database.
Add the user login to the database role that was created.
If the users will only be using the views you only need to grant them
SELECT to the views and not the underlying tables.
You could also consider creating stored procedures that accept
parameters for the users. That is the most secure and is accepted as
the best performing method. If you use stored procedures then you only
need to grant EXECUTE rights to the stored procedures and none of the
underlying objects.
All times are: Eastern Time (US & Canada) (change)
Page 1 of 1
You can post new topics in this forum You can reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
Home
Forums
Home
FAQ
Profile
Private Messages
Log in
Office