permission to user - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

permission to user

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2009-03-05 : 11:26:55

what permission should i give to users, if I want them to read data and create a stored procedure ( only selecting records) but not to modify the tables or insert/update /delete records in sql server 2005.

swekik
Posting Yak Master

126 Posts

Posted - 2009-03-05 : 12:04:15

Create a role and assign the permissions to that role . Add the specific objects to that role and assign the permissions .

jung1975
Aged Yak Warrior

503 Posts

Posted - 2009-03-05 : 13:08:22

thanks.. what kind of permission should i give to achive my goal

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-05 : 13:10:02

Give create and select permission to your desired objects.

jung1975
Aged Yak Warrior

503 Posts

Posted - 2009-03-05 : 15:54:34

I did create a Report_User database role and grant create procedure and SELECT permission to that role.. and I have assigned this role to a user

GRANT CREATE PROCEDURE TO Report_User
GRANT SELECT TO Report_User

When I try to create a SP
create procedure test

as

select * from Access.

I keep getting an below error

The specified schema name "dbo" either does not exist or you do not have permission to use it.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-05 : 16:16:55

You have to give this one as well:

GRANT ALTER ON SCHEMA::DBO TO Role

jung1975
Aged Yak Warrior

503 Posts

Posted - 2009-03-06 : 13:30:24

thanks..
One thing that still not working is that thsi user still can import the table using Import Wizard on SSMS..

but can not insert record using T-SQL or SSMS..

How is this possible? and how can i prevent this user from using Import Wizard?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-06 : 14:17:34

Are you using Import Wizard witin the instances/server or outside the server?

jung1975
Aged Yak Warrior

503 Posts

Posted - 2009-03-06 : 22:34:47

I've logged in as a user and using a Import Wizard within the instances/server

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-08 : 15:57:41

Here are details.
http://msdn.microsoft.com/en-us/library/ms141209.aspx