SQL Security 2005 Question

[jmd0252]

OK in sql 2000 I would know, how,, but not in 2005. I need to setup a login for SQL. I need to give it acess to one database, and one database only. This does not need to be a user from our AD. I will use this login to connect the crystal reports web server.

 

[RiverGuy]

Here's an example, modify as needed.

CREATE LOGIN MyUser
WITH PASSWORD = 'MyPassword'
GO
CREATE USER MyUser FOR LOGIN MyUser
WITH DEFAULT_SCHEMA = dbo
GO
GRANT SELECT ON SCHEMA::dbo TO MyUser
GO

 

[mrdenny]

You should only grant SELECT rights to the schema if the user needs access to all the tables and views.

It's best practice to grant only the minimum rights needed.

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