Sp_adduser And The Old Question Of Why??
Jul 20, 2005
Hello Group
Ok, I've read the BOL topic on sp_adduser and have done some research here
in the groups but the 'why' question remains...
A simple thing:
I would like to empower 'User A' to be able to add logins and users to
database 'ABC' so I don't have to do it.
Let's assume I'm far away on a tropical island with no remote access for
several months ...ahhhh
Now, I've added the Login for 'User A' to the server roles 'Security
Administrators' and 'System Administrators' and given access to the
databases 'ABC' and 'master' - it all works, 'User A' can create logins and
users for 'ABC'
What I find a little bit disturbing is, that this gives 'User A' the
possibility to do anything with any database and I just wondered whether
there's a way.. to avoid this perhaps?
So just createing logins and creating users permissions for database 'ABC'
and as little as needed from everything else...
Please describe in detail on how to do this as I'm new to this and don't
have much of a clue..as you see!
Later on, I might want to create a 'UserB' with the same capabilities but
for now I'm just happy to get going.
Thanks for your help & efforts!!
Martin
View 2 Replies
Oct 9, 2001
I am trying to setup a user that will have access to execute the sp_adduser system stored procedure but cannot get it to work. I would assume that putting it in the db_accessadmin role would do this but it will not work. It tells me "Only members of the sysadmin role or the database owner can execute this stored procedure.". Just to pacify it, I gave it access to dbo in the database we are wanting to add the user but it still throws out the same error. Any ideas would be greatly appreciated.
View 1 Replies
View Related
Aug 13, 2006
I tried to execute the sp_adduser system stored procedure, but I always get an error saying that I don't have permission to perform this action:
Msg 15247, Level 16, State 1, Procedure sp_adduser, Line 35. User does not have permission to perform this action.
I've tried to execute sp_adduser with different users: one that is a member of the db_accessadmin role, the database owner, and even a member of the sysadmin server role. In all cases, I received the error showed above.
Is there some problem with this stored procedure ? I know that SQL Server 2005 recommends using CREATE USER, to add users to a database, but I couldn't get it to work with a user name and a password as parameters (combined with CREATE LOGIN).
View 8 Replies
View Related
Aug 9, 2007
I have a product purchased which has it's own sp_adduser stored procedure to add users to a table (seperate from sql server).
When the program tries to run sp_adduser, it executes the SQL 2005 sp_adduser and this is not what I want.
Is there a way to turn off sp_adduser for a certain user, or force it to use the local copy?
This program is compiled asp.net code and I cannot change the name of the procedure it calls.
Thanks,
Jimmy Cartrette
View 4 Replies
View Related
Sep 12, 2006
Hi guys,
I'm having an issue with using the sp_adduser stored procedure. What i'm doing is creating a new database through coldfusion. After the database is created i need to creatd a Login. So i used the sp_addlogin and all of that worked great. Next in order to make this work i need to add a user to a database role for the new database....
The problem i am having is when i do this, every time i do this the user is automatically put into the "master" database, instead of the database i need it in which is what was just created. I'm guessing this is because "master" is the database that contains the stored procedure for creating a new user. Is there a way in which i could use this stored procidure to create a user in the database that i just created? I was thinking maybe copy that stored procedure into the new database and use it that way but i'm not sure how to do this either. Everything needs to be done programmatically as well. Its all being built in one coldfusion function so i don't have the option to go into enterprise manager and manual do this.
Any help or advise would be great!
Thanks,
~Jamie
View 1 Replies
View Related
May 10, 1999
I am trying to add a new user (SQL Server 6.5) from an application program (Delphi 4.0). I was hoping I could do this in a stored procedure that basically acts a a wrapper around the SQL Server stored procedures.
It partially works - here is my procedure:
create procedure AddUserToSQLServer(@loginame varchar(30),
@Password varchar(30) ) as
begin
-- Add User to SQL Server Database
exec mydb.dbo.sp_addlogin @loginame,@Password,'mydb'
exec mydb.dbo.sp_adduser @loginame
end
The sp_addlogin adds the login and sets the default database as mydb. This is OK, except the user still can not login to the application. So I added the sp_adduser. This gives the user access to the application, but I still have a problem. When I go into Executive Manager, and go to the Manage Logins screen by double-clicking on the newly added user, I get the message "Error 21770: The name 'loginame' was not found in the users collection" where loginame is the users login. I click OK to this message, and the user is displayed on the manage logins screen. Their default database is mydb, a check is in the Permit box and their username is in the User column. Alias and Group are blank. When I try to drop the user, I get the same error again (2 times), then error "Error 15175: [SQL Server] login 'loginame' is aliased or mapped to a user in one or more database(s), drop user or alias before dropping login".
The only thing I found that works is to go to SQL and issue sp_dropuser loginame, then I can drop the user.
Anybody see what's wrong? Or know of a FAQ that addresses this situation?
Thanks
Wayne
View 1 Replies
View Related
