July 26, 2006 at 12:07 am
Hi All,
I have created a SQL Server Login called Vikas with Default Schema as dbo in a SQL Server 2005 Database.
The Login has been Granted following Permissions:
GRANT CREATE FUNCTION TO [Vikas]
GO
GRANT CREATE PROCEDURE TO [Vikas]
GO
GRANT CREATE VIEW TO [Vikas]
GO
GRANT DELETE TO [Vikas]
GO
GRANT EXECUTE TO [Vikas]
GO
GRANT INSERT TO [Vikas]
GO
GRANT SELECT TO [Vikas]
GO
GRANT UPDATE TO [Vikas]
GO
GRANT VIEW DEFINITION TO [Vikas]
GO
The Login still can't create a new procedure or a view or a function. I am missing Something with SQL 2005 Security here, not sure what.
--------------------------------------------------------------------------------
IF I
GRANT ALTER TO [Vikas]
Then the Create statements start working but it also means that a user can alter any existing tables as well,we don't want that.
Any suggessions are Welcome.
Thanks
Vikas..
July 30, 2006 at 9:54 am
Vikas,
1. Would like to see the statement that you executed to create the login, in the first place.
2. Please confirm if you have the user name as the login name (Are both Vikas?)
3. Did you switch back & forth between Windows authentication & mixed-mode? I understand, you switched to Mixed mode atleast once, otherwise the you'd have got 'Login error'.
Seyed Ibrahim
Verizon Data Services India
MCDBA, MCTS SQL 2005 Charter Member
July 30, 2006 at 5:30 pm
Hi Seyed,
Thanks for replying back.
USE
[master]
GO
CREATE
LOGIN [Vikas] WITH PASSWORD=N'vikas', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE MyDB
CREATE
USER [Vikas] FOR LOGIN [Vikas]
GO
No i didn't switch between authentication modes, By default we use Mixed mode.
I have done further investigation into this issue and it seems like the View Defination Permission is causing the problem.
If I grant ALTER to USER.. and Remove View Defication then it seems to work properly but then again the issue is that the users can't view the objects.
Vikas.
Thanks
Vikas..
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply