April 4, 2008 at 10:56 am
Hi there!
I want to create a SP that creates a login on a sql server 2005. I want the users that runs this SP not to have permissions to create a login, thus it is the permissions of the SP that allows to create a login.
I have the following:
CREATE PROC schemaWithOwnerThatHasRightsToCreateLogin.InsertUser
(
@UserFullName AS NVARCHAR(50),
@UserLogin AS NVARCHAR(30),
@UserPassword AS VARCHAR(30),
@UserClientId AS INT) WITH EXECUTE AS OWNER
AS
DECLARE @defDB AS VARCHAR(50)
SELECT @defDB = '[3DPROD]'
declare @createQuery as nvarchar(500)
set @createQuery = 'create login ' + @UserLogin + ' WITH PASSWORD = ''' + @UserPassword + ''', DEFAULT_DATABASE = ' + @defDB + ', CHECK_EXPIRATION = OFF'
execute sp_executesql @createQuery
When I run this I get the error: "User does not have permission to perform this action"
I read a little on dynamic SQL, and it states that when dynamic SQL is invoked in SP, the user calling the SP needs to have sufficient permissions, not the SP (check http://www.sqlservercentral.com/articles/Security/dynamicsqlversusstaticsqlp1/617/)
How can I make the mentioned work? Is there a work around where I do not need to use dynamic sql or can I force to execute the dynamic sql inside the SP with a certain user?
April 4, 2008 at 12:21 pm
Not that I know of.
April 4, 2008 at 2:10 pm
Depending on your configuration, you should be able to do this with a SQL Agent Job and definitely with Service Broker, if you set it up right.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 4, 2008 at 3:15 pm
Could you be a little more specific? I can't see the relation here...
April 4, 2008 at 3:34 pm
The SQL Server Agent job goes back to before SQL Server 2005 service broker and here's how it works.
There is a job that runs periodically which polls a table. That table is where someone who doesn't have rights to do something (like create a login, execute a job, whatever you define) has the ability to insert a row which the job will recognize and execute a task. For instance, with a CREATE LOGIN type of scenario, the username/password (hopefully encrypted) would be stored, along with any other information you might want, such as default database, etc. When the job runs, it would retrieve the information, perform the task (since you can have it run as sa or a privileged login), and then delete the row from the table.
With service broker you can do the same sort of thing without having to involve the agent.
K. Brian Kelley
@kbriankelley
April 4, 2008 at 8:40 pm
Well, Brian provided a better explanation of the Agent approach than I could have.
As for Service Broker, I just want to add, not only can do the same thing, with a lot less DB-external dependency, it can be setup to respond to a request immediately so that it's virtually instantaneous, while still being asynchronous.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 30, 2008 at 8:27 am
please excuse me for interrupting, but I'd like to do something related, but with a different twist.
Rather than creating a SQL login for each database user, do you have an ideas about impersonation. I'd like do something like this:
1) Users are setup in the employee table and they are given "employee" or "admin" permissions internally in my own tables. They have an employee number and Pin they use to login
2) I use a registry key and windows authntication to get into SQL server to read my tables. I'm not concerned about performance at login, so I don't mind a login/logout within my app to have them connect with the right "role"
3) once they login can I use application roles or something else to make this session use the SQL server role that matches my "standard" or "Admin" role
For example, user A is admin user, user B is normal employee. Both users share a PC in the kiosk setup using a registry key and shared windows account. When user A logs in I'd like him to have permissions I setup in my SQL "Admin role". When user B logs in, I'd like him to have permissions I assigne to SQL "employee role".
Got any ideas? Sorry for interrupting, if you want me to start a new thread, let me know.
Thanks
April 30, 2008 at 1:50 pm
You are better of starting a new thread since I cannot help you with your problem.
Cheers,
April 30, 2008 at 3:11 pm
Sorry about that, good luck with your problem. I tried to do the same in SQL 2000, but you can't add a server login inless you're SA. DBO's could make a server login a member of the DB, but that's as far as you can go.
I would lean the way of the work queues and see if you can create a work item, and have the worker thread in the work queue add the server and DB login.
Good luck
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply