Need some help in finding if my databases are on security pack three.
Thanksselect @.@.version
the version number returned must be equal or superior to 8.00.762.
Jacques.
"Robert Salazar" <rsalazar@.cbbank.com> a crit dans le message de
news:9318FB2B-7240-494C-A1F9-F7E1C563CA77@.microsoft.com...
> Need some help in finding if my databases are on security pack three.
> Thanks|||Hi,
Execute "select @.@.version " from query analyzer.
If the value is "8.00.760" , then it is service pack 3.
Look into the attached link for service pack versions:-
This link gives you the version numbers and will also redirect to download
locations.
Thanks
Hari
MCDBA
"Robert Salazar" <rsalazar@.cbbank.com> wrote in message
news:9318FB2B-7240-494C-A1F9-F7E1C563CA77@.microsoft.com...
> Need some help in finding if my databases are on security pack three.
> Thankssql
Showing posts with label security. Show all posts
Showing posts with label security. Show all posts
Wednesday, March 21, 2012
Monday, March 19, 2012
Database Security Question - Can this be done?
Hello,
I am new to MS SQL Server and I am in the process of implementing a
database system which introduces an interesting security issue that I
was hoping some one could advise me on.
BACKGROUND: I am developing a client / server application that which
requires users to be able to download data from a global database and
then save this information in a local database. This enables them to
work offline and upload their local data to the global database at a
later date. FYI: The global DB is MS SQL, and the local database is
Paradox.
THE PROBLEM: The issue is that I dont want to give users the
ability/permissions to update, delete records from the global database
- because this would make it easy for hackers to simply corrupt the
database (i.e. delete * from <table> ). Also, the global database
contains data from a selection of companies and I must ensure that each
user can not see the other company's data.
So to summarise I have the following issues?
1. How do I restrict what users can see?
2. How do I prevent users from accessing data I dont want them to
manipulate (ie. restricting update / delete statements).
I would be gratful for any assistance you can provide.
Best regards
Spencer
(satest@.hotmail.com)The short answer is to use stored procedures and place execute permission on
those.
You can then filter out what can be seen by who.
Other methods include views.
Basically don't permission directly on the base tables.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Spence" <satest@.hotmail.com> wrote in message
news:1130760231.566603.254620@.g43g2000cwa.googlegroups.com...
> Hello,
> I am new to MS SQL Server and I am in the process of implementing a
> database system which introduces an interesting security issue that I
> was hoping some one could advise me on.
> BACKGROUND: I am developing a client / server application that which
> requires users to be able to download data from a global database and
> then save this information in a local database. This enables them to
> work offline and upload their local data to the global database at a
> later date. FYI: The global DB is MS SQL, and the local database is
> Paradox.
> THE PROBLEM: The issue is that I dont want to give users the
> ability/permissions to update, delete records from the global database
> - because this would make it easy for hackers to simply corrupt the
> database (i.e. delete * from <table> ). Also, the global database
> contains data from a selection of companies and I must ensure that each
> user can not see the other company's data.
> So to summarise I have the following issues?
> 1. How do I restrict what users can see?
> 2. How do I prevent users from accessing data I dont want them to
> manipulate (ie. restricting update / delete statements).
> I would be gratful for any assistance you can provide.
> Best regards
> Spencer
> (satest@.hotmail.com)
>|||From what you have described, the users don't really need access to the
Global database at all. In fact, they don't even need a login to the server.
What you can use is a DTS package that exports the appropriate from the
Global database to a distributed offline Paradox database located on a
network folder that is accessable by the users. Once the users have finished
inserting/updating/deleting the Paradox database, another DTS package can
migrate the data back into the Global database.
Also, you may want to consider using MS Access instead of Paradox for
the front end application/database. I don't know that much about Paradox,
but I would bet it's options for integrating with SQL Server are much more
limited than MS Access. Here is an article that describes the concepts of an
architecture for migrating data to and from a distributed MS Access
database.
http://www.microsoft.com/technet/pr...bldsysarch.mspx
"Spence" <satest@.hotmail.com> wrote in message
news:1130760231.566603.254620@.g43g2000cwa.googlegroups.com...
> Hello,
> I am new to MS SQL Server and I am in the process of implementing a
> database system which introduces an interesting security issue that I
> was hoping some one could advise me on.
> BACKGROUND: I am developing a client / server application that which
> requires users to be able to download data from a global database and
> then save this information in a local database. This enables them to
> work offline and upload their local data to the global database at a
> later date. FYI: The global DB is MS SQL, and the local database is
> Paradox.
> THE PROBLEM: The issue is that I dont want to give users the
> ability/permissions to update, delete records from the global database
> - because this would make it easy for hackers to simply corrupt the
> database (i.e. delete * from <table> ). Also, the global database
> contains data from a selection of companies and I must ensure that each
> user can not see the other company's data.
> So to summarise I have the following issues?
> 1. How do I restrict what users can see?
> 2. How do I prevent users from accessing data I dont want them to
> manipulate (ie. restricting update / delete statements).
> I would be gratful for any assistance you can provide.
> Best regards
> Spencer
> (satest@.hotmail.com)
>
I am new to MS SQL Server and I am in the process of implementing a
database system which introduces an interesting security issue that I
was hoping some one could advise me on.
BACKGROUND: I am developing a client / server application that which
requires users to be able to download data from a global database and
then save this information in a local database. This enables them to
work offline and upload their local data to the global database at a
later date. FYI: The global DB is MS SQL, and the local database is
Paradox.
THE PROBLEM: The issue is that I dont want to give users the
ability/permissions to update, delete records from the global database
- because this would make it easy for hackers to simply corrupt the
database (i.e. delete * from <table> ). Also, the global database
contains data from a selection of companies and I must ensure that each
user can not see the other company's data.
So to summarise I have the following issues?
1. How do I restrict what users can see?
2. How do I prevent users from accessing data I dont want them to
manipulate (ie. restricting update / delete statements).
I would be gratful for any assistance you can provide.
Best regards
Spencer
(satest@.hotmail.com)The short answer is to use stored procedures and place execute permission on
those.
You can then filter out what can be seen by who.
Other methods include views.
Basically don't permission directly on the base tables.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Spence" <satest@.hotmail.com> wrote in message
news:1130760231.566603.254620@.g43g2000cwa.googlegroups.com...
> Hello,
> I am new to MS SQL Server and I am in the process of implementing a
> database system which introduces an interesting security issue that I
> was hoping some one could advise me on.
> BACKGROUND: I am developing a client / server application that which
> requires users to be able to download data from a global database and
> then save this information in a local database. This enables them to
> work offline and upload their local data to the global database at a
> later date. FYI: The global DB is MS SQL, and the local database is
> Paradox.
> THE PROBLEM: The issue is that I dont want to give users the
> ability/permissions to update, delete records from the global database
> - because this would make it easy for hackers to simply corrupt the
> database (i.e. delete * from <table> ). Also, the global database
> contains data from a selection of companies and I must ensure that each
> user can not see the other company's data.
> So to summarise I have the following issues?
> 1. How do I restrict what users can see?
> 2. How do I prevent users from accessing data I dont want them to
> manipulate (ie. restricting update / delete statements).
> I would be gratful for any assistance you can provide.
> Best regards
> Spencer
> (satest@.hotmail.com)
>|||From what you have described, the users don't really need access to the
Global database at all. In fact, they don't even need a login to the server.
What you can use is a DTS package that exports the appropriate from the
Global database to a distributed offline Paradox database located on a
network folder that is accessable by the users. Once the users have finished
inserting/updating/deleting the Paradox database, another DTS package can
migrate the data back into the Global database.
Also, you may want to consider using MS Access instead of Paradox for
the front end application/database. I don't know that much about Paradox,
but I would bet it's options for integrating with SQL Server are much more
limited than MS Access. Here is an article that describes the concepts of an
architecture for migrating data to and from a distributed MS Access
database.
http://www.microsoft.com/technet/pr...bldsysarch.mspx
"Spence" <satest@.hotmail.com> wrote in message
news:1130760231.566603.254620@.g43g2000cwa.googlegroups.com...
> Hello,
> I am new to MS SQL Server and I am in the process of implementing a
> database system which introduces an interesting security issue that I
> was hoping some one could advise me on.
> BACKGROUND: I am developing a client / server application that which
> requires users to be able to download data from a global database and
> then save this information in a local database. This enables them to
> work offline and upload their local data to the global database at a
> later date. FYI: The global DB is MS SQL, and the local database is
> Paradox.
> THE PROBLEM: The issue is that I dont want to give users the
> ability/permissions to update, delete records from the global database
> - because this would make it easy for hackers to simply corrupt the
> database (i.e. delete * from <table> ). Also, the global database
> contains data from a selection of companies and I must ensure that each
> user can not see the other company's data.
> So to summarise I have the following issues?
> 1. How do I restrict what users can see?
> 2. How do I prevent users from accessing data I dont want them to
> manipulate (ie. restricting update / delete statements).
> I would be gratful for any assistance you can provide.
> Best regards
> Spencer
> (satest@.hotmail.com)
>
DataBase security problems
Hello every one
We have made a application which cancreate and restoredata base for MS SQLwe also set the password. we want to make it secure. problem is thatif data base files are copy from one system to other system and try to open then . they are openedwith out asking data basePass word can any told me solution so that our data base become secure
Thank
Hello!
I developed database driven VC++ application. I faced a problem, which is "how to protect my database against direct access". E.g. .when i copy data files from one server to another and then using to attach the database to the new server the data base files are openedwith out asking password .
I use MS SQL Server 2000 enterprise Edition as a DBMS and appropriate database.
I want to make possible to manipulate with data in my database only through my client application.
1. How do I define SA password and instance name in silent mode of MS SQL 2000 EE installation with Mixed type of Authentication?
2. If my database be attached to my new instance. Is it possible to copy my database, attach it to another instance and get a direct access to its objects?
|||Hi,actually you can′t secure it. The magic word in this case is prevention. Secure the directory that noone can connect to the server directory except the SQL Server Service user and administrator.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
|||
This topic has been discussed already here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=52094&SiteID=1.
Thanks
Laurentiu
Database security on a Local Network
This is regarding general protection of a database hosted on a network. I am developing a database application for my college library using VB.NET, that will reside on a network.
For some reasons, I did not want to hardcode the Database location in the application. Instead, when a user logs in, he can choose the database location using a folder browser control, if the location has changed.
1) My database is in Access
2) And, I am not moving the Database at run-time. The database will remain in its non-shared folder. And there will be a text file, that will act as a sort of pointer to the database location for my application, as I will read the DB path from the text file.|||
For some reasons, I did not want to hardcode the Database location in the application. Instead, when a user logs in, he can choose the database location using a folder browser control, if the location has changed.
Now, I realize that for this, I have to put the database in a shared folder, which makes it quite vulnerable. Having pondered over the problem for sometime, a solution that comes to my mind is to place a Text file in the same shared folder that always contains the correct path of the database. When a user chooses that folder, I will read the actual path of the database from the text file, and move the database to a non-shared folder.
I haven't yet implemented this approach, but felt it better to consult someone before. So, would this approach work, and is it a good idea.
For information purposes, I consider it important to mention that the database is in MS Access. I know this is not a place for discussing it, but this is a general security concern. So, I thought
people would not mind answering it....
Hi,
how aout securing the MDB file using the appropiate NTFS permissions and eventually additional Access password security or using an ldb file ? I don′t know if there can be concurrent users on the database, but coyping the database file to a shared folder will allow other users also to copy the file to another folder and working on it, for you having the trouble to bring the data together afterwards.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||More than the problem of bringing it together afterwards, I am worried about someone manipulating it mischievously. That's why I thought of putting the database in a non-shared folder on the server and a text file in a shared folder, which will always
contain the correct path of the DB on the server.
So, when a user logs in, he will select the path of the text file. As he
will do so with a folder chooser, he will not know that the folder contains a
text file & not the actual DB. Internally, I will read the database path from
that file in my application, & use that path to construct the connection
string...
I think this approach will shield the database from direct access on the network, using an explorer etc.
I already have Access password security, but still I dont want the database to be directly accessible on the network.
Can you elaborate a bit more on securing the Database on the server with NTFS permissions, in a way that my application can still access & manipulate it?|||
One thing, you cannot perform such move/copy with a SQL Server database as that will be in exclusive use of SQLengine.
Refer to KBA http://support.microsoft.com/kb/295234, http://support.microsoft.com/kb/307901 and link http://vb123.com/toolshed/links/map/opr.htm for more information.
|||There are two things I will mention again here...1) My database is in Access
2) And, I am not moving the Database at run-time. The database will remain in its non-shared folder. And there will be a text file, that will act as a sort of pointer to the database location for my application, as I will read the DB path from the text file.|||
I suggest posting this question on a Microsoft Access or Microsoft Visual Basic forum instead of this one. This forum is used for posting questions related to Microsoft SQL Server security features, as you observed, and your question is Access specific.
Thanks
Laurentiu
Hi,
You can do this well with NTFS permission with Read right for everyone in Group so that everyone can read the database files (this will not make user to able to copy files/folder too) , and give write/modify permission to specific users who need to insert/update/delete records in your access database. Refer www.windowsecurity.com/articles/
HTH
Hemantgiri S. Goswami
Database Security in ADP
thanks mary. i'm not having any problems as the developer. my
machine has EM so no biggie. i want to allow a small hand full of
managers to be able to add users to the application. what i don't
want to do is install EM on each of their machines. they've made it
clear that they want to do it from within the app. so i need to write
my own little GUI i suppose.
adding sql server logins is a snap (sp_adduser) but because the app
uses integrated security i need to provide a dialog similar to that of
EM where the users can select a domain and view a list of users within
that domain. not quite sure how to accomplish that. do i need
sql-dmo? any ideas would be appreciated.
TIA
> To address the issue of people not being able to administer SQLS/MSDE
> security through an easy-to-use GUI, msft has made the Developer
> edition available for $49. The answer is, use the Enterprise Manager.
> -- Mary
> Microsoft Access Developer's Guide to SQL Server
> http://www.amazon.com/exec/obidos/ASIN/0672319446
>Yes -- SQL-DMO is what you need.
-- Mary
MCW Technologies
http://www.mcwtech.com
On 11 Mar 2004 06:54:25 -0800, teddy_theo@.yahoo.com (Ted
Theodoropoulos) wrote:
>thanks mary. i'm not having any problems as the developer. my
>machine has EM so no biggie. i want to allow a small hand full of
>managers to be able to add users to the application. what i don't
>want to do is install EM on each of their machines. they've made it
>clear that they want to do it from within the app. so i need to write
>my own little GUI i suppose.
>adding sql server logins is a snap (sp_adduser) but because the app
>uses integrated security i need to provide a dialog similar to that of
>EM where the users can select a domain and view a list of users within
>that domain. not quite sure how to accomplish that. do i need
>sql-dmo? any ideas would be appreciated.
>TIA
>
machine has EM so no biggie. i want to allow a small hand full of
managers to be able to add users to the application. what i don't
want to do is install EM on each of their machines. they've made it
clear that they want to do it from within the app. so i need to write
my own little GUI i suppose.
adding sql server logins is a snap (sp_adduser) but because the app
uses integrated security i need to provide a dialog similar to that of
EM where the users can select a domain and view a list of users within
that domain. not quite sure how to accomplish that. do i need
sql-dmo? any ideas would be appreciated.
TIA
> To address the issue of people not being able to administer SQLS/MSDE
> security through an easy-to-use GUI, msft has made the Developer
> edition available for $49. The answer is, use the Enterprise Manager.
> -- Mary
> Microsoft Access Developer's Guide to SQL Server
> http://www.amazon.com/exec/obidos/ASIN/0672319446
>Yes -- SQL-DMO is what you need.
-- Mary
MCW Technologies
http://www.mcwtech.com
On 11 Mar 2004 06:54:25 -0800, teddy_theo@.yahoo.com (Ted
Theodoropoulos) wrote:
>thanks mary. i'm not having any problems as the developer. my
>machine has EM so no biggie. i want to allow a small hand full of
>managers to be able to add users to the application. what i don't
>want to do is install EM on each of their machines. they've made it
>clear that they want to do it from within the app. so i need to write
>my own little GUI i suppose.
>adding sql server logins is a snap (sp_adduser) but because the app
>uses integrated security i need to provide a dialog similar to that of
>EM where the users can select a domain and view a list of users within
>that domain. not quite sure how to accomplish that. do i need
>sql-dmo? any ideas would be appreciated.
>TIA
>
Database security design with ASP.net and form-based authentication
I'm setting up an ASP.Net intranet application with a SQL Server 2000
database. The application uses form-based authentication which is supported
by the following tables: User, Role, UserRole (where each user is assigned
specific roles). The system will have several different roles and users can
belong to multiple roles. As an example, let's say I have the following
roles: data entry, guest/view only, admin, report viewer. I'm guessing now
the system will have about 20 unique users. I've figured out how to
implement the role-based part on ASP.Net, but I'm stuck trying to decide the
best way to secure my database tables and stored procedures.
We're on a Novell network, so I'm using SQL Server authentication. At it's
simplest, I could just have one login for my database and lock down all the
tables and stored procedures to that one login. I'd like to have the
security a little tighter, though, so that only users who belong to the
administrative role can access the administrative procedures, only data
entry members can access the data entry procedures, etc.
I've thought of the following scenarios, but none makes me happy:
1) Create a SQL Server login for each user of the application and assign
them to roles. Then lock the tables and procedures down to the appropriate
roles.
I don't want to do this because I want an administrative user to be able to
create new application users through the Web application. This wouldn't be
possible as I don't have rights to create new SQL Server logins. I'd have
to go to my DB Admin each time we want to add a new user, which isn't really
acceptable.
2) Use SQL application roles to secure tables and procedures. We've used
these in other applications, but I'd like to stay away from them since
connection pooling doesn't work with them.
3) Use a set number of SQL Logins for each pre-defined role (data entry,
guest, admin, report viewer) and grant those logins permission to tables and
procedures as appropriate. I think this is my favorite method right now,
but then I'm not sure how to manage the multiple usernames and passwords.
Where do I store them and how does the application decide which one to use?
This is where maybe this question is more appropriate in an ASP.Net group,
but I thought I'd try here first.
I'm wondering what other people have done in this scenario?
Thanks,
Diane Y.Since you already have forms-based security, why not use a single SQL login
for all database access?
Hope this helps.
Dan Guzman
SQL Server MVP
"Diane Y" <diane.yocom@.spam.seattle.gov> wrote in message
news:OUiKBQwQGHA.5500@.TK2MSFTNGP12.phx.gbl...
> I'm setting up an ASP.Net intranet application with a SQL Server 2000
> database. The application uses form-based authentication which is
> supported
> by the following tables: User, Role, UserRole (where each user is assigned
> specific roles). The system will have several different roles and users
> can
> belong to multiple roles. As an example, let's say I have the following
> roles: data entry, guest/view only, admin, report viewer. I'm guessing
> now
> the system will have about 20 unique users. I've figured out how to
> implement the role-based part on ASP.Net, but I'm stuck trying to decide
> the
> best way to secure my database tables and stored procedures.
> We're on a Novell network, so I'm using SQL Server authentication. At
> it's
> simplest, I could just have one login for my database and lock down all
> the
> tables and stored procedures to that one login. I'd like to have the
> security a little tighter, though, so that only users who belong to the
> administrative role can access the administrative procedures, only data
> entry members can access the data entry procedures, etc.
> I've thought of the following scenarios, but none makes me happy:
> 1) Create a SQL Server login for each user of the application and assign
> them to roles. Then lock the tables and procedures down to the
> appropriate
> roles.
> I don't want to do this because I want an administrative user to be able
> to
> create new application users through the Web application. This wouldn't
> be
> possible as I don't have rights to create new SQL Server logins. I'd have
> to go to my DB Admin each time we want to add a new user, which isn't
> really
> acceptable.
> 2) Use SQL application roles to secure tables and procedures. We've used
> these in other applications, but I'd like to stay away from them since
> connection pooling doesn't work with them.
> 3) Use a set number of SQL Logins for each pre-defined role (data entry,
> guest, admin, report viewer) and grant those logins permission to tables
> and
> procedures as appropriate. I think this is my favorite method right now,
> but then I'm not sure how to manage the multiple usernames and passwords.
> Where do I store them and how does the application decide which one to
> use?
> This is where maybe this question is more appropriate in an ASP.Net group,
> but I thought I'd try here first.
> I'm wondering what other people have done in this scenario?
> Thanks,
> Diane Y.
>|||That's actually the way I have it setup now and it's what I've mostly done
in the past. I just really liked how, when I used multiple application
roles, I was able to give only certain roles permission to certain stored
procedures. So, I was just wondering what others have done...
Diane
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eoNTQdyQGHA.5552@.TK2MSFTNGP10.phx.gbl...
> Since you already have forms-based security, why not use a single SQL
login
> for all database access?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Diane Y" <diane.yocom@.spam.seattle.gov> wrote in message
> news:OUiKBQwQGHA.5500@.TK2MSFTNGP12.phx.gbl...
assigned[vbcol=seagreen]
have[vbcol=seagreen]
used[vbcol=seagreen]
now,[vbcol=seagreen]
passwords.[vbcol=seagreen]
group,[vbcol=seagreen]
>|||> So, I was just wondering what others have done...
I usually opt for option #1 (individual logins/database role membership) for
intranet apps, . This allows SQL Server to control security from both
within and outside your application. Unfortunately, this isn't an option
for you due to the reasons you stated.
Application roles vs. role-based logins are similar approaches. These work
well when a user belongs to a single role so that you can use the same
security context for a given user's database access. However, this method
is problematic in your case because a user can belong to multiple roles
(cumulative permissions). The difficult question is how you decide which
database security context to enable when a user belongs to multiple roles
and multiple roles are associated with a particular application feature.
For example, if user Mary belongs to both DataEntry and ReportViewer roles
and your security is such that either role can view a report, which role
should be used as the database security context?
As long as you can define your business rules for identifying the
appropriate database security context, the implementation is easy. All you
need to do is store the application role name or login along with the
password (encrypted) in your Role table. You can then use that for database
access.
IMHO, the single login approach is best in your situation since you don't
want DBA involvement for security administration.
Hope this helps.
Dan Guzman
SQL Server MVP
"Diane Y" <diane.yocom@.spam.seattle.gov> wrote in message
news:e8WPEH5QGHA.2300@.TK2MSFTNGP11.phx.gbl...
> That's actually the way I have it setup now and it's what I've mostly done
> in the past. I just really liked how, when I used multiple application
> roles, I was able to give only certain roles permission to certain stored
> procedures. So, I was just wondering what others have done...
> Diane
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:eoNTQdyQGHA.5552@.TK2MSFTNGP10.phx.gbl...
> login
> assigned
> have
> used
> now,
> passwords.
> group,
>
database. The application uses form-based authentication which is supported
by the following tables: User, Role, UserRole (where each user is assigned
specific roles). The system will have several different roles and users can
belong to multiple roles. As an example, let's say I have the following
roles: data entry, guest/view only, admin, report viewer. I'm guessing now
the system will have about 20 unique users. I've figured out how to
implement the role-based part on ASP.Net, but I'm stuck trying to decide the
best way to secure my database tables and stored procedures.
We're on a Novell network, so I'm using SQL Server authentication. At it's
simplest, I could just have one login for my database and lock down all the
tables and stored procedures to that one login. I'd like to have the
security a little tighter, though, so that only users who belong to the
administrative role can access the administrative procedures, only data
entry members can access the data entry procedures, etc.
I've thought of the following scenarios, but none makes me happy:
1) Create a SQL Server login for each user of the application and assign
them to roles. Then lock the tables and procedures down to the appropriate
roles.
I don't want to do this because I want an administrative user to be able to
create new application users through the Web application. This wouldn't be
possible as I don't have rights to create new SQL Server logins. I'd have
to go to my DB Admin each time we want to add a new user, which isn't really
acceptable.
2) Use SQL application roles to secure tables and procedures. We've used
these in other applications, but I'd like to stay away from them since
connection pooling doesn't work with them.
3) Use a set number of SQL Logins for each pre-defined role (data entry,
guest, admin, report viewer) and grant those logins permission to tables and
procedures as appropriate. I think this is my favorite method right now,
but then I'm not sure how to manage the multiple usernames and passwords.
Where do I store them and how does the application decide which one to use?
This is where maybe this question is more appropriate in an ASP.Net group,
but I thought I'd try here first.
I'm wondering what other people have done in this scenario?
Thanks,
Diane Y.Since you already have forms-based security, why not use a single SQL login
for all database access?
Hope this helps.
Dan Guzman
SQL Server MVP
"Diane Y" <diane.yocom@.spam.seattle.gov> wrote in message
news:OUiKBQwQGHA.5500@.TK2MSFTNGP12.phx.gbl...
> I'm setting up an ASP.Net intranet application with a SQL Server 2000
> database. The application uses form-based authentication which is
> supported
> by the following tables: User, Role, UserRole (where each user is assigned
> specific roles). The system will have several different roles and users
> can
> belong to multiple roles. As an example, let's say I have the following
> roles: data entry, guest/view only, admin, report viewer. I'm guessing
> now
> the system will have about 20 unique users. I've figured out how to
> implement the role-based part on ASP.Net, but I'm stuck trying to decide
> the
> best way to secure my database tables and stored procedures.
> We're on a Novell network, so I'm using SQL Server authentication. At
> it's
> simplest, I could just have one login for my database and lock down all
> the
> tables and stored procedures to that one login. I'd like to have the
> security a little tighter, though, so that only users who belong to the
> administrative role can access the administrative procedures, only data
> entry members can access the data entry procedures, etc.
> I've thought of the following scenarios, but none makes me happy:
> 1) Create a SQL Server login for each user of the application and assign
> them to roles. Then lock the tables and procedures down to the
> appropriate
> roles.
> I don't want to do this because I want an administrative user to be able
> to
> create new application users through the Web application. This wouldn't
> be
> possible as I don't have rights to create new SQL Server logins. I'd have
> to go to my DB Admin each time we want to add a new user, which isn't
> really
> acceptable.
> 2) Use SQL application roles to secure tables and procedures. We've used
> these in other applications, but I'd like to stay away from them since
> connection pooling doesn't work with them.
> 3) Use a set number of SQL Logins for each pre-defined role (data entry,
> guest, admin, report viewer) and grant those logins permission to tables
> and
> procedures as appropriate. I think this is my favorite method right now,
> but then I'm not sure how to manage the multiple usernames and passwords.
> Where do I store them and how does the application decide which one to
> use?
> This is where maybe this question is more appropriate in an ASP.Net group,
> but I thought I'd try here first.
> I'm wondering what other people have done in this scenario?
> Thanks,
> Diane Y.
>|||That's actually the way I have it setup now and it's what I've mostly done
in the past. I just really liked how, when I used multiple application
roles, I was able to give only certain roles permission to certain stored
procedures. So, I was just wondering what others have done...
Diane
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eoNTQdyQGHA.5552@.TK2MSFTNGP10.phx.gbl...
> Since you already have forms-based security, why not use a single SQL
login
> for all database access?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Diane Y" <diane.yocom@.spam.seattle.gov> wrote in message
> news:OUiKBQwQGHA.5500@.TK2MSFTNGP12.phx.gbl...
assigned[vbcol=seagreen]
have[vbcol=seagreen]
used[vbcol=seagreen]
now,[vbcol=seagreen]
passwords.[vbcol=seagreen]
group,[vbcol=seagreen]
>|||> So, I was just wondering what others have done...
I usually opt for option #1 (individual logins/database role membership) for
intranet apps, . This allows SQL Server to control security from both
within and outside your application. Unfortunately, this isn't an option
for you due to the reasons you stated.
Application roles vs. role-based logins are similar approaches. These work
well when a user belongs to a single role so that you can use the same
security context for a given user's database access. However, this method
is problematic in your case because a user can belong to multiple roles
(cumulative permissions). The difficult question is how you decide which
database security context to enable when a user belongs to multiple roles
and multiple roles are associated with a particular application feature.
For example, if user Mary belongs to both DataEntry and ReportViewer roles
and your security is such that either role can view a report, which role
should be used as the database security context?
As long as you can define your business rules for identifying the
appropriate database security context, the implementation is easy. All you
need to do is store the application role name or login along with the
password (encrypted) in your Role table. You can then use that for database
access.
IMHO, the single login approach is best in your situation since you don't
want DBA involvement for security administration.
Hope this helps.
Dan Guzman
SQL Server MVP
"Diane Y" <diane.yocom@.spam.seattle.gov> wrote in message
news:e8WPEH5QGHA.2300@.TK2MSFTNGP11.phx.gbl...
> That's actually the way I have it setup now and it's what I've mostly done
> in the past. I just really liked how, when I used multiple application
> roles, I was able to give only certain roles permission to certain stored
> procedures. So, I was just wondering what others have done...
> Diane
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:eoNTQdyQGHA.5552@.TK2MSFTNGP10.phx.gbl...
> login
> assigned
> have
> used
> now,
> passwords.
> group,
>
Database Security
Hi experts, I would like to ask if it is feasible to limit the accessibility of an SA account in SQL 2005 in a specific database. The reason of doing this procedure is since we are deploying a package software to our client(s) we want to secure our own database to get tampered by our client(s).
No its not possible to restrict SA from any database. There are many post on this topic on this forum
check this
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1993336&SiteID=1
Madhu
|||Is there any suggestion on how could we secure our Database? for a possible tampering? or changing the data types.|||Create DDL trigger on this database and prevent tampering or log tampering of the db objects. Its very good option avaliable in sql server 2005. Generally, you should remove Built/AdminGroup,Guest from the database. Set strong password for SA
Madhu
|||Thanks to your effort. I will try this for now|||check my blog for some DDL script
http://madhuottapalam.blogspot.com/search?q=ddl+trigger
Madhu|||I just want to emphasize that (as Madhu mentioned) it is not possible to restrict members of sysadmin from any database. Using triggers and other mechanisms to try to avoid tampering can be very helpful for keeping honest people honest and to prevent modifying the schema by mistake, but a sysadmin with enough determination won’t be stopped by such mechanisms.
Thanks,
-Raul Garcia
SDE/T
SQL Server Engine
Database Security
Hi experts, I would like to ask if it is feasible to limit the accessibility of an SA account in SQL 2005 in a specific database. The reason of doing this procedure is since we are deploying a package software to our client(s) we want to secure our own database to get tampered by our client(s).
No its not possible to restrict SA from any database. There are many post on this topic on this forum
check this
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1993336&SiteID=1
Madhu
|||Is there any suggestion on how could we secure our Database? for a possible tampering? or changing the data types.|||Create DDL trigger on this database and prevent tampering or log tampering of the db objects. Its very good option avaliable in sql server 2005. Generally, you should remove Built/AdminGroup,Guest from the database. Set strong password for SA
Madhu
|||Thanks to your effort. I will try this for now|||check my blog for some DDL script
http://madhuottapalam.blogspot.com/search?q=ddl+trigger
Madhu|||I just want to emphasize that (as Madhu mentioned) it is not possible to restrict members of sysadmin from any database. Using triggers and other mechanisms to try to avoid tampering can be very helpful for keeping honest people honest and to prevent modifying the schema by mistake, but a sysadmin with enough determination won’t be stopped by such mechanisms.
Thanks,
-Raul Garcia
SDE/T
SQL Server Engine
Database Security
Hi,
I have created a database in server SRV1 with user 'aaa' as database owner
Know if some body detach this database from SRV1 and attach them on other
server same SRV2 with defrent sa and defrent users 'SA' user in SRV2 has ful
l
access to may database
How can restric my database for other server and other sa youser ther ?
Tanks .
Daryoush AjamiHi,
First of all restirct the access to your SQL Server. In this case no one can
detach the database and attach in SRV2.
FYI, If he have detach database access in sql server and copy the files from
operating system then he will be able to
attach the database in his server and view all tables and objects.
Thanks
hari
SQL Server MVP
"Ajami" <Ajami@.discussions.microsoft.com> wrote in message
news:34C340B7-2451-4E9C-8227-EA0AA0DF7573@.microsoft.com...
> Hi,
> I have created a database in server SRV1 with user 'aaa' as database owner
> Know if some body detach this database from SRV1 and attach them on other
> server same SRV2 with defrent sa and defrent users 'SA' user in SRV2 has
> full
> access to may database
> How can restric my database for other server and other sa youser ther ?
> Tanks .
> --
> Daryoush Ajami
I have created a database in server SRV1 with user 'aaa' as database owner
Know if some body detach this database from SRV1 and attach them on other
server same SRV2 with defrent sa and defrent users 'SA' user in SRV2 has ful
l
access to may database
How can restric my database for other server and other sa youser ther ?
Tanks .
Daryoush AjamiHi,
First of all restirct the access to your SQL Server. In this case no one can
detach the database and attach in SRV2.
FYI, If he have detach database access in sql server and copy the files from
operating system then he will be able to
attach the database in his server and view all tables and objects.
Thanks
hari
SQL Server MVP
"Ajami" <Ajami@.discussions.microsoft.com> wrote in message
news:34C340B7-2451-4E9C-8227-EA0AA0DF7573@.microsoft.com...
> Hi,
> I have created a database in server SRV1 with user 'aaa' as database owner
> Know if some body detach this database from SRV1 and attach them on other
> server same SRV2 with defrent sa and defrent users 'SA' user in SRV2 has
> full
> access to may database
> How can restric my database for other server and other sa youser ther ?
> Tanks .
> --
> Daryoush Ajami
Database Security
How i can Secure my database file from to be attached to
any other SERVER (sql server ) and view database design
Thanks"Said Fadel" <saidfadel@.hotmail.com> wrote in message
news:265001c4c104$ad703b20$a401280a@.phx.gbl...
> How i can Secure my database file from to be attached to
> any other SERVER (sql server ) and view database design
Are you asking, can your SQL Server 2000 database (and log) files be moved
to another remote server, keeping SQL Server on the original server? If so,
the database data and log files need to be keep on drives that appear to be
local to SQL Server, however these drives could be SAN attached.
Steve|||Typically this is done by securing access to the server itself - who
can access the directories where the data and log files are, who can
stop and start services, etc.
If you are referring to having a database that you give to a customer
and you don't want that customer to be able to view your design.
Likely the best was to manage this is through legal agreements.
Outside of that, you can look at some encryption mechanisms. You can
find links for these products in the encryption section of this FAQ:
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=22
-Sue
On Tue, 2 Nov 2004 09:52:10 -0800, "Said Fadel"
<saidfadel@.hotmail.com> wrote:
>How i can Secure my database file from to be attached to
>any other SERVER (sql server ) and view database design
>Thanks|||I want to deploy a SQL server database on my client windows 2003 server
; I
will have admin access on the server through Terminal services
The client server will have SQL server 2000 installed ; and I will use a
backup copy of my db and install it using Restor Database command in the
Enterprise manager
How can prevent my client from viewing/modifing my Database while he
has
admin access to the server ?
The database will host information relative to an ASP.net website that
is
hosted on IIS on the same server ; a username/password for this DB
should be
available also on the web.config file of the ASP.net website .. what is
the
minimum security setting that should be given to this username/password
to
allow the website manipulate the database ?
Please advice about that ; the reason I want to do this is to prevent my
client from reverse engineering my system analysis and databse design;
but
at the same time being able to use my ASP.net web application .
.
How can prevent my client from viewing/modifing my Database while he has
admin access to the server ?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
any other SERVER (sql server ) and view database design
Thanks"Said Fadel" <saidfadel@.hotmail.com> wrote in message
news:265001c4c104$ad703b20$a401280a@.phx.gbl...
> How i can Secure my database file from to be attached to
> any other SERVER (sql server ) and view database design
Are you asking, can your SQL Server 2000 database (and log) files be moved
to another remote server, keeping SQL Server on the original server? If so,
the database data and log files need to be keep on drives that appear to be
local to SQL Server, however these drives could be SAN attached.
Steve|||Typically this is done by securing access to the server itself - who
can access the directories where the data and log files are, who can
stop and start services, etc.
If you are referring to having a database that you give to a customer
and you don't want that customer to be able to view your design.
Likely the best was to manage this is through legal agreements.
Outside of that, you can look at some encryption mechanisms. You can
find links for these products in the encryption section of this FAQ:
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=22
-Sue
On Tue, 2 Nov 2004 09:52:10 -0800, "Said Fadel"
<saidfadel@.hotmail.com> wrote:
>How i can Secure my database file from to be attached to
>any other SERVER (sql server ) and view database design
>Thanks|||I want to deploy a SQL server database on my client windows 2003 server
; I
will have admin access on the server through Terminal services
The client server will have SQL server 2000 installed ; and I will use a
backup copy of my db and install it using Restor Database command in the
Enterprise manager
How can prevent my client from viewing/modifing my Database while he
has
admin access to the server ?
The database will host information relative to an ASP.net website that
is
hosted on IIS on the same server ; a username/password for this DB
should be
available also on the web.config file of the ASP.net website .. what is
the
minimum security setting that should be given to this username/password
to
allow the website manipulate the database ?
Please advice about that ; the reason I want to do this is to prevent my
client from reverse engineering my system analysis and databse design;
but
at the same time being able to use my ASP.net web application .
.
How can prevent my client from viewing/modifing my Database while he has
admin access to the server ?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Database security
How can sql server ensure that the data that is P&C cannot be accessed even by the dba? Is there a way to encrypt these data without comprising on the flexibility of sql?Man, that's a big question. Here's a good place to start reading:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_93u6.asp|||It should be possible to set up a role for your DBA that allows them to do basic maintenance, such as backups and restores, but does not allow them to access tables or procedures. Can't say I've done this, though.
If your DBA logs in as sa or dbo, then there is no way to hide anything that you don't encrypt yourself. DBO is database god (small g), and SA is server God (big g).
blindman|||hmm... in that case anybody that gotten hold of any of my full backup files too can just restore it and definately he/she will have sa authority and be able to view everything....|||Where are you leaving your backups? Shouldn't they enjoy the same security (file system, locked cabinet for tapes, etc) as your live database files? It's like leaving photocopies of you credit card statements around.
Did you read the article on encryption? It does provide a method of protecting your data from direct viewing, but it needs to be set up that way initially.|||bpdWork,
I don't see where in the article it talks about protecting your data from viewing within the database, just from viewing intercepted transmissions across the network. Have you done this?
debcwong,
The BACKUP command allows to supply a password that is then required in order to restore the file, although the sqlmaint Utility (used by the Maintenance Plan wizard) does not.
blindman|||No, I haven't. In fact, you can probably tell just how lazy I am by the fact that I didn't read the whole article.
Though, the second sentence says: "Encryption ensures that data remains secure by keeping the information hidden from everyone, even if the encrypted data is viewed directly," I cannot find any way of actually doing this.
I am able to encrypt Stored rocedures and Views so that their definitions are encrypted, but that doesn't help much.
In the past, I have always written an encryption routine that things such as Credit Card numbers were passed through on their way into and out of the database. .NET has an encryption class that makes that approach a lot easier, and more secure.
Sorry for being misleading there. I guess I'm the naked, following blindman around... ;-)|||..that makes me feel a little nervous... :rolleyes:|||yeah, it scares the heck out of me.|||err guys..or gals...there's always the icq or msn for those kind of thing i believe ;)
neway sometimes it's not perfectly true in the sense that most of the software developed might be for customers and usually customers will DEMAND for the rights to access to everything and also to restore it.
That's was the whole reason y I asked the question in the first place =)
Neway am thinking of the payroll system that is currrently under development stage... I'm sure you might be a bit interested to know the pay your superior's getting ...|||Sorry. Just a little crazy from the workload.
I do unserstand what you are trying to accomplish. If you use your front-end application (or middleware) to perform the encryption, that would solve your data visibility problem. If you ultimately find a way for SQL to do it for you, I would love to know about it.
Also, as for blindman's idea to password protect the backups, you could let the end-users control the backup password protection. You can even impliment code in your front end to perform the backup and restores.|||thanks to both of you.
will try that bit on the backup thingy at work tomorrow|||As far as sensitive data is concerned (such as salaries), tell your people that the same confidentiality rules apply to DBAs as to priests.
...except that the celibacy is implied rather than enforced...|||Hey guys I have been looking into this lately myself for a db that needs to be secured. I ran across this plugin, but I haven't actually tested it.
http://www.appsecinc.com/products/dbencrypt/mssql/
When I looked over the info on this product it does exactly what you are looking for. You select the users that should get access to the information, and you can set it to encrypt only a specific column.
Do you guys know of a good way to send data from one remote computer to another? I need to send credit info from an online server to the companies internal server in the most secure way possible. These two servers will have a vpn link and the online db will have an SSL cert attached to it as well. Any thoughts?
Thanks for you help...|||Thanks for the link, 6SC.
blindman|||We implemented this slightly differently:
- Put your payroll system on a separate server
- Install SQLLiteSpeed with Encryption
- Create scheduled jobs to run custom backup
- Create alerts on vital system counters
- Setup email notification
- Create notification job
- Remove Builtin Admins and Domain/Enterprise admins from sysadmin server role
- Ask your accounting boss to change SA password, because even you should not have routine access to this server
- Make sure your accounting boss shares SA password with your CIO.|||thanks rdjabarov
Probably I'll propose to have it in another database but without additional software such as sqllitespeed. Will try to implement the database backup. Probably dts can help in schedulling it since the maintainence plan doesn't have this password feature.|||You can use T-SQL fired by a SQL Server Agent job to perform your backups, and specify passwords. See the following link on MSDN (yes, I've read this one and use it quite a bit!):
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ba-bz_35ww.asp
Using T-SQL will allow you to be more dynamic in your code. Also, you won't be going out of process (running DTSRUN.exe) which can cause it's own problems with production reliability.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_93u6.asp|||It should be possible to set up a role for your DBA that allows them to do basic maintenance, such as backups and restores, but does not allow them to access tables or procedures. Can't say I've done this, though.
If your DBA logs in as sa or dbo, then there is no way to hide anything that you don't encrypt yourself. DBO is database god (small g), and SA is server God (big g).
blindman|||hmm... in that case anybody that gotten hold of any of my full backup files too can just restore it and definately he/she will have sa authority and be able to view everything....|||Where are you leaving your backups? Shouldn't they enjoy the same security (file system, locked cabinet for tapes, etc) as your live database files? It's like leaving photocopies of you credit card statements around.
Did you read the article on encryption? It does provide a method of protecting your data from direct viewing, but it needs to be set up that way initially.|||bpdWork,
I don't see where in the article it talks about protecting your data from viewing within the database, just from viewing intercepted transmissions across the network. Have you done this?
debcwong,
The BACKUP command allows to supply a password that is then required in order to restore the file, although the sqlmaint Utility (used by the Maintenance Plan wizard) does not.
blindman|||No, I haven't. In fact, you can probably tell just how lazy I am by the fact that I didn't read the whole article.
Though, the second sentence says: "Encryption ensures that data remains secure by keeping the information hidden from everyone, even if the encrypted data is viewed directly," I cannot find any way of actually doing this.
I am able to encrypt Stored rocedures and Views so that their definitions are encrypted, but that doesn't help much.
In the past, I have always written an encryption routine that things such as Credit Card numbers were passed through on their way into and out of the database. .NET has an encryption class that makes that approach a lot easier, and more secure.
Sorry for being misleading there. I guess I'm the naked, following blindman around... ;-)|||..that makes me feel a little nervous... :rolleyes:|||yeah, it scares the heck out of me.|||err guys..or gals...there's always the icq or msn for those kind of thing i believe ;)
neway sometimes it's not perfectly true in the sense that most of the software developed might be for customers and usually customers will DEMAND for the rights to access to everything and also to restore it.
That's was the whole reason y I asked the question in the first place =)
Neway am thinking of the payroll system that is currrently under development stage... I'm sure you might be a bit interested to know the pay your superior's getting ...|||Sorry. Just a little crazy from the workload.
I do unserstand what you are trying to accomplish. If you use your front-end application (or middleware) to perform the encryption, that would solve your data visibility problem. If you ultimately find a way for SQL to do it for you, I would love to know about it.
Also, as for blindman's idea to password protect the backups, you could let the end-users control the backup password protection. You can even impliment code in your front end to perform the backup and restores.|||thanks to both of you.
will try that bit on the backup thingy at work tomorrow|||As far as sensitive data is concerned (such as salaries), tell your people that the same confidentiality rules apply to DBAs as to priests.
...except that the celibacy is implied rather than enforced...|||Hey guys I have been looking into this lately myself for a db that needs to be secured. I ran across this plugin, but I haven't actually tested it.
http://www.appsecinc.com/products/dbencrypt/mssql/
When I looked over the info on this product it does exactly what you are looking for. You select the users that should get access to the information, and you can set it to encrypt only a specific column.
Do you guys know of a good way to send data from one remote computer to another? I need to send credit info from an online server to the companies internal server in the most secure way possible. These two servers will have a vpn link and the online db will have an SSL cert attached to it as well. Any thoughts?
Thanks for you help...|||Thanks for the link, 6SC.
blindman|||We implemented this slightly differently:
- Put your payroll system on a separate server
- Install SQLLiteSpeed with Encryption
- Create scheduled jobs to run custom backup
- Create alerts on vital system counters
- Setup email notification
- Create notification job
- Remove Builtin Admins and Domain/Enterprise admins from sysadmin server role
- Ask your accounting boss to change SA password, because even you should not have routine access to this server
- Make sure your accounting boss shares SA password with your CIO.|||thanks rdjabarov
Probably I'll propose to have it in another database but without additional software such as sqllitespeed. Will try to implement the database backup. Probably dts can help in schedulling it since the maintainence plan doesn't have this password feature.|||You can use T-SQL fired by a SQL Server Agent job to perform your backups, and specify passwords. See the following link on MSDN (yes, I've read this one and use it quite a bit!):
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ba-bz_35ww.asp
Using T-SQL will allow you to be more dynamic in your code. Also, you won't be going out of process (running DTSRUN.exe) which can cause it's own problems with production reliability.
Database security
Hi,
I'm trying to implement some security on our more sensitive tables in a database.
The database is used by all for read/write via Web pages (IIS).
Is there any way to restrict users from accessing a table other than from a specific application (i.e. IIS or Crystal Reports)?
Am I looking in the wrong direction?
Thanks
MottyYes, you can do that by implementing application security(application role).
For more details see "application roles" in BOL.
Originally posted by mseal1
Hi,
I'm trying to implement some security on our more sensitive tables in a database.
The database is used by all for read/write via Web pages (IIS).
Is there any way to restrict users from accessing a table other than from a specific application (i.e. IIS or Crystal Reports)?
Am I looking in the wrong direction?
Thanks
Motty|||How is the access to the tables controlled?Thru Stored procedure ,roles?|||I have no control at this time as to how users access the Db.
Security is using NT logons, and domain users can read/write to all tables.
(Hope I don't sound too naive about administrating my database (SQL 7.0)
Thanks
Motty|||What if I have no control over the application that accesses SQL, then I can't run the sp_setapprole to gain access?|||Once the app role in place, you won`t need to keep NT logons , so this it would be the only way to connect to the database for the users. (supposing of course that guest acc. don`t exists in the current DB)
Originally posted by mseal1
What if I have no control over the application that accesses SQL, then I can't run the sp_setapprole to gain access?|||I know I'm sounding a little thick today
I have several applications (off the shelf) such as Crystal reporting, Access, Excel
I want to be able to limit access to a table based on the application name the users are coming from.
If I use Profiler, I have a column called 'Application Name' that identifies the type of application.
Can I use that information? At times I don't have a way to 'send' the sp_setapprole command.
Thanks for all your help!|||No you don't because SQL implements the security based on accounts and roles. The only way to restrict the access is to declare a custom role in your DB for each app., then set the privileges according to your policy, and map your users to these roles.
Originally posted by mseal1
I know I'm sounding a little thick today
I have several applications (off the shelf) such as Crystal reporting, Access, Excel
I want to be able to limit access to a table based on the application name the users are coming from.
If I use Profiler, I have a column called 'Application Name' that identifies the type of application.
Can I use that information? At times I don't have a way to 'send' the sp_setapprole command.
Thanks for all your help!|||Thanks,
I think I have enough to start
I'm trying to implement some security on our more sensitive tables in a database.
The database is used by all for read/write via Web pages (IIS).
Is there any way to restrict users from accessing a table other than from a specific application (i.e. IIS or Crystal Reports)?
Am I looking in the wrong direction?
Thanks
MottyYes, you can do that by implementing application security(application role).
For more details see "application roles" in BOL.
Originally posted by mseal1
Hi,
I'm trying to implement some security on our more sensitive tables in a database.
The database is used by all for read/write via Web pages (IIS).
Is there any way to restrict users from accessing a table other than from a specific application (i.e. IIS or Crystal Reports)?
Am I looking in the wrong direction?
Thanks
Motty|||How is the access to the tables controlled?Thru Stored procedure ,roles?|||I have no control at this time as to how users access the Db.
Security is using NT logons, and domain users can read/write to all tables.
(Hope I don't sound too naive about administrating my database (SQL 7.0)
Thanks
Motty|||What if I have no control over the application that accesses SQL, then I can't run the sp_setapprole to gain access?|||Once the app role in place, you won`t need to keep NT logons , so this it would be the only way to connect to the database for the users. (supposing of course that guest acc. don`t exists in the current DB)
Originally posted by mseal1
What if I have no control over the application that accesses SQL, then I can't run the sp_setapprole to gain access?|||I know I'm sounding a little thick today
I have several applications (off the shelf) such as Crystal reporting, Access, Excel
I want to be able to limit access to a table based on the application name the users are coming from.
If I use Profiler, I have a column called 'Application Name' that identifies the type of application.
Can I use that information? At times I don't have a way to 'send' the sp_setapprole command.
Thanks for all your help!|||No you don't because SQL implements the security based on accounts and roles. The only way to restrict the access is to declare a custom role in your DB for each app., then set the privileges according to your policy, and map your users to these roles.
Originally posted by mseal1
I know I'm sounding a little thick today
I have several applications (off the shelf) such as Crystal reporting, Access, Excel
I want to be able to limit access to a table based on the application name the users are coming from.
If I use Profiler, I have a column called 'Application Name' that identifies the type of application.
Can I use that information? At times I don't have a way to 'send' the sp_setapprole command.
Thanks for all your help!|||Thanks,
I think I have enough to start
database security
i a the user of sql server 2005 on window server 2003. i want to know
that how can i prevent my database from other user logins because all
of them are sysadmin type.
and i am also looking for database concurrency control methods.
Hi,
You can use SQL Server authentification, open MS SQL manager then
change the connection of the current server
Omar Abid|||Mandy (mahendersinghbe@.gmail.com) writes:
In that case you would need to move the database to a different
instance where the other people can't get in. No protection from other
sysadmin on a aserver.
that how can i prevent my database from other user logins because all
of them are sysadmin type.
and i am also looking for database concurrency control methods.
if any one know about this plz mail me answer on this mail id
mahendersingh_be@.yahoo.co.in
thanx in advanceOn 11 juin, 13:00, Mandy <mahendersing...@.gmail.comwrote:
Quote:
Originally Posted by
i a the user of sql server 2005 on window server 2003. i want to know
that how can i prevent my database from other user logins because all
of them are sysadmin type.
and i am also looking for database concurrency control methods.
>
if any one know about this plz mail me answer on this mail id
mahendersingh...@.yahoo.co.in
>
thanx in advance
Hi,
You can use SQL Server authentification, open MS SQL manager then
change the connection of the current server
Omar Abid|||Mandy (mahendersinghbe@.gmail.com) writes:
Quote:
Originally Posted by
i a the user of sql server 2005 on window server 2003. i want to know
that how can i prevent my database from other user logins because all
of them are sysadmin type.
In that case you would need to move the database to a different
instance where the other people can't get in. No protection from other
sysadmin on a aserver.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Database security
hi thr,
May I know how to create a secure database? I heard some of my seniors said about something that outsider unable to open and view our database table unless the user is the admin itself...instead of setting password to our database, is there any other way to avoid our database to be viewed from other people?
p/s: Any databases but I prefer SQL Server 2000 for this question...thx!!Nobody can access database unless you grant them rights.
If you are sysadmin revoke all rights and privileges like security administrator or system administrator from all users and create, for example, read_only group (role) in each database. You can grant some rights to this group like select from some tables of views, execute from some reporting stored procedures. Any new user should be a member of this group. And you will not have to worry granting rights separately to each user.
If you have user_id and password that everyone knows. You just change a password and nobody will be able to login without your knowledge or permission.
To test my words:
1.create some login_id make it a member of read_only group.
2.login with this new UID and see what youll see or be able to do.
Hope it helps.
There is much more to it. But people write books on Server Security and it is not a place for it.. :)
Sunday, March 11, 2012
Database Role Security Permissions
Hi,
How can we determine permissions of a database role?
I could only find out how to determine user and login perms:
EXECUTE AS user = 'Omid' GO SELECT has_perms_by_name(db_name(), 'DATABASE', 'ANY') GO REVERT GOAny suggestions?I can't believe there is no reply after a day in MSDN forums. Anyway not to disappoint folks with the same problem, there is a very very stupid solution: Create a temp user(login) and add this user to the database role and then check the permissions and at last remove the user!|||Hey, sometimes we all have to take a break
Are you using 2005? If so, you can use the sys.database_permissions view. Here is a blog that I forgot that I wrote about this until I started doing some research for you
http://drsql.spaces.live.com/blog/cns!80677FB08B3162E4!1485.entry
This query gets table permissions in a database, with object names... Easy enough to expand for other types of objects:
select database_permissions.permission_name,
coalesce(objects.type_desc,database_permissions.class_desc)
+ case when objects.type_desc is not null and minor_id > 0 then '-COLUMN'
else '' end as object_type,
case database_permissions.class_desc
when 'SCHEMA' then schema_name(major_id)
when 'OBJECT_OR_COLUMN' then
case when minor_id = 0 then object_name(major_id)
else (select object_name(object_id) + '.'+ name
from sys.columns
where object_id = database_permissions.major_id
and column_id = database_permissions.minor_id) end
else 'other' end as object_name,
database_principals.name as database_principal,
database_permissions.state_desc as grant_state
from sys.database_permissions
join sys.database_principals
on database_permissions.grantee_principal_id = database_principals.principal_id
left join sys.objects --left because it is possible that it is a schema
on objects.object_id = database_permissions.major_id
where database_permissions.major_id > 0
and permission_name in ('SELECT','INSERT','UPDATE','DELETE')
order by object_name
I really appreciate it. Although I've already implemented the "stupid" solution, I'll surely change it to use the query as soon as possible.
Thanks again.
Thursday, March 8, 2012
Database Restore Security Issue
We are doing a restore of a SQL database to a stand alone test server. We
were able to restore the database successfully to SQL Server 2000 sp3a. We
restored the data into a database that has the same name as the production
database. We installed the application that will be accessing the database
also without issue.
The problem we are running into is with the security. When the application
tries to access the data it says the user does not have permission. It
appears that the restore pulled in the security data from the production
server and when I try to add a new user (from the test server) under security
and give them access to the database I get an error saying "Errir 15401:
Windows NT useror roup 'Test\Administrator' not found. Check the name
again." Even if I go into the database itsel and go to users and try to add
one there. Same error message. How do I allow users from the test
environment to access the database?
Thanks for your help.
You need to link the existing user in the restored database to the login on
the server. Look up sp_change_users_login in BOL for details.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Ken Ostrowski" <KenOstrowski@.discussions.microsoft.com> wrote in message
news:BDBC45C6-1186-4D41-8057-9EAC21C42DE7@.microsoft.com...
> We are doing a restore of a SQL database to a stand alone test server. We
> were able to restore the database successfully to SQL Server 2000 sp3a.
> We
> restored the data into a database that has the same name as the production
> database. We installed the application that will be accessing the
> database
> also without issue.
> The problem we are running into is with the security. When the
> application
> tries to access the data it says the user does not have permission. It
> appears that the restore pulled in the security data from the production
> server and when I try to add a new user (from the test server) under
> security
> and give them access to the database I get an error saying "Errir 15401:
> Windows NT useror roup 'Test\Administrator' not found. Check the name
> again." Even if I go into the database itsel and go to users and try to
> add
> one there. Same error message. How do I allow users from the test
> environment to access the database?
> Thanks for your help.
were able to restore the database successfully to SQL Server 2000 sp3a. We
restored the data into a database that has the same name as the production
database. We installed the application that will be accessing the database
also without issue.
The problem we are running into is with the security. When the application
tries to access the data it says the user does not have permission. It
appears that the restore pulled in the security data from the production
server and when I try to add a new user (from the test server) under security
and give them access to the database I get an error saying "Errir 15401:
Windows NT useror roup 'Test\Administrator' not found. Check the name
again." Even if I go into the database itsel and go to users and try to add
one there. Same error message. How do I allow users from the test
environment to access the database?
Thanks for your help.
You need to link the existing user in the restored database to the login on
the server. Look up sp_change_users_login in BOL for details.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Ken Ostrowski" <KenOstrowski@.discussions.microsoft.com> wrote in message
news:BDBC45C6-1186-4D41-8057-9EAC21C42DE7@.microsoft.com...
> We are doing a restore of a SQL database to a stand alone test server. We
> were able to restore the database successfully to SQL Server 2000 sp3a.
> We
> restored the data into a database that has the same name as the production
> database. We installed the application that will be accessing the
> database
> also without issue.
> The problem we are running into is with the security. When the
> application
> tries to access the data it says the user does not have permission. It
> appears that the restore pulled in the security data from the production
> server and when I try to add a new user (from the test server) under
> security
> and give them access to the database I get an error saying "Errir 15401:
> Windows NT useror roup 'Test\Administrator' not found. Check the name
> again." Even if I go into the database itsel and go to users and try to
> add
> one there. Same error message. How do I allow users from the test
> environment to access the database?
> Thanks for your help.
Database Restore Security Issue
We are doing a restore of a SQL database to a stand alone test server. We
were able to restore the database successfully to SQL Server 2000 sp3a. We
restored the data into a database that has the same name as the production
database. We installed the application that will be accessing the database
also without issue.
The problem we are running into is with the security. When the application
tries to access the data it says the user does not have permission. It
appears that the restore pulled in the security data from the production
server and when I try to add a new user (from the test server) under securit
y
and give them access to the database I get an error saying "Errir 15401:
Windows NT useror roup 'Test\Administrator' not found. Check the name
again." Even if I go into the database itsel and go to users and try to add
one there. Same error message. How do I allow users from the test
environment to access the database?
Thanks for your help.You need to link the existing user in the restored database to the login on
the server. Look up sp_change_users_login in BOL for details.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Ken Ostrowski" <KenOstrowski@.discussions.microsoft.com> wrote in message
news:BDBC45C6-1186-4D41-8057-9EAC21C42DE7@.microsoft.com...
> We are doing a restore of a SQL database to a stand alone test server. We
> were able to restore the database successfully to SQL Server 2000 sp3a.
> We
> restored the data into a database that has the same name as the production
> database. We installed the application that will be accessing the
> database
> also without issue.
> The problem we are running into is with the security. When the
> application
> tries to access the data it says the user does not have permission. It
> appears that the restore pulled in the security data from the production
> server and when I try to add a new user (from the test server) under
> security
> and give them access to the database I get an error saying "Errir 15401:
> Windows NT useror roup 'Test\Administrator' not found. Check the name
> again." Even if I go into the database itsel and go to users and try to
> add
> one there. Same error message. How do I allow users from the test
> environment to access the database?
> Thanks for your help.
were able to restore the database successfully to SQL Server 2000 sp3a. We
restored the data into a database that has the same name as the production
database. We installed the application that will be accessing the database
also without issue.
The problem we are running into is with the security. When the application
tries to access the data it says the user does not have permission. It
appears that the restore pulled in the security data from the production
server and when I try to add a new user (from the test server) under securit
y
and give them access to the database I get an error saying "Errir 15401:
Windows NT useror roup 'Test\Administrator' not found. Check the name
again." Even if I go into the database itsel and go to users and try to add
one there. Same error message. How do I allow users from the test
environment to access the database?
Thanks for your help.You need to link the existing user in the restored database to the login on
the server. Look up sp_change_users_login in BOL for details.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Ken Ostrowski" <KenOstrowski@.discussions.microsoft.com> wrote in message
news:BDBC45C6-1186-4D41-8057-9EAC21C42DE7@.microsoft.com...
> We are doing a restore of a SQL database to a stand alone test server. We
> were able to restore the database successfully to SQL Server 2000 sp3a.
> We
> restored the data into a database that has the same name as the production
> database. We installed the application that will be accessing the
> database
> also without issue.
> The problem we are running into is with the security. When the
> application
> tries to access the data it says the user does not have permission. It
> appears that the restore pulled in the security data from the production
> server and when I try to add a new user (from the test server) under
> security
> and give them access to the database I get an error saying "Errir 15401:
> Windows NT useror roup 'Test\Administrator' not found. Check the name
> again." Even if I go into the database itsel and go to users and try to
> add
> one there. Same error message. How do I allow users from the test
> environment to access the database?
> Thanks for your help.
Database Restore Security Issue
We are doing a restore of a SQL database to a stand alone test server. We
were able to restore the database successfully to SQL Server 2000 sp3a. We
restored the data into a database that has the same name as the production
database. We installed the application that will be accessing the database
also without issue.
The problem we are running into is with the security. When the application
tries to access the data it says the user does not have permission. It
appears that the restore pulled in the security data from the production
server and when I try to add a new user (from the test server) under security
and give them access to the database I get an error saying "Errir 15401:
Windows NT useror roup 'Test\Administrator' not found. Check the name
again." Even if I go into the database itsel and go to users and try to add
one there. Same error message. How do I allow users from the test
environment to access the database?
Thanks for your help.You need to link the existing user in the restored database to the login on
the server. Look up sp_change_users_login in BOL for details.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Ken Ostrowski" <KenOstrowski@.discussions.microsoft.com> wrote in message
news:BDBC45C6-1186-4D41-8057-9EAC21C42DE7@.microsoft.com...
> We are doing a restore of a SQL database to a stand alone test server. We
> were able to restore the database successfully to SQL Server 2000 sp3a.
> We
> restored the data into a database that has the same name as the production
> database. We installed the application that will be accessing the
> database
> also without issue.
> The problem we are running into is with the security. When the
> application
> tries to access the data it says the user does not have permission. It
> appears that the restore pulled in the security data from the production
> server and when I try to add a new user (from the test server) under
> security
> and give them access to the database I get an error saying "Errir 15401:
> Windows NT useror roup 'Test\Administrator' not found. Check the name
> again." Even if I go into the database itsel and go to users and try to
> add
> one there. Same error message. How do I allow users from the test
> environment to access the database?
> Thanks for your help.
were able to restore the database successfully to SQL Server 2000 sp3a. We
restored the data into a database that has the same name as the production
database. We installed the application that will be accessing the database
also without issue.
The problem we are running into is with the security. When the application
tries to access the data it says the user does not have permission. It
appears that the restore pulled in the security data from the production
server and when I try to add a new user (from the test server) under security
and give them access to the database I get an error saying "Errir 15401:
Windows NT useror roup 'Test\Administrator' not found. Check the name
again." Even if I go into the database itsel and go to users and try to add
one there. Same error message. How do I allow users from the test
environment to access the database?
Thanks for your help.You need to link the existing user in the restored database to the login on
the server. Look up sp_change_users_login in BOL for details.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Ken Ostrowski" <KenOstrowski@.discussions.microsoft.com> wrote in message
news:BDBC45C6-1186-4D41-8057-9EAC21C42DE7@.microsoft.com...
> We are doing a restore of a SQL database to a stand alone test server. We
> were able to restore the database successfully to SQL Server 2000 sp3a.
> We
> restored the data into a database that has the same name as the production
> database. We installed the application that will be accessing the
> database
> also without issue.
> The problem we are running into is with the security. When the
> application
> tries to access the data it says the user does not have permission. It
> appears that the restore pulled in the security data from the production
> server and when I try to add a new user (from the test server) under
> security
> and give them access to the database I get an error saying "Errir 15401:
> Windows NT useror roup 'Test\Administrator' not found. Check the name
> again." Even if I go into the database itsel and go to users and try to
> add
> one there. Same error message. How do I allow users from the test
> environment to access the database?
> Thanks for your help.
Sunday, February 19, 2012
database protection?
I know SQL Server has a good security system for the enterprise manager.
But are SQL server 2005 databases password protected?
In other words, suppose I make a database, named DATA1, with all its tables
and data on SQL Server 2005 I.
Can any one who download SQL Server Express 2005 open DATA1 on such a server
?
Are databases password protected like MS Access databases?
Thank you.newbie in hell (newbieinhell@.discussions.microsoft.com) writes:
> I know SQL Server has a good security system for the enterprise manager.
> But are SQL server 2005 databases password protected?
> In other words, suppose I make a database, named DATA1, with all its
> tables and data on SQL Server 2005 I.
> Can any one who download SQL Server Express 2005 open DATA1 on such a
> server?
> Are databases password protected like MS Access databases?
No. If you have been able to get hold of database file for SQL Server,
you can attach it to a server do whatever you like with it. What you can
do is to use encryption, and protect the encryption keys with the service
master key. In that case, it's difficult to get hold of everything, if
you attach it a different server.
I don't know Access, but from what I've heard passwords for Access databases
are not much of a protection either. It stops the stray wanderer, but
anyone who is decided to get in, will do so.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
But are SQL server 2005 databases password protected?
In other words, suppose I make a database, named DATA1, with all its tables
and data on SQL Server 2005 I.
Can any one who download SQL Server Express 2005 open DATA1 on such a server
?
Are databases password protected like MS Access databases?
Thank you.newbie in hell (newbieinhell@.discussions.microsoft.com) writes:
> I know SQL Server has a good security system for the enterprise manager.
> But are SQL server 2005 databases password protected?
> In other words, suppose I make a database, named DATA1, with all its
> tables and data on SQL Server 2005 I.
> Can any one who download SQL Server Express 2005 open DATA1 on such a
> server?
> Are databases password protected like MS Access databases?
No. If you have been able to get hold of database file for SQL Server,
you can attach it to a server do whatever you like with it. What you can
do is to use encryption, and protect the encryption keys with the service
master key. In that case, it's difficult to get hold of everything, if
you attach it a different server.
I don't know Access, but from what I've heard passwords for Access databases
are not much of a protection either. It stops the stray wanderer, but
anyone who is decided to get in, will do so.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Friday, February 17, 2012
Database Permissions
Hello All,
I have a need to set up database security on our QA and Production servers
in the following manner:
IT Managers - Read/write access. Ability to view/start/stop scheduled jobs
not owned by them (all jobs are owned by sa).
Team Leads - Allow them to create/drop/alter stored procedures and functions
only. Otherwise, read-only access to all other objects
Developers - Read-only access to all objects.
For the IT Managers, I have a couple of options. 1) Give dbo permissions,
which will give them everything but the ability to view/start/stop jobs. I
won't give them sysadmin rights.
For the Developers, it's pretty easy. db_datareader permissions,
db_denydatawriter permissions.
For the Team Leads, I have not come up with anything bullet-proof. If I
give db_ddladmin rights, it allows them to modify data regardless of any
explicit deny permissions I put on any objects.
Does anyone have any suggestions?
Thanks,
David Grau
Database Administrator
Surprise & DelightDavid
1. Create ITManagers Group and add it to sysadmin server role.
2. Create TeamLead Group
a) Don't make it a member of sysadmin server role
b) GRANT CREATE TABLE ,CREATE Function ,GRANT CREATE Procedure TO
TeamLead
Take a look at creating Roles in the BOL as well
"David Grau" <DavidGrau@.discussions.microsoft.com> wrote in message
news:D265D1AC-C3D5-408B-8C77-B91696442F9F@.microsoft.com...
> Hello All,
> I have a need to set up database security on our QA and Production servers
> in the following manner:
> IT Managers - Read/write access. Ability to view/start/stop scheduled
> jobs
> not owned by them (all jobs are owned by sa).
> Team Leads - Allow them to create/drop/alter stored procedures and
> functions
> only. Otherwise, read-only access to all other objects
> Developers - Read-only access to all objects.
> For the IT Managers, I have a couple of options. 1) Give dbo permissions,
> which will give them everything but the ability to view/start/stop jobs.
> I
> won't give them sysadmin rights.
> For the Developers, it's pretty easy. db_datareader permissions,
> db_denydatawriter permissions.
> For the Team Leads, I have not come up with anything bullet-proof. If I
> give db_ddladmin rights, it allows them to modify data regardless of any
> explicit deny permissions I put on any objects.
> Does anyone have any suggestions?
> Thanks,
> David Grau
> Database Administrator
> --
> Surprise & Delight|||Thanks for your reply. However, let me add more detail now that I know more
about this request.
The IT Managers want to have SQL Logins that have expanded security beyond
their Windows logins. Is there a way to give them read/write to each
database as well as the ability to start/stop/delete scheduled jobs?
Similarly, Team Leaders want separate SQL Logins that they can use that have
the following: read-only access to the databases; create/drop/alter stored
procedures and functions. No other abilities for the Team Leaders. They
should not be able to create/alter/drop tables or any other objects.
Can all this be accomplished through database roles?
Thanks,
David Grau
--
Surprise & Delight
"Uri Dimant" wrote:
> David
> 1. Create ITManagers Group and add it to sysadmin server role.
> 2. Create TeamLead Group
> a) Don't make it a member of sysadmin server role
> b) GRANT CREATE TABLE ,CREATE Function ,GRANT CREATE Procedure TO
> TeamLead
>
> Take a look at creating Roles in the BOL as well
>
> "David Grau" <DavidGrau@.discussions.microsoft.com> wrote in message
> news:D265D1AC-C3D5-408B-8C77-B91696442F9F@.microsoft.com...
>
>|||David
> The IT Managers want to have SQL Logins that have expanded security beyond
> their Windows logins. Is there a way to give them read/write to each
> database as well as the ability to start/stop/delete scheduled jobs?
Add them to sysadmin server role
"David Grau" <DavidGrau@.discussions.microsoft.com> wrote in message
news:3079CDD8-49BC-4466-9FC2-2CED09181265@.microsoft.com...[vbcol=seagreen]
> Thanks for your reply. However, let me add more detail now that I know
> more
> about this request.
> The IT Managers want to have SQL Logins that have expanded security beyond
> their Windows logins. Is there a way to give them read/write to each
> database as well as the ability to start/stop/delete scheduled jobs?
> Similarly, Team Leaders want separate SQL Logins that they can use that
> have
> the following: read-only access to the databases; create/drop/alter
> stored
> procedures and functions. No other abilities for the Team Leaders. They
> should not be able to create/alter/drop tables or any other objects.
> Can all this be accomplished through database roles?
> Thanks,
> David Grau
> --
> Surprise & Delight
>
> "Uri Dimant" wrote:
>
I have a need to set up database security on our QA and Production servers
in the following manner:
IT Managers - Read/write access. Ability to view/start/stop scheduled jobs
not owned by them (all jobs are owned by sa).
Team Leads - Allow them to create/drop/alter stored procedures and functions
only. Otherwise, read-only access to all other objects
Developers - Read-only access to all objects.
For the IT Managers, I have a couple of options. 1) Give dbo permissions,
which will give them everything but the ability to view/start/stop jobs. I
won't give them sysadmin rights.
For the Developers, it's pretty easy. db_datareader permissions,
db_denydatawriter permissions.
For the Team Leads, I have not come up with anything bullet-proof. If I
give db_ddladmin rights, it allows them to modify data regardless of any
explicit deny permissions I put on any objects.
Does anyone have any suggestions?
Thanks,
David Grau
Database Administrator
Surprise & DelightDavid
1. Create ITManagers Group and add it to sysadmin server role.
2. Create TeamLead Group
a) Don't make it a member of sysadmin server role
b) GRANT CREATE TABLE ,CREATE Function ,GRANT CREATE Procedure TO
TeamLead
Take a look at creating Roles in the BOL as well
"David Grau" <DavidGrau@.discussions.microsoft.com> wrote in message
news:D265D1AC-C3D5-408B-8C77-B91696442F9F@.microsoft.com...
> Hello All,
> I have a need to set up database security on our QA and Production servers
> in the following manner:
> IT Managers - Read/write access. Ability to view/start/stop scheduled
> jobs
> not owned by them (all jobs are owned by sa).
> Team Leads - Allow them to create/drop/alter stored procedures and
> functions
> only. Otherwise, read-only access to all other objects
> Developers - Read-only access to all objects.
> For the IT Managers, I have a couple of options. 1) Give dbo permissions,
> which will give them everything but the ability to view/start/stop jobs.
> I
> won't give them sysadmin rights.
> For the Developers, it's pretty easy. db_datareader permissions,
> db_denydatawriter permissions.
> For the Team Leads, I have not come up with anything bullet-proof. If I
> give db_ddladmin rights, it allows them to modify data regardless of any
> explicit deny permissions I put on any objects.
> Does anyone have any suggestions?
> Thanks,
> David Grau
> Database Administrator
> --
> Surprise & Delight|||Thanks for your reply. However, let me add more detail now that I know more
about this request.
The IT Managers want to have SQL Logins that have expanded security beyond
their Windows logins. Is there a way to give them read/write to each
database as well as the ability to start/stop/delete scheduled jobs?
Similarly, Team Leaders want separate SQL Logins that they can use that have
the following: read-only access to the databases; create/drop/alter stored
procedures and functions. No other abilities for the Team Leaders. They
should not be able to create/alter/drop tables or any other objects.
Can all this be accomplished through database roles?
Thanks,
David Grau
--
Surprise & Delight
"Uri Dimant" wrote:
> David
> 1. Create ITManagers Group and add it to sysadmin server role.
> 2. Create TeamLead Group
> a) Don't make it a member of sysadmin server role
> b) GRANT CREATE TABLE ,CREATE Function ,GRANT CREATE Procedure TO
> TeamLead
>
> Take a look at creating Roles in the BOL as well
>
> "David Grau" <DavidGrau@.discussions.microsoft.com> wrote in message
> news:D265D1AC-C3D5-408B-8C77-B91696442F9F@.microsoft.com...
>
>|||David
> The IT Managers want to have SQL Logins that have expanded security beyond
> their Windows logins. Is there a way to give them read/write to each
> database as well as the ability to start/stop/delete scheduled jobs?
Add them to sysadmin server role
"David Grau" <DavidGrau@.discussions.microsoft.com> wrote in message
news:3079CDD8-49BC-4466-9FC2-2CED09181265@.microsoft.com...[vbcol=seagreen]
> Thanks for your reply. However, let me add more detail now that I know
> more
> about this request.
> The IT Managers want to have SQL Logins that have expanded security beyond
> their Windows logins. Is there a way to give them read/write to each
> database as well as the ability to start/stop/delete scheduled jobs?
> Similarly, Team Leaders want separate SQL Logins that they can use that
> have
> the following: read-only access to the databases; create/drop/alter
> stored
> procedures and functions. No other abilities for the Team Leaders. They
> should not be able to create/alter/drop tables or any other objects.
> Can all this be accomplished through database roles?
> Thanks,
> David Grau
> --
> Surprise & Delight
>
> "Uri Dimant" wrote:
>
Subscribe to:
Posts (Atom)