User setup in 2000

[djj55]

Hello, (SQL Server 2000)
I created a login for a new user. Actually it will be used for a program to connect to the server.

Here is how I set up the login
SQL Server Authentication
A default database
Under database Access I gave permission to the three database the program uses. These have roles of public and db_datareader.

When I tried the connection it would not work. I finally tried a Query Analyzer session and it said something about not being able to read the master database.

I gave the login public permission to the master database and was able to connect.

What did I do wrong?

I need to find the answer before we implement the program which outside users will access over the web. Thus I need to now if this will open up the server to possible security issues.

Thank you,
djj

 

[mrdenny]

Sounds like you have disabled the guest login on the server. With the guest login disabled, if you have the master database setup as the default database, then you will need to setup the login as a user within the master database and give it access to the public role.

If you don't want the user to have access to the master database you can change the default database to a database that the user already has rights to.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[djj55]

Interesting. Yes we have disabled the guest login.

That makes sense but the default database for the login is not "master".

Thank you for the reply.
djj

 

[mrdenny]

What's the exact error message?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[djj55]

SQL Query Analyzer

Unable to connect to server MyServer:

Server: Msg 229, Level 14, State 5
[Microsoft][ODBC SQL Server Driver][SQL Server]Select permissino denied on object 'spt_values', database 'master', owner 'dbo'.

 

[mrdenny]

spt_values is a table in the master database which all users need access to. There's a little info about it posted here

http://windowsitpro.com/article/art...i-get-more-info-on-the-spt_values-table.html.

They get the rights to the object by being a member of the public group in the master database.

Why did you remove the guest account?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[djj55]

They said they removed it for security reasons before I got here.

Thank you for the information, I will look at the article.
djj

 

[djj55]

Hello Denny,
Another security question.

In 2005 there is a role for stored procedure execution, is there such a role in 2000 say Process Administrator.

What I want to set up is a login to be the owner of the scheduled job which will run a stored procedure. Thus permission to the associated databases is also needed.

As you can tell I am still trying to learn about security. This comes from poor memory and the security not working the way I think it should. Example: I have a user that I have tried to limit but they still have access to admin functions. I can keep them out all together or they seem to have full admin rights? Man am I confused.

Thank you for your assistance.
djj

 

[mrdenny]

If you are setting up a job to run a stored procedure you'll want to grant the right to only that procedure. You can grant the rights to the user via the GRANT command.

GRANT EXEC ON YourProcedure TO YourUser

What roles are you adding the user to when you create the users account?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[djj55]

For the user account I am having problems with:
Under Security\Logins
- right click and choose New Login...
- type the username
- use windows authentication
- type the domain
- leave Grant access
- select a database other than master
- do not select any roles
- specify databases (include master see above)
- set all selected database except master to db_datareader and db_datawriter (also Public by default)

Thanks
Dana

 

[mrdenny]

If you make the user a member of the db_datareader and db_datawriter roles the user will have access to select, insert, update and delete all data in all tables.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[djj55]

When you say "all data in all tables" do you mean with in a given database or all database, if I only select the roles for the individual databases?

Thank you,
djj

 

[mrdenny]

Within the database which you place the user within the role.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog