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: 323482 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

muthukkumaran Kaliyamoorthy

SSCoach

Points: 19026

May 12, 2011 at 4:30 am

#1323560

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

SQL006

SSCertifiable

Points: 5071

May 12, 2011 at 4:33 am

#1323561

thnks for the reply

obviously i created the login first and then created the user

Still this error is coming

Lowell SSC Guru Points: 323482 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 Old Hand Points: 391 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...:)