Granting CREATE Procedure permission to user

Question

Post reply

Granting CREATE Procedure permission to user

SQL006

SSCertifiable

Points: 5071
More actions
May 12, 2011 at 4:22 am

#250166

Hi
I created a user 'test' and assigned the db_owner role to the user, under the security tab in sql server management studio.
I wated to give the user permission for CREATE PROCEDURE
After searchin the internet i found these t-sql
GRANT ALTER ON SCHEMA::dbo TO test
GO
GRANT CREATE PROCEDURE TO test
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo TO test
Go
These statements executed sucessfully...
But when i try to CREATE PROCEDURE under the test user
this error is coming
Msg 18456, Level 14, State 1, Line 1
Login failed for user 'pradeep1'.
Pls help
Thank you in advance
with regard
Dilip d

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

Login failed for user 'pradeep1'.

the above error has to do with no login in the server for that user;

that's a different issue, not related to the user/role you created...

only AFTER the login process is completed, and the USER that should be mapped to the login in your database.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 2

http://blogs.msdn.com/b/sql_protocols/archive/2006/02/21/536201.aspx

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

SQL006 SSCertifiable Points: 5071 More actions · Answer 3

thnks for the reply

obviously i created the login first and then created the user

Still this error is coming

Lowell SSC Guru Points: 323518 More actions · Answer 4

dilipd006 (5/12/2011)
thnks for the reply
obviously i created the login first and then created the user
Still this error is coming

you said when you are trying to use the user test....

are you doing something like this? show us your real exact code. like i said, a login shouldn't be occuring...is there a linked server you forgot to mention?

EXECUTE AS USER='TEST"

CREATE PROCEDURE...

REVERT;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQL006 SSCertifiable Points: 5071 More actions · Answer 5

created the LOGIN 'test' through this process

1.under instance clicked the SECURITY tab

2.LOGINS ---New Logins --> open the pop up Window---> Give the Login name test and password test(SQL server authentication) --->Checked only Enforce password policy---under default database selected the database 'DBTest'--language english

3 Under server roles it is already selected public

4.Under user mappings Selected the 'DBTest' --under Database role membership--checked db_owner

5.Under Securables and Status did not made any changes and clicked ok button

6.Under database 'DBTest' tab --> security tab the user 'test' is already available

After this executed these Statements

GRANT ALTER ON SCHEMA::dbo TO test

GO

GRANT CREATE PROCEDURE TO test

GO

GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo TO test

Go

AND Created a test procedure

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

Create procedure usp_test

@name varchar(50)

AS

BEGIN

SET NOCOUNT ON;

INSERT INTO dbo.Test

(Name)

VALUES

(@name)

END

YES there is a linked server...between Sql2005 and sql 2008

SQL006 SSCertifiable Points: 5071 More actions · Answer 6

thanks Lowell for the hint ...Linked server

Finally solved that problem by dropping the Database trigger which report the changes made in the 2008 DB to the 2005 DB ..which is linked by Linked Server.

How this CREATE PROCDURE permission is connect to the Linked Server ..if you can explain or forward any link it will be very helpfull

hp_dba_uk SSC-Addicted Points: 421 More actions · Answer 7

I created a local User Test1 on my local machine just to test some schema settings. Then I added the user in Logins at the Sql server instance level. Then I added the same user in Northwind DB User's list where I gave db_read and db_write rols and restricted to a schema.

But when I log in as this new user I cant see Northwind database at all. I did restart my machine.

I wonder what am doing wrong.. no hope for DBA career for me is there...:)

SQL006 SSCertifiable Points: 5071 More actions · Answer 8

hirenio_22 (5/17/2012)
Then I added the same user in Northwind DB User's list where I gave db_read and db_write rols and restricted to a schema.

what you mean by restricted to schema..dbo is the default schema..

Did u try with db_owner role..what is the result

hp_dba_uk SSC-Addicted Points: 421 More actions · Answer 9

I mean only give access to selected schema not to all.

Also give db_owner...should it make diff as I just want the user to be able to read/write not own it...