Security

[abhay]

hai,
i am a VB devekoper, and now working on a SQL Query Engine.... the appn. will QUERY the SQL Server(SQL)...i made the appn. to create a USER (say: QueryEngine), and i log in with the same UserName... now i want to set some restrictions for the User(QueryEngine) to provide READ-ONLY access, and that also through some SQL statements, so that i can quey the Server each time the appn is opened...
i tried out the "GRANT" sql key word but it didn;t work oput...

anybody has any solutions

expecting the reply ASAP

regards,
abhay

[skhanal]

The easiest way is to use Enterprise Manager and put the queryengine user in data_reader role in the database. If you want more restricted privileges, then you can grant them on per object basis.

If you want to use T-SQL then search books online for

GRANT
sp_addrolemember

[abhay]

thanks,
if the appn is installed the ADMIn has to do use the Enterprise Manager to Setup the permission, where as i am thinking of the appn itself will set the restrictions.....

[skhanal]

Then you have to use GRANT statements.

GRANT SELECT ON TABLE1 TO USER1;

etc.

[rmiao]

User needs proper permission to run grant statement.

[skhanal]

If the appn user is able to create user, it should be able to grant permissions as well. I am assuming appn is a dbo.