putting user accounts on the database

hii all;

My question is from 2 sections:

1- Is it secure to put user accounts (userName and password ...) on the database?

2- How can I set username and password for SQL server 2005 express file?

Thanks

Hi Abdul-Rahman,

"1- Is it secure to put user accounts (userName and password ...) on the database?"

Actually, the fact that you have concerns about storing the passwords in the database show that it isn′t secure :-) In my past project with SQL Server 2000, I always used a encryption function in the middle tier which encrypted the password, send the encrypted text to SQL Server which did the comparison of the stored and send encrypted password. So the logic and the security layer was on the middle tier. In SQL Server 2005 there are more security enhancements like enryption of data:

http://www.databasejournal.com/features/mssql/article.php/3483931

AFAIK, there is no password on the database file. If you have access to attach the database file you are free to attach it, but as somebody has another opinion or facts about that, I would be glad to hear that.

HTH; Jens Suessmeyer.

|||

(1) You can protect information in various ways. But to decide how secure your protection scheme is you should provide additional information: how do you use that information and against whom you are trying to protect it?

(2) What file are you referring to? Please provide additional information.

Thanks
Laurentiu

|||

Hi all;

First, my application is based on storing some data about customers, and I hope to make something to prevent anyone from opening or connecting to the database directly and watching or editing its data, but instead, specific users can enter an application that can deal with data in tables.

I think there's a way, because I see some where connection string that has something like: "... username=someUser ... password=pw..."

and I think this means the program enters the password to enter the DB.

"(2) What file are you referring to? Please provide additional information"

SQL2005 Express File, which ends with .mdf extension.

Thanks

|||

(1) SQL Server can do the authentication for you, which means you don't have to store passwords in a table and process them yourself. See Books Online, the topics on creating logins and users (CREATE LOGIN, CREATE USER). SQL Server 2005 stores SHA1 hashes of the SQL login passwords and the hashes are only visible to system administrators. If you use Windows authentication, no password is stored in the server. Books Online has more information on this:

http://msdn2.microsoft.com/en-us/library/ms130214.aspx

(2) There is no password for mdf files. If you store sensitive information in the database, you should look at the encryption features. To protect the mdf file, you should restrict access to it in Windows.

Thanks

Laurentiu

putting dbo explicitly in select staetement

I have witten a lot of stored procedures in my project where I did not put dbo before the user tables.My colleague told me that I have to put dbo for all statements other there could be a problem.

Any thought?,

Please assist.

Arkhan:

There are at least a couple of places in which the owner name prefix -- dbo -- is required including (1) naming of a scalar functions and (2) objects used with schemabinding. In addition, if your "shop standards" are to always designate object names with the owner name then you need to do so.

I can think of at least one situation in which I prefer that objects NOT be owned by dbo. This is at least somewhat controversial so take it with a grain of salt. In DTS "staging" tables I like to have a designated table owner so that that owner has the right to truncate the table without needing the database owner privilege. This does not figure to be relevant to your problem. It is good practice to include the owner name as part of your qualified name. One thing that worries about your question has to do with the practice of deployment of privileges.

I do not like it when I see scores of tables or other database objects that are owned by many different database users. When I see this type of stuff my knee-jerk reaction is that privileges are beging deployed to liberally. And I would guess that if you and your colleagues are seeing many problems from NOT including the owner name that you likely have this privilege problem.

Short answer: Include the dbo portion of the name.

Dave

|||

I've heard it said that not including the schema owner (usually DBO) on your object prefix can result in a "Compile lock" against your stored procedure while the client determines whether there is an object in existence for its own schema. In this situation, multiple users executing the same stored procedure would suffer from a queuing effect as each would place a compile lock on the procedure (or statement).

That being said, I've never been able to repro this is a testing environment and I have yet to see any white papers or KB articles that discuss this so I'd love it if somebody could chime in on this.

|||

Specifying the owner can help the system find the stored procedure faster. It also prevents issues if someone creates the same name procedure with a different owner (or schema in 2005) by mistake. At least if everyone uses two-part names, they will be less likely to make mistakes.

As for the compile locks, I have reproduced recompile locks in my stored procedures. I'm not 100% how I did it, but it seemed to be with temporary tables stored procedures. I added dbo in the front of each stored procedure and table name within the stored procedure and it elliminated most of the problems we had.

sql

Putting a DB into Single User Mode...

How do I do this when that option is grayed out? Also,
how does the single vs. the other mode impact persons
using the db?
Thanks in advancePatrick,
You can also use the command:
ALTER DATABASE databasename SINGLE_USER
Why is the option grayed out? Do you have sufficient rights to issue the
command on this database? You must be the db_owner or in the system roles
of sysadmin or dbcreator.
Russell Fields
"Patrick Ross" <yourmcse@.msn.com> wrote in message
news:1274901c3f62b$c9afe3c0$a601280a@.phx.gbl...
> How do I do this when that option is grayed out? Also,
> how does the single vs. the other mode impact persons
> using the db?
> Thanks in advancesql

Putting a DB into Single User Mode...

How do I do this when that option is grayed out? Also,
how does the single vs. the other mode impact persons
using the db?
Thanks in advancePatrick,
You can also use the command:
ALTER DATABASE databasename SINGLE_USER
Why is the option grayed out? Do you have sufficient rights to issue the
command on this database? You must be the db_owner or in the system roles
of sysadmin or dbcreator.
Russell Fields
"Patrick Ross" <yourmcse@.msn.com> wrote in message
news:1274901c3f62b$c9afe3c0$a601280a@.phx
.gbl...
> How do I do this when that option is grayed out? Also,
> how does the single vs. the other mode impact persons
> using the db?
> Thanks in advance

Pulling info from the "current row" of one dataset into another

This is day 1 of me ever using RS so forgive ignorance of the topic.

Here's the basic scenario:

Input parameter from the user is an Account name.

For that account name I want to list Addresses of locations under the account, and then for each of those, I want to list received shipments from the address.

So Ideally the result is:

Account Name

Address 1

Shipment 1
Shipment 2
Shipment 3

Address 2

Shipment 1
Shipment 2

Address 3

Shipment 1

So, I am able to list the addresses just fine, but the next step is getting the stuff from the shipments table based on the currently running address? I'm probably saying that poorly. What I need to know is how do I structure the dataset/report to accomplish this. I suppose I could combine the query for the shipments with the query for the addresses, but that seems suboptimal to me.

TIA for the help.

Hello Scott,

If it is a one query i.e. one Dataset which gets al the AccountName, Address and Shipment then I would follow this.

1.) On the Toolbox, drag and drop the Table which is grid kinda structure.

2.) RightClick on the Table and add a Group and name it grpAccountName and from the dataset select the AccountName field in the field selection.

3.) On the first column of the grpAccountName drag and drop the AccountName field.

4.) Again RightClick on the Table and add a Group and name it grpAddress and from the dataset select the Address field in the field selection.

5.) On the First column of the grpAddress drag and drop the Address field from the dataset.

4.) Again RightClick on the Table and add a Group and name it grpShipment and from the dataset select the Shipment field in the field selection.

5.) On the Second column of the grpShipment drag and drop the Shipment field from the dataset

I think it should work. I have not tested it but I think this should solve your problem.

All the best.

Hope this helps...

--Deepak

pull-down menu based on user id

Hello,
When I lunch my report I want to display a pull-down menu
based on the user id. I know how to get userid but I do
not know how I can use it to display pull-down menu, for
some user id I should not display anything. Also if I
display the pull-down, after selection I should write
value selected to a table in SQL. How can I do that?
Thanks,
Jim.Throw the userid in the WHERE clause (assuming you are using a query
for the pull-down).
SELECT *
FROM tblPullDownMenu
WHERE userid = user.userid
"JIM.H." <anonymous@.discussions.microsoft.com> wrote in message news:<08d201c46e65$5bc843e0$a601280a@.phx.gbl>...
> Hello,
> When I lunch my report I want to display a pull-down menu
> based on the user id. I know how to get userid but I do
> not know how I can use it to display pull-down menu, for
> some user id I should not display anything. Also if I
> display the pull-down, after selection I should write
> value selected to a table in SQL. How can I do that?
> Thanks,
> Jim.|||Minor clarification: This would have to be a query expression:
="SELECT * FROM tblPullDownMenu WHERE userid = " & User!UserID
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Stoma_Kalos" <fzuma@.yahoo.com> wrote in message
news:e876e3c8.0407201152.7951b8c5@.posting.google.com...
> Throw the userid in the WHERE clause (assuming you are using a query
> for the pull-down).
> SELECT *
> FROM tblPullDownMenu
> WHERE userid = user.userid
>
> "JIM.H." <anonymous@.discussions.microsoft.com> wrote in message
news:<08d201c46e65$5bc843e0$a601280a@.phx.gbl>...
> > Hello,
> > When I lunch my report I want to display a pull-down menu
> > based on the user id. I know how to get userid but I do
> > not know how I can use it to display pull-down menu, for
> > some user id I should not display anything. Also if I
> > display the pull-down, after selection I should write
> > value selected to a table in SQL. How can I do that?
> > Thanks,
> > Jim.|||Thanks, Where should I use this SQL. Let's say, I am
running my report, at that time I need to check If this
string returns anything, if not display a pulldown menu,
accept user selection and update tblPullDownMenu.
How shoudl I do this? I am new at reporting.
>--Original Message--
>Minor clarification: This would have to be a query
expression:
>="SELECT * FROM tblPullDownMenu WHERE userid = " &
User!UserID
>--
>Ravi Mumulla (Microsoft)
>SQL Server Reporting Services
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>"Stoma_Kalos" <fzuma@.yahoo.com> wrote in message
>news:e876e3c8.0407201152.7951b8c5@.posting.google.com...
>> Throw the userid in the WHERE clause (assuming you are
using a query
>> for the pull-down).
>> SELECT *
>> FROM tblPullDownMenu
>> WHERE userid = user.userid
>>
>> "JIM.H." <anonymous@.discussions.microsoft.com> wrote in
message
>news:<08d201c46e65$5bc843e0$a601280a@.phx.gbl>...
>> > Hello,
>> > When I lunch my report I want to display a pull-down
menu
>> > based on the user id. I know how to get userid but I
do
>> > not know how I can use it to display pull-down menu,
for
>> > some user id I should not display anything. Also if I
>> > display the pull-down, after selection I should write
>> > value selected to a table in SQL. How can I do that?
>> > Thanks,
>> > Jim.
>
>.
>|||This should be typed into the Data pane of report designer.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"JIM.H." <anonymous@.discussions.microsoft.com> wrote in message
news:0da201c46e9a$31884690$a301280a@.phx.gbl...
> Thanks, Where should I use this SQL. Let's say, I am
> running my report, at that time I need to check If this
> string returns anything, if not display a pulldown menu,
> accept user selection and update tblPullDownMenu.
> How shoudl I do this? I am new at reporting.
>
> >--Original Message--
> >Minor clarification: This would have to be a query
> expression:
> >
> >="SELECT * FROM tblPullDownMenu WHERE userid = " &
> User!UserID
> >
> >--
> >Ravi Mumulla (Microsoft)
> >SQL Server Reporting Services
> >
> >This posting is provided "AS IS" with no warranties, and
> confers no rights.
> >"Stoma_Kalos" <fzuma@.yahoo.com> wrote in message
> >news:e876e3c8.0407201152.7951b8c5@.posting.google.com...
> >> Throw the userid in the WHERE clause (assuming you are
> using a query
> >> for the pull-down).
> >>
> >> SELECT *
> >> FROM tblPullDownMenu
> >> WHERE userid = user.userid
> >>
> >>
> >> "JIM.H." <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:<08d201c46e65$5bc843e0$a601280a@.phx.gbl>...
> >> > Hello,
> >> > When I lunch my report I want to display a pull-down
> menu
> >> > based on the user id. I know how to get userid but I
> do
> >> > not know how I can use it to display pull-down menu,
> for
> >> > some user id I should not display anything. Also if I
> >> > display the pull-down, after selection I should write
> >> > value selected to a table in SQL. How can I do that?
> >> > Thanks,
> >> > Jim.
> >
> >
> >.
> >|||Ravi, you know me, I need a little bit more help. LOL.
what should I do to make sure that the oulldown item
visaible and invisibale. I have more question below.
Thanks,
Jim.
>--Original Message--
>This should be typed into the Data pane of report
designer.
>--
>Ravi Mumulla (Microsoft)
>SQL Server Reporting Services
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>"JIM.H." <anonymous@.discussions.microsoft.com> wrote in
message
>news:0da201c46e9a$31884690$a301280a@.phx.gbl...
>> Thanks, Where should I use this SQL. Let's say, I am
>> running my report, at that time I need to check If this
>> string returns anything, if not display a pulldown menu,
>> accept user selection and update tblPullDownMenu.
>> How shoudl I do this? I am new at reporting.
>>
>> >--Original Message--
>> >Minor clarification: This would have to be a query
>> expression:
>> >
>> >="SELECT * FROM tblPullDownMenu WHERE userid = " &
>> User!UserID
>> >
>> >--
>> >Ravi Mumulla (Microsoft)
>> >SQL Server Reporting Services
>> >
>> >This posting is provided "AS IS" with no warranties,
and
>> confers no rights.
>> >"Stoma_Kalos" <fzuma@.yahoo.com> wrote in message
>> >news:e876e3c8.0407201152.7951b8c5@.posting.google.com...
>> >> Throw the userid in the WHERE clause (assuming you
are
>> using a query
>> >> for the pull-down).
>> >>
>> >> SELECT *
>> >> FROM tblPullDownMenu
>> >> WHERE userid = user.userid
>> >>
>> >>
>> >> "JIM.H." <anonymous@.discussions.microsoft.com> wrote
in
>> message
>> >news:<08d201c46e65$5bc843e0$a601280a@.phx.gbl>...
>> >> > Hello,
>> >> > When I lunch my report I want to display a pull-
down
>> menu
>> >> > based on the user id. I know how to get userid but
I
>> do
>> >> > not know how I can use it to display pull-down
menu,
>> for
>> >> > some user id I should not display anything. Also
if I
>> >> > display the pull-down, after selection I should
write
>> >> > value selected to a table in SQL. How can I do
that?
>> >> > Thanks,
>> >> > Jim.
>> >
>> >
>> >.
>> >
>
>.
>|||Could you please describe what you're trying to do in more detail? By
pulldown menu, do you mean the report parameters dropdown?
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"JIM.H." <anonymous@.discussions.microsoft.com> wrote in message
news:0d1901c46ea3$9d514490$a401280a@.phx.gbl...
> Ravi, you know me, I need a little bit more help. LOL.
> what should I do to make sure that the oulldown item
> visaible and invisibale. I have more question below.
> Thanks,
> Jim.
> >--Original Message--
> >This should be typed into the Data pane of report
> designer.
> >
> >--
> >Ravi Mumulla (Microsoft)
> >SQL Server Reporting Services
> >
> >This posting is provided "AS IS" with no warranties, and
> confers no rights.
> >"JIM.H." <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:0da201c46e9a$31884690$a301280a@.phx.gbl...
> >> Thanks, Where should I use this SQL. Let's say, I am
> >> running my report, at that time I need to check If this
> >> string returns anything, if not display a pulldown menu,
> >> accept user selection and update tblPullDownMenu.
> >> How shoudl I do this? I am new at reporting.
> >>
> >>
> >> >--Original Message--
> >> >Minor clarification: This would have to be a query
> >> expression:
> >> >
> >> >="SELECT * FROM tblPullDownMenu WHERE userid = " &
> >> User!UserID
> >> >
> >> >--
> >> >Ravi Mumulla (Microsoft)
> >> >SQL Server Reporting Services
> >> >
> >> >This posting is provided "AS IS" with no warranties,
> and
> >> confers no rights.
> >> >"Stoma_Kalos" <fzuma@.yahoo.com> wrote in message
> >> >news:e876e3c8.0407201152.7951b8c5@.posting.google.com...
> >> >> Throw the userid in the WHERE clause (assuming you
> are
> >> using a query
> >> >> for the pull-down).
> >> >>
> >> >> SELECT *
> >> >> FROM tblPullDownMenu
> >> >> WHERE userid = user.userid
> >> >>
> >> >>
> >> >> "JIM.H." <anonymous@.discussions.microsoft.com> wrote
> in
> >> message
> >> >news:<08d201c46e65$5bc843e0$a601280a@.phx.gbl>...
> >> >> > Hello,
> >> >> > When I lunch my report I want to display a pull-
> down
> >> menu
> >> >> > based on the user id. I know how to get userid but
> I
> >> do
> >> >> > not know how I can use it to display pull-down
> menu,
> >> for
> >> >> > some user id I should not display anything. Also
> if I
> >> >> > display the pull-down, after selection I should
> write
> >> >> > value selected to a table in SQL. How can I do
> that?
> >> >> > Thanks,
> >> >> > Jim.
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||Hello,
I have a table:UserArea that has UserId and Area fields.
When the users run the report I want to check if the user
is available in UserArea table, if yes, I will display
the report by taking this area into consideration, if not,
I have another table: Areas, I should display this areas
to the user as a pull-down menu so that he can select one
area, after selection I should update UserArea table and
display report based on area again. So I am building
UserArea table while people are running report to give
results based on the area they are in.
Is this possible?
Jim.
>--Original Message--
>Could you please describe what you're trying to do in
more detail? By
>pulldown menu, do you mean the report parameters dropdown?
>--
>Ravi Mumulla (Microsoft)
>SQL Server Reporting Services
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>"JIM.H." <anonymous@.discussions.microsoft.com> wrote in
message
>news:0d1901c46ea3$9d514490$a401280a@.phx.gbl...
>> Ravi, you know me, I need a little bit more help. LOL.
>> what should I do to make sure that the oulldown item
>> visaible and invisibale. I have more question below.
>> Thanks,
>> Jim.
>> >--Original Message--
>> >This should be typed into the Data pane of report
>> designer.
>> >
>> >--
>> >Ravi Mumulla (Microsoft)
>> >SQL Server Reporting Services
>> >
>> >This posting is provided "AS IS" with no warranties,
and
>> confers no rights.
>> >"JIM.H." <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:0da201c46e9a$31884690$a301280a@.phx.gbl...
>> >> Thanks, Where should I use this SQL. Let's say, I am
>> >> running my report, at that time I need to check If
this
>> >> string returns anything, if not display a pulldown
menu,
>> >> accept user selection and update tblPullDownMenu.
>> >> How shoudl I do this? I am new at reporting.
>> >>
>> >>
>> >> >--Original Message--
>> >> >Minor clarification: This would have to be a query
>> >> expression:
>> >> >
>> >> >="SELECT * FROM tblPullDownMenu WHERE userid = "
&
>> >> User!UserID
>> >> >
>> >> >--
>> >> >Ravi Mumulla (Microsoft)
>> >> >SQL Server Reporting Services
>> >> >
>> >> >This posting is provided "AS IS" with no warranties,
>> and
>> >> confers no rights.
>> >> >"Stoma_Kalos" <fzuma@.yahoo.com> wrote in message
>> >>
>news:e876e3c8.0407201152.7951b8c5@.posting.google.com...
>> >> >> Throw the userid in the WHERE clause (assuming you
>> are
>> >> using a query
>> >> >> for the pull-down).
>> >> >>
>> >> >> SELECT *
>> >> >> FROM tblPullDownMenu
>> >> >> WHERE userid = user.userid
>> >> >>
>> >> >>
>> >> >> "JIM.H." <anonymous@.discussions.microsoft.com>
wrote
>> in
>> >> message
>> >> >news:<08d201c46e65$5bc843e0$a601280a@.phx.gbl>...
>> >> >> > Hello,
>> >> >> > When I lunch my report I want to display a pull-
>> down
>> >> menu
>> >> >> > based on the user id. I know how to get userid
but
>> I
>> >> do
>> >> >> > not know how I can use it to display pull-down
>> menu,
>> >> for
>> >> >> > some user id I should not display anything. Also
>> if I
>> >> >> > display the pull-down, after selection I should
>> write
>> >> >> > value selected to a table in SQL. How can I do
>> that?
>> >> >> > Thanks,
>> >> >> > Jim.
>> >> >
>> >> >
>> >> >.
>> >> >
>> >
>> >
>> >.
>> >
>
>.
>|||Assuming you want to display data from table called Table_A, here's the
query I'd use in the data pane:
="select ta.* from Table_A ta, UserArea ua where ta.Area = ua.Area and
ua.UserID = " & User!UserID
On your second question, here's the approach I'd take:
1. Create a stored procedure that takes the userID as a parameter and checks
whether that userID is in the UserArea table. If is exists in that table,
don't return any rows. I it doesn't exist, return all Areas. Something like
follows:
drop proc sp_testuser
create proc sp_testuser (@.userIDParam varchar(32))
AS
BEGIN
select Area from testuser where not exists (select * from testuser where
userID = @.userIDParam)
END
2. Create a dataset based on the above stored procedure.
3. Create a query-based parameter list based on the dataset in previous step
(while in the Layout tab, go to Report menu item and choose Report
Parameters.)
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"JIM.H." <anonymous@.discussions.microsoft.com> wrote in message
news:172801c46f50$3bdc0bc0$a401280a@.phx.gbl...
> Hello,
> I have a table:UserArea that has UserId and Area fields.
> When the users run the report I want to check if the user
> is available in UserArea table, if yes, I will display
> the report by taking this area into consideration, if not,
> I have another table: Areas, I should display this areas
> to the user as a pull-down menu so that he can select one
> area, after selection I should update UserArea table and
> display report based on area again. So I am building
> UserArea table while people are running report to give
> results based on the area they are in.
> Is this possible?
> Jim.
> >--Original Message--
> >Could you please describe what you're trying to do in
> more detail? By
> >pulldown menu, do you mean the report parameters dropdown?
> >
> >--
> >Ravi Mumulla (Microsoft)
> >SQL Server Reporting Services
> >
> >This posting is provided "AS IS" with no warranties, and
> confers no rights.
> >"JIM.H." <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:0d1901c46ea3$9d514490$a401280a@.phx.gbl...
> >> Ravi, you know me, I need a little bit more help. LOL.
> >> what should I do to make sure that the oulldown item
> >> visaible and invisibale. I have more question below.
> >> Thanks,
> >> Jim.
> >>
> >> >--Original Message--
> >> >This should be typed into the Data pane of report
> >> designer.
> >> >
> >> >--
> >> >Ravi Mumulla (Microsoft)
> >> >SQL Server Reporting Services
> >> >
> >> >This posting is provided "AS IS" with no warranties,
> and
> >> confers no rights.
> >> >"JIM.H." <anonymous@.discussions.microsoft.com> wrote in
> >> message
> >> >news:0da201c46e9a$31884690$a301280a@.phx.gbl...
> >> >> Thanks, Where should I use this SQL. Let's say, I am
> >> >> running my report, at that time I need to check If
> this
> >> >> string returns anything, if not display a pulldown
> menu,
> >> >> accept user selection and update tblPullDownMenu.
> >> >> How shoudl I do this? I am new at reporting.
> >> >>
> >> >>
> >> >> >--Original Message--
> >> >> >Minor clarification: This would have to be a query
> >> >> expression:
> >> >> >
> >> >> >="SELECT * FROM tblPullDownMenu WHERE userid = "
> &
> >> >> User!UserID
> >> >> >
> >> >> >--
> >> >> >Ravi Mumulla (Microsoft)
> >> >> >SQL Server Reporting Services
> >> >> >
> >> >> >This posting is provided "AS IS" with no warranties,
> >> and
> >> >> confers no rights.
> >> >> >"Stoma_Kalos" <fzuma@.yahoo.com> wrote in message
> >> >>
> >news:e876e3c8.0407201152.7951b8c5@.posting.google.com...
> >> >> >> Throw the userid in the WHERE clause (assuming you
> >> are
> >> >> using a query
> >> >> >> for the pull-down).
> >> >> >>
> >> >> >> SELECT *
> >> >> >> FROM tblPullDownMenu
> >> >> >> WHERE userid = user.userid
> >> >> >>
> >> >> >>
> >> >> >> "JIM.H." <anonymous@.discussions.microsoft.com>
> wrote
> >> in
> >> >> message
> >> >> >news:<08d201c46e65$5bc843e0$a601280a@.phx.gbl>...
> >> >> >> > Hello,
> >> >> >> > When I lunch my report I want to display a pull-
> >> down
> >> >> menu
> >> >> >> > based on the user id. I know how to get userid
> but
> >> I
> >> >> do
> >> >> >> > not know how I can use it to display pull-down
> >> menu,
> >> >> for
> >> >> >> > some user id I should not display anything. Also
> >> if I
> >> >> >> > display the pull-down, after selection I should
> >> write
> >> >> >> > value selected to a table in SQL. How can I do
> >> that?
> >> >> >> > Thanks,
> >> >> >> > Jim.
> >> >> >
> >> >> >
> >> >> >.
> >> >> >
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >

Pull Replication Error 1069

I have a pull replication set up for users who synchronize data once in a
week. This replication is subscribed to a NT group with 5 users.
When a user is trying to synchronize the data, it is giving the error
message 1069 and the service did not start due to a logon failure.
Any help is greatly appreciated to resolve this issue.
David,
please have a look at Neil's explanation:
http://www.windowsitpro.com/Article/...376/14376.html
(replication runs as a series of jobs implemented by the sql server agent).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Pull between 2 dates from user input

I want to pull dates from my database that are between to set dates i have written a query that does this which looks like this:

"SELECT OCH_ID, empno, Selected_OCD, Start_Time, End_Time, Selected_OCDay, Selected_DOM, Selected_Month, Selected_Year FROM dbo.ICT_On_Call_Hours WHERE (CONVERT (datetime, LEFT (Selected_OCD, 2) + '/' + SUBSTRING(Selected_OCD, 4, 2) + '/' + RIGHT (Selected_OCD, 4)) >= CONVERT (datetime, LEFT ('12/02/2007', 2) + '/' + SUBSTRING('12/02/2007', 4, 2) + '/' + RIGHT ('12/02/2007', 4))) AND (CONVERT (datetime, LEFT (Selected_OCD, 2) + '/' + SUBSTRING(Selected_OCD, 4, 2) + '/' + RIGHT (Selected_OCD, 4)) < CONVERT (datetime, LEFT ('14/02/2007', 2) + '/' + SUBSTRING('14/02/2007', 4, 2) + '/' + RIGHT ('14/02/2007', 4)))"

This works when the dates are included in the statement but when i try and use parameters to pull them in like this:

"SELECT OCH_ID, empno, Selected_OCD, Start_Time, End_Time, Selected_OCDay, Selected_DOM, Selected_Month, Selected_Year FROM dbo.ICT_On_Call_Hours WHERE (CONVERT (datetime, LEFT (Selected_OCD, 2) + '/' + SUBSTRING(Selected_OCD, 4, 2) + '/' + RIGHT (Selected_OCD, 4)) >= CONVERT (datetime, LEFT ('@.Choice1', 2) + '/' + SUBSTRING('@.Choice1', 4, 2) + '/' + RIGHT ('@.Choice1', 4))) AND (CONVERT (datetime, LEFT (Selected_OCD, 2) + '/' + SUBSTRING(Selected_OCD, 4, 2) + '/' + RIGHT (Selected_OCD, 4)) < CONVERT (datetime, LEFT ('@.Choice2', 2) + '/' + SUBSTRING('@.Choice2', 4, 2) + '/' + RIGHT ('@.Choice2', 4)))"

I then recieve the following error message why is this?

ERROR ------> Syntax error converting datetime from character string.

Any Help would be greatly appreciated thanks

Dont surround a parameter with single quotes. Also if choice1 is a data already why not try something like this

"SELECT OCH_ID, empno, Selected_OCD, Start_Time, End_Time, Selected_OCDay, Selected_DOM, Selected_Month, Selected_Year FROM dbo.ICT_On_Call_Hours WHERE (CONVERT (datetime, LEFT (Selected_OCD, 2) + '/' + SUBSTRING(Selected_OCD, 4, 2) + '/' + RIGHT (Selected_OCD, 4)) >=@.Choice1 AND (CONVERT (datetime, LEFT (Selected_OCD, 2) + '/' + SUBSTRING(Selected_OCD, 4, 2) + '/' + RIGHT (Selected_OCD, 4)) <@.Choice2"

|||

Thanks for the immediate response that works perfectly exactly what i wanted

Thanks Again

Dazza22

Public user; tempdb permissions

We have financial application that uses SQL 2000 as the backend database. As part of the setup we were told that the "public" user account needed "full permissions" to the tempdb.

So, I right-click on the tempdb and go the the properties and then to the permissions and give "public" rights for all options.

Everytime the system is restarted after MS critical updates or any other reason the "public" user is reset back to the default settings and I have to go back in and reset the permissions for "public" in the tempdb.

Anyone know why this is happening? We have a production box and a development box and they both do the same thing.

Thanks for any insight.just curious, but did they give you any reason for having to do that?

I can't think of any reason that you'd have to. Have you tried just leaving it alone? If so, do the users get any kind of errors?

also curious as to the application that's requiring you to do something like that|||The application is Portia from Thomson Financial. It is a portfolio management app.

If the public user does not have rights to the tempdb then some reports that users run will return no data and other problems like funny application error messages.

They didn't give a reason why this is required, but I am guessing it is a cover for poor application design.|||your guess is correct. my hunch is they create permanent objects in tempdb (each user upon being added to a database is automatically enrolled into public group of that database. in properties window of tempdb they require permissions given to public to create all objects, not sure about other functions like backup, etc.)|||You can't backup a TempDb !! All users have permissions in a temp db and only they can access the temp table created with "#" Prefix. However , a Permanent temp table created by one user is owned by that user and behaves like any other table in a regular database . All those permissions and object would be gone after the SQL is restarted because tempdb , as the name says is a temporary database and is recreated each time SQl Server restarts|||a permanent object cannot be created in a database, including tempdb, unless a user is explicitly granted permissions to create that type of object or the user belongs to the database group/role or the server fixed role that has those permissions. in this discussion the vendor requires public to have permissions to create certain objects in tempdb. of course it gets recreated every time the service restarts! that's not the question! the question is how to make it so that granting permissions will not be the chore to do every time it happens. i'd go with a startup procedure that would grant the required permissions to public.|||I think that besides creating a startup procedure to grant the rights you need to call that company and tell them that thier developers need to take a look at what they are doing and add 'reboot server and test functionality again' to thier QA process. I sure hope that's an alpha or beta release of that software... what a freakin' nightmare.

why in the world wouldn't they just create the objects in the database they are working in? or store those objects in another database if they had some concern about the objects being there.

sorry to sound ranty... that kind of stuff just plain bothers me|||you're right on man, right on!|||Though I never tested it , but objects created in Model, would they be created in tempdb when system restarts ? OR Tempdb doesn't copy model structure ?|||Originally posted by aashu
Though I never tested it , but objects created in Model, would they be created in tempdb when system restarts ? OR Tempdb doesn't copy model structure ?

BOL:

The model database is used as the template for all databases created on a system. When a CREATE DATABASE statement is issued, the first part of the database is created by copying in the contents of the model database, then the remainder of the new database is filled with empty pages. Because tempdb is created every time SQL Server is started, the model database must always exist on a SQL Server system.|||If that is the case , Why not put all those users and permissions in Model !!

public role question

Using SS2000 SP4. I'm trying to lock down the database as much as possible.
I
created a new sql server login and gave the user no permissions other than
public role.
Why am I able to create a stored procedure with the new user. I thought
Public had select, insert, update, delete permissions. Does that allow a use
r
to create a stored procedure?
Thanks,
--
Dan D.I ran this query EXEC sp_helprotect NULL, 'public' and saw that public has
the permissions to create Default, create function, create procedure, create
rule, create table, create view, backup database, and backup transaction.
I created a new database and these public did not have these permissions so
I guess public doesn't have them by default.
Is it better to revoke or deny those permissions to public?
Thanks,
--
Dan D.
"Dan D." wrote:

> Using SS2000 SP4. I'm trying to lock down the database as much as possible
. I
> created a new sql server login and gave the user no permissions other than
> public role.
> Why am I able to create a stored procedure with the new user. I thought
> Public had select, insert, update, delete permissions. Does that allow a u
ser
> to create a stored procedure?
> Thanks,
> --
> Dan D.|||In the new database that I created, I was able to create a stored procedure.
I ran EXEC sp_helprotect NULL, 'public' but I don't see the "create
procedure" permisson listed under the action column. How am I able to create
a stored procedure with a user that is only a member of public and has no
other permissions?
--
Dan D.
"Dan D." wrote:

> Using SS2000 SP4. I'm trying to lock down the database as much as possible
. I
> created a new sql server login and gave the user no permissions other than
> public role.
> Why am I able to create a stored procedure with the new user. I thought
> Public had select, insert, update, delete permissions. Does that allow a u
ser
> to create a stored procedure?
> Thanks,
> --
> Dan D.|||What does the query 'SELECT USER' return?
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:2E69E961-1C77-4B9D-9962-F4C11B7017EB@.microsoft.com...[vbcol=seagreen]
> In the new database that I created, I was able to create a stored
> procedure.
> I ran EXEC sp_helprotect NULL, 'public' but I don't see the "create
> procedure" permisson listed under the action column. How am I able to
> create
> a stored procedure with a user that is only a member of public and has no
> other permissions?
> --
> Dan D.
>
> "Dan D." wrote:
>|||It returns 'dbo'.
--
Dan D.
"Dan Guzman" wrote:

> What does the query 'SELECT USER' return?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:2E69E961-1C77-4B9D-9962-F4C11B7017EB@.microsoft.com...
>
>|||Hi Dan
The user dbo is a privileged user and can create tables and procs, execute
procs, and access data in all the tables. You should try to revoke
permissions from the user dbo.
You have said you created a new user. How are you trying to connect as that
new user?
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:FB897411-12FA-47C0-BDD8-5855475E1A2C@.microsoft.com...[vbcol=seagreen]
> It returns 'dbo'.
> --
> Dan D.
>
> "Dan Guzman" wrote:
>|||The new user that I created in the new database I connected to through QA an
d
was able to create a stored procedure. If I run EXEC sp_helprotect NULL,
'public', the only permissions that public has is for "select" to 18 system
tables. If I run "select user" (and what does this tell me - who owns the
database?), I get 'dbo'. So I'm wondering how I'm able to create a stored
procedure if public doesn't have permissions and the new user is only in the
public role with no other permissions?
Are you saying that the permissions to create the stored procedure are
coming from dbo so I need to revoke them from 'dbo'?
The ultimate objective is to be able to create a login/user that a web app
will use and the user should only have permissions for "select" to the views
and "execute" on the stored procedures with no permissions on the underlying
tables. Would I need to revoke insert, update, and delete permissions from
public in this case?
Thanks for you help,
--
Dan D.
"Kalen Delaney" wrote:

> Hi Dan
> The user dbo is a privileged user and can create tables and procs, execute
> procs, and access data in all the tables. You should try to revoke
> permissions from the user dbo.
> You have said you created a new user. How are you trying to connect as tha
t
> new user?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:FB897411-12FA-47C0-BDD8-5855475E1A2C@.microsoft.com...
>
>|||If 'select user' is returning DBO, it means you are connecting AS DBO, and
not as your new user.
You ARE the special DBO user, and not the new user, which is why you have
all these permissions. You are not connecting as your new user and not
testing the permissions the new user has.
So again, please tell us exactly HOW you are trying to connect as the new
user. If you are connecting through QA, please tell us what you are putting
in every box in the connection dialog.
(Telling you that dbo 'owns' the database would be meaningless. DBO is a
user name. It is the name always given to whatever login is the owner of the
database, when that login connects to the database. Please read about logins
and user names in the Books Online.)
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:A193F3D7-B34C-49A8-AA8E-2CA851011AFD@.microsoft.com...[vbcol=seagreen]
> The new user that I created in the new database I connected to through QA
> and
> was able to create a stored procedure. If I run EXEC sp_helprotect NULL,
> 'public', the only permissions that public has is for "select" to 18
> system
> tables. If I run "select user" (and what does this tell me - who owns the
> database?), I get 'dbo'. So I'm wondering how I'm able to create a stored
> procedure if public doesn't have permissions and the new user is only in
> the
> public role with no other permissions?
> Are you saying that the permissions to create the stored procedure are
> coming from dbo so I need to revoke them from 'dbo'?
> The ultimate objective is to be able to create a login/user that a web app
> will use and the user should only have permissions for "select" to the
> views
> and "execute" on the stored procedures with no permissions on the
> underlying
> tables. Would I need to revoke insert, update, and delete permissions from
> public in this case?
> Thanks for you help,
> --
> Dan D.
>
> "Kalen Delaney" wrote:
>|||I'm sorry. I didn't understand what Dan was asking me to do. If I log in
through QA using the new user and run "select user" I get "dantest" which is
the new user. Then I am able to create a stored procedure. If I run 'EXEC
sp_helprotect NULL, 'dantest'', I get "There are no matching rows on which t
o
report.". If I run EXEC sp_helprotect NULL, 'public', I get a list of 18
system tables with select permissions.
--
Dan D.
"Kalen Delaney" wrote:

> If 'select user' is returning DBO, it means you are connecting AS DBO, and
> not as your new user.
> You ARE the special DBO user, and not the new user, which is why you have
> all these permissions. You are not connecting as your new user and not
> testing the permissions the new user has.
> So again, please tell us exactly HOW you are trying to connect as the new
> user. If you are connecting through QA, please tell us what you are puttin
g
> in every box in the connection dialog.
> (Telling you that dbo 'owns' the database would be meaningless. DBO is a
> user name. It is the name always given to whatever login is the owner of t
he
> database, when that login connects to the database. Please read about logi
ns
> and user names in the Books Online.)
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:A193F3D7-B34C-49A8-AA8E-2CA851011AFD@.microsoft.com...
>
>|||Sorry I didn't make my instructions clearer. A common cause of elevated
permissions is that users are members of the 'BUILTIN\Administrators'
Windows group and thereby get sysadmin permissions. User 'dbo' would be
returned in that case.
Since 'SELECT USER' returns 'dantest' instead of'dbo', we know 'dantest' is
not a sysadmin role member and is not the database owner. That means one of
the following must be true for the user to create procs:
1) The user is a member of the db_owner role. You can check this with
sp_helprolemember 'db_owner'
2) The user is a member of the db_ddladmin role. You can check this with
sp_helprolemember 'db_ddladmin'
3) The user (or a role the member belongs to) was granted CREATE PROCEDURE
permissions. You can check this with sp_helprotect 'CREATE PROCEDURE'
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:583E27E1-1C79-40D8-A9D2-A22B348D2154@.microsoft.com...[vbcol=seagreen]
> I'm sorry. I didn't understand what Dan was asking me to do. If I log in
> through QA using the new user and run "select user" I get "dantest" which
> is
> the new user. Then I am able to create a stored procedure. If I run 'EXEC
> sp_helprotect NULL, 'dantest'', I get "There are no matching rows on which
> to
> report.". If I run EXEC sp_helprotect NULL, 'public', I get a list of 18
> system tables with select permissions.
> --
> Dan D.
>
> "Kalen Delaney" wrote:
>

Public Role in SQL Server 2000 SP3

If I add a user to a database with Public Database Role, I
would like to know what privilege has been granted to him.
This is because we find that he is not able to SELECT any
table.
The default permissions of the public role allow SELECT from sysobjects and
other meta-data objects.
A security best practice is to create your own roles and grant object
permissions to those roles as needed. You can then control security by user
role membership.
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:2b73701c46890$d144c1a0$a501280a@.phx.gbl...
> If I add a user to a database with Public Database Role, I
> would like to know what privilege has been granted to him.
> This is because we find that he is not able to SELECT any
> table.
|||Hi,
The public role is a special database role to which every database user
belongs. Captures all default permissions for users in a database.
The user with public role can access only the tables / objects granted
permissions exclusevely to PUBLIC role.
For eg:
Hari is a database owner and he create a table customer. After creation he
gave the below statement to give previlage to customer table.
GRANT select on customer to public
IN this case user inside public role will be able to access the table
customer. Otherwise he can not.
Thanks
Hari
MCDBA
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:2b73701c46890$d144c1a0$a501280a@.phx.gbl...
> If I add a user to a database with Public Database Role, I
> would like to know what privilege has been granted to him.
> This is because we find that he is not able to SELECT any
> table.
|||If I want him to select all tables, what is the easiest
way to achieve it ?
Thanks

>--Original Message--
>The default permissions of the public role allow SELECT
from sysobjects and
>other meta-data objects.
>A security best practice is to create your own roles and
grant object
>permissions to those roles as needed. You can then
control security by user
>role membership.
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:2b73701c46890$d144c1a0$a501280a@.phx.gbl...
Role, I[vbcol=seagreen]
him.[vbcol=seagreen]
any
>
>.
>
|||HI,
You can assign the user "db_datareader" database fixed role. This will
enable the user to select from all tables
from the particular database.
How to add the role.
sp_addrolemember 'db_datareader','user_name'
Thanks
Hari
MCDBA
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:2ba7c01c46894$c6c84a40$a401280a@.phx.gbl...[vbcol=seagreen]
> If I want him to select all tables, what is the easiest
> way to achieve it ?
> Thanks
> from sysobjects and
> grant object
> control security by user
> message
> Role, I
> him.
> any
|||To add to Hari's response, db_datareader fixed database role members can
select from any table or view. However, execute permissions on read-only
stored procedures are not included.
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:2ba7c01c46894$c6c84a40$a401280a@.phx.gbl...[vbcol=seagreen]
> If I want him to select all tables, what is the easiest
> way to achieve it ?
> Thanks
> from sysobjects and
> grant object
> control security by user
> message
> Role, I
> him.
> any

Public Role in SQL Server 2000 SP3

If I add a user to a database with Public Database Role, I
would like to know what privilege has been granted to him.
This is because we find that he is not able to SELECT any
table.The default permissions of the public role allow SELECT from sysobjects and
other meta-data objects.
A security best practice is to create your own roles and grant object
permissions to those roles as needed. You can then control security by user
role membership.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:2b73701c46890$d144c1a0$a501280a@.phx.gbl...
> If I add a user to a database with Public Database Role, I
> would like to know what privilege has been granted to him.
> This is because we find that he is not able to SELECT any
> table.|||Hi,
The public role is a special database role to which every database user
belongs. Captures all default permissions for users in a database.
The user with public role can access only the tables / objects granted
permissions exclusevely to PUBLIC role.
For eg:
Hari is a database owner and he create a table customer. After creation he
gave the below statement to give previlage to customer table.
GRANT select on customer to public
IN this case user inside public role will be able to access the table
customer. Otherwise he can not.
Thanks
Hari
MCDBA
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:2b73701c46890$d144c1a0$a501280a@.phx.gbl...
> If I add a user to a database with Public Database Role, I
> would like to know what privilege has been granted to him.
> This is because we find that he is not able to SELECT any
> table.|||If I want him to select all tables, what is the easiest
way to achieve it ?
Thanks
>--Original Message--
>The default permissions of the public role allow SELECT
from sysobjects and
>other meta-data objects.
>A security best practice is to create your own roles and
grant object
>permissions to those roles as needed. You can then
control security by user
>role membership.
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2b73701c46890$d144c1a0$a501280a@.phx.gbl...
>> If I add a user to a database with Public Database
Role, I
>> would like to know what privilege has been granted to
him.
>> This is because we find that he is not able to SELECT
any
>> table.
>
>.
>|||HI,
You can assign the user "db_datareader" database fixed role. This will
enable the user to select from all tables
from the particular database.
How to add the role.
sp_addrolemember 'db_datareader','user_name'
Thanks
Hari
MCDBA
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:2ba7c01c46894$c6c84a40$a401280a@.phx.gbl...
> If I want him to select all tables, what is the easiest
> way to achieve it ?
> Thanks
> >--Original Message--
> >The default permissions of the public role allow SELECT
> from sysobjects and
> >other meta-data objects.
> >
> >A security best practice is to create your own roles and
> grant object
> >permissions to those roles as needed. You can then
> control security by user
> >role membership.
> >
> >--
> >Hope this helps.
> >
> >Dan Guzman
> >SQL Server MVP
> >
> >"Peter" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:2b73701c46890$d144c1a0$a501280a@.phx.gbl...
> >> If I add a user to a database with Public Database
> Role, I
> >> would like to know what privilege has been granted to
> him.
> >>
> >> This is because we find that he is not able to SELECT
> any
> >> table.
> >
> >
> >.
> >|||To add to Hari's response, db_datareader fixed database role members can
select from any table or view. However, execute permissions on read-only
stored procedures are not included.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:2ba7c01c46894$c6c84a40$a401280a@.phx.gbl...
> If I want him to select all tables, what is the easiest
> way to achieve it ?
> Thanks
> >--Original Message--
> >The default permissions of the public role allow SELECT
> from sysobjects and
> >other meta-data objects.
> >
> >A security best practice is to create your own roles and
> grant object
> >permissions to those roles as needed. You can then
> control security by user
> >role membership.
> >
> >--
> >Hope this helps.
> >
> >Dan Guzman
> >SQL Server MVP
> >
> >"Peter" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:2b73701c46890$d144c1a0$a501280a@.phx.gbl...
> >> If I add a user to a database with Public Database
> Role, I
> >> would like to know what privilege has been granted to
> him.
> >>
> >> This is because we find that he is not able to SELECT
> any
> >> table.
> >
> >
> >.
> >

Public Role in SQL Server 2000 SP3

If I add a user to a database with Public Database Role, I
would like to know what privilege has been granted to him.
This is because we find that he is not able to SELECT any
table.The default permissions of the public role allow SELECT from sysobjects and
other meta-data objects.
A security best practice is to create your own roles and grant object
permissions to those roles as needed. You can then control security by user
role membership.
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:2b73701c46890$d144c1a0$a501280a@.phx
.gbl...
> If I add a user to a database with Public Database Role, I
> would like to know what privilege has been granted to him.
> This is because we find that he is not able to SELECT any
> table.|||Hi,
The public role is a special database role to which every database user
belongs. Captures all default permissions for users in a database.
The user with public role can access only the tables / objects granted
permissions exclusevely to PUBLIC role.
For eg:
Hari is a database owner and he create a table customer. After creation he
gave the below statement to give previlage to customer table.
GRANT select on customer to public
IN this case user inside public role will be able to access the table
customer. Otherwise he can not.
Thanks
Hari
MCDBA
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:2b73701c46890$d144c1a0$a501280a@.phx
.gbl...
> If I add a user to a database with Public Database Role, I
> would like to know what privilege has been granted to him.
> This is because we find that he is not able to SELECT any
> table.|||If I want him to select all tables, what is the easiest
way to achieve it ?
Thanks

>--Original Message--
>The default permissions of the public role allow SELECT
from sysobjects and
>other meta-data objects.
>A security best practice is to create your own roles and
grant object
>permissions to those roles as needed. You can then
control security by user
>role membership.
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message
> news:2b73701c46890$d144c1a0$a501280a@.phx
.gbl...
Role, I[vbcol=seagreen]
him.[vbcol=seagreen]
any[vbcol=seagreen]
>
>.
>|||HI,
You can assign the user "db_datareader" database fixed role. This will
enable the user to select from all tables
from the particular database.
How to add the role.
sp_addrolemember 'db_datareader','user_name'
Thanks
Hari
MCDBA
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:2ba7c01c46894$c6c84a40$a401280a@.phx
.gbl...[vbcol=seagreen]
> If I want him to select all tables, what is the easiest
> way to achieve it ?
> Thanks
>
> from sysobjects and
> grant object
> control security by user
> message
> Role, I
> him.
> any|||To add to Hari's response, db_datareader fixed database role members can
select from any table or view. However, execute permissions on read-only
stored procedures are not included.
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:2ba7c01c46894$c6c84a40$a401280a@.phx
.gbl...[vbcol=seagreen]
> If I want him to select all tables, what is the easiest
> way to achieve it ?
> Thanks
>
> from sysobjects and
> grant object
> control security by user
> message
> Role, I
> him.
> any

Public role granted to user in master database.

In one of our sql server 2000 databases, there is an
application user that has public granted to it in the
master database. Can this be a security issue? There are
no permissions granted on objects just the public role.
I'm awaiting feedback from the vendor to find out why the
user is there. I just want to make sure this user cannot
do any harm to the master database.Not sure what you are saying. Every database has a role named public, you ca
nnot remove this...
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Erin" <Erin_Regotti@.grainger.com> wrote in message news:a28701c3eb2d$f399e270$a601280a@.phx.gbl...

quote:

---

> In one of our sql server 2000 databases, there is an
> application user that has public granted to it in the
> master database. Can this be a security issue? There are
> no permissions granted on objects just the public role.
> I'm awaiting feedback from the vendor to find out why the
> user is there. I just want to make sure this user cannot
> do any harm to the master database.

---

|||And everybody has public access to the master database (via the guest
account, I believe).
Russell Fields
"Erin" <Erin_Regotti@.grainger.com> wrote in message
news:a28701c3eb2d$f399e270$a601280a@.phx.gbl...

quote:

---

> In one of our sql server 2000 databases, there is an
> application user that has public granted to it in the
> master database. Can this be a security issue? There are
> no permissions granted on objects just the public role.
> I'm awaiting feedback from the vendor to find out why the
> user is there. I just want to make sure this user cannot
> do any harm to the master database.

---

Public role granted to user in master database.

In one of our sql server 2000 databases, there is an
application user that has public granted to it in the
master database. Can this be a security issue? There are
no permissions granted on objects just the public role.
I'm awaiting feedback from the vendor to find out why the
user is there. I just want to make sure this user cannot
do any harm to the master database.Not sure what you are saying. Every database has a role named public, you cannot remove this...
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Erin" <Erin_Regotti@.grainger.com> wrote in message news:a28701c3eb2d$f399e270$a601280a@.phx.gbl...
> In one of our sql server 2000 databases, there is an
> application user that has public granted to it in the
> master database. Can this be a security issue? There are
> no permissions granted on objects just the public role.
> I'm awaiting feedback from the vendor to find out why the
> user is there. I just want to make sure this user cannot
> do any harm to the master database.|||And everybody has public access to the master database (via the guest
account, I believe).
Russell Fields
"Erin" <Erin_Regotti@.grainger.com> wrote in message
news:a28701c3eb2d$f399e270$a601280a@.phx.gbl...
> In one of our sql server 2000 databases, there is an
> application user that has public granted to it in the
> master database. Can this be a security issue? There are
> no permissions granted on objects just the public role.
> I'm awaiting feedback from the vendor to find out why the
> user is there. I just want to make sure this user cannot
> do any harm to the master database.

Public Role for SQL 2000

For SQL 2000, I see that the Public role has some privileges like SELECT,
UPDATE, DELETE, & EXECUTE, permissions for some User's database objects
(Tables, views, &Stored procedures .)
Is that a security concern?
What will happen if I remove these privileges or revoke them?
Will that effect the rest of the users?
I read many threads on the internet, but no one could tell me the answers
for the 3 questions above.
Thanks in advance.wit1 (wit1@.hotmail.com) writes:
> For SQL 2000, I see that the Public role has some privileges like SELECT,
> UPDATE, DELETE, & EXECUTE, permissions for some User's database objects
> (Tables, views, &Stored procedures .)
That is not the default.

> Is that a security concern?
It does not sound like the best security to me.

> What will happen if I remove these privileges or revoke them?
Impossible to tell as it depends on the application using the database.

> Will that effect the rest of the users?
Again, that depends on the application using the database.
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|||Hi, I too have same questions.
I tried to drop this public role but not able to drop.
I tried to Revoke Select permission but not able to do that. It is not
giving any error but not working as expected. Any urgent reply will be
gratly appreciated.
Thank,
Tushar Vaja
"wit1" <wit1@.hotmail.com> wrote in message
news:%23DBcPkqiHHA.1244@.TK2MSFTNGP04.phx.gbl...
> For SQL 2000, I see that the Public role has some privileges like SELECT,
> UPDATE, DELETE, & EXECUTE, permissions for some User's database objects
> (Tables, views, &Stored procedures .)
>
> Is that a security concern?
> What will happen if I remove these privileges or revoke them?
> Will that effect the rest of the users?
>
> I read many threads on the internet, but no one could tell me the answers
> for the 3 questions above.
> Thanks in advance.
>
>|||> Hi, I too have same questions.
> I tried to drop this public role but not able to drop.
> I tried to Revoke Select permission but not able to do that. It is not
> giving any error but not working as expected. Any urgent reply will be
> gratly appreciated.
> Thank,
> Tushar Vaja
>
> "wit1" <wit1@.hotmail.com> wrote in message
> news:%23DBcPkqiHHA.1244@.TK2MSFTNGP04.phx.gbl...
>|||Tushar (tushar_vaja@.yahoo.co.in) writes:
> I tried to drop this public role but not able to drop.
> I tried to Revoke Select permission but not able to do that. It is not
> giving any error but not working as expected. Any urgent reply will be
> gratly appreciated.
You could try DENY, but since everyone is in public, this could have
the undesired effect that no one can access anything.
But maybe there is some misunderstanding? Could you clarify more precisely
what tables that are accessible to users who should not get there?
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|||Hi, thanks for reply. My problem is below:
Whenever user run my exe on his system, my app will create two DB on his
local system. 1 DB contain general info and second DB contain some very
confidenmt info. Now i do not want user(the person who has installed my app)
to access the second DB but he can access 1st DB if he wants.
I do not knwo how to implemet this thing. Please help.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns99337CC774783Yazorman@.127.0.0.1...
> Tushar (tushar_vaja@.yahoo.co.in) writes:
> You could try DENY, but since everyone is in public, this could have
> the undesired effect that no one can access anything.
> But maybe there is some misunderstanding? Could you clarify more precisely
> what tables that are accessible to users who should not get there?
> --
> 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|||Tushar (tushar_vaja@.yahoo.co.in) writes:
> Whenever user run my exe on his system, my app will create two DB on his
> local system. 1 DB contain general info and second DB contain some very
> confidenmt info. Now i do not want user(the person who has installed my
> app) to access the second DB but he can access 1st DB if he wants. I do
> not knwo how to implemet this thing. Please help.
Presumably the user who installed the application will have admin rights
on the machine, and you cannot hide anything from an administrator.
You can of course, store the data in the database encrypted, and then
your application could decrypt the data as needed. But since the
application would have to hide the encryption key somewhere, it's not
safe from a user who is dead set from accessing the data, but at least
it protects you from the stray wanderer.
In any case, you need to cover this situation in the license agreement,
and explicitly say that disclosing the data in the database is not
permitted.
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|||Hi,
The following excerpt is taken from Chapter 5 - Microsoft SQL Server 2000
Security of Microsoft SQL Server 2000 Administrator's Pocket Consultant (ISB
N
0-7356-1129-7):
public is the default role for all database users. Users inherit the
permissions and privileges of the public role, and this role represents
their minimum permissions and privileges. Any role that you assign to a
user, beyond the public role, add permissions and privileges. If you want
all database users to have specific permissions, assign the permissions to
the public role.
The following excerpt is taken from Chapter 8 - Managing SQL Serer 2005
Security of Microsoft SQL Server 2005 Administrator's Pocket Consultant:
The guest user is a special user that you can add to a database to allow
anyone with a valid SQL Server login to access the database... Before using
the guest user, you should not the following information about the account:
The guest user is a member of the public server role and inherits the
permissions of this role.
The guest user must exist in a database before anyone can access it as a
guest.
The guest user is used only when a user account has access to SQL Server
but does not have access to the database through this user account.
Other topics around public server role that may be of interest include:
http://www.microsoft.com/technet/pr...in/sqlops3.mspx
http://www.microsoft.com/technet/pr...n/sp3sec01.mspx
http://www.microsoft.com/technet/pr...ploy/mysql.mspx
10 Steps to help Secure SQL Server 2000
https://www.microsoft.com/sql/prodi...n/sp3sec04.mspx
SQL Server 2000 SP3 Security Features and Best Practices: Implementation of
Server Level Security and Object Level Security
http://www.microsoft.com/technet/pr...n/sp3sec02.mspx
SQL Server 2005 (BOL) - Security Considerations for a SQL Server Installatio
n
http://msdn2.microsoft.com/en-us/library/ms144228.aspx
Regards,
Keith Wilson
Disclaimer: this posting is provided "as is" without implied or express
warranties.
"Tushar" wrote:

>
>

public permissions

I have a little problem.
I'm trying to configure permissions on sql server 2000. I have a user
"intranet" wich has permissions on a couple of tables and a stored
procedures. When I set the permissions from "public" (usergroup? can't find
it) to deny, intranet can't acces the tables. When I set no permissions on
public, intranet can acces the tables en SP's. Can somebody explain how it
comes that intranet can's acces these tables while the users permissions are
set to permit?
Kind regards,
Bert
(when replying to me please remove the antispam-part in the emailaddress)Hello, Janssen!
You shouldn't really be granting permissions to public unless you intend
everybody in the DB to have those permissions.
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
: Kind regards,
: Bert
-- Microsoft Outlook Express 5.50.4133.2400|||Every user is by default member of the role (that is what it's called in SQL
Server) 'public'. The SQL Server permissions system works in such a way that
you only have permission on a table, view or stored procedure if you have
been granted the permission directly or via one of the roles you are a
member of, _and_ it is not denied to you or one of the roles you are a
member of.
In your situation that means that members of the public role do not have
permissions to any tables if there are no permisions explicitly set (unless
they are granted to them directly or via another role), but denying a
permission to the public role will deny that permission to anyone who is a
member of the public role (= all the users in the database), regardless of
the permissions they have directly or via another role.
hth
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Janssen" <legedoosantispam@.hotmail.com> wrote in message
news:be1dqh$6783$1@.ID-200327.news.dfncis.de...
> I have a little problem.
> I'm trying to configure permissions on sql server 2000. I have a user
> "intranet" wich has permissions on a couple of tables and a stored
> procedures. When I set the permissions from "public" (usergroup? can't
find
> it) to deny, intranet can't acces the tables. When I set no permissions on
> public, intranet can acces the tables en SP's. Can somebody explain how it
> comes that intranet can's acces these tables while the users permissions
are
> set to permit?
>
> Kind regards,
> Bert
> (when replying to me please remove the antispam-part in the emailaddress)
>
>
>|||Thanx!
"Jacco Schalkwijk" wrote
> Every user is by default member of the role (that is what it's called in
SQL
> Server) 'public'. The SQL Server permissions system works in such a way
that
> you only have permission on a table, view or stored procedure if you have
> been granted the permission directly or via one of the roles you are a
> member of, _and_ it is not denied to you or one of the roles you are a
> member of.
> In your situation that means that members of the public role do not have
> permissions to any tables if there are no permisions explicitly set
(unless
> they are granted to them directly or via another role), but denying a
> permission to the public role will deny that permission to anyone who is a
> member of the public role (= all the users in the database), regardless of
> the permissions they have directly or via another role.
> hth
> --
> Jacco Schalkwijk MCDBA, MCSD, MCSE
> Database Administrator
> Eurostop Ltd.
>