October 7, 2005 at 12:32 pm
need to give a user the ability to create logins and
then add that login to a database. Is it possible to do
this without granting System Administrator Role? I cannot
seem to do this any other way then giving the user
sysadmin. Any help would be greatly apperciated.
Thanks.
I have given the Login to the securityadmin fixed server role and to the
db_accessadmin fixed database role.
It is not working.
This is testing my ability as a DBA..pls respond as soon as you can.
Thanks,
Sreeni
October 7, 2005 at 12:38 pm
What do you mean by its not working?
error message?
October 7, 2005 at 12:41 pm
Thanks for the quick reply and I am getting the following error.
Server: Msg 15247, Level 16, State 1, Procedure sp_adduser, Line 35
User does not have permission to perform this action.
Server: Msg 15410, Level 11, State 1, Procedure sp_addrolemember, Line 66
October 7, 2005 at 12:45 pm
This is the script I am running.
/* CREATE Power Faids Logins in DBs */
DECLARE @username nvarchar(128)
DECLARE @return_status integer
DECLARE @sql_stmt varchar(256)
DECLARE @u_password varchar(256)
DECLARE @testdb1_access char(1)
DECLARE @testdb2_access char(1)
SET @username = 'TESTLOGN'
SET @u_password = 'ev!96kr'
SET @testdb1_access = 'Y'
SET @testdb2_access = 'N'
-- Check If Username Exist
USE master
IF @Username =
(SELECT name
FROM sysxlogins
WHERE name = @username)
BEGIN
PRINT ' ** User Name "' + @username + '" Already In use, Please use a different User Name ** '
RETURN
END
-- Check and Drop the user in all databases
Use testdb1
IF @username = (SELECT name FROM sysusers WHERE name = @username)
BEGIN
SET @sql_stmt = 'sp_dropuser @name_in_db ="' + @username + '"'
--PRINT @sql_stmt
EXECUTE (@sql_stmt)
PRINT 'Dropped User "' + @username + '" in testdb1'
END
Use testdb2
IF @username = (SELECT name FROM sysusers WHERE name = @username)
BEGIN
SET @sql_stmt = 'sp_dropuser @name_in_db ="' + @username + '"'
--PRINT @sql_stmt
EXECUTE (@sql_stmt)
PRINT 'Dropped User "' + @username + '" in testdb2'
END
-- Drop and recreate logins
SET @sql_stmt = 'sp_droplogin @loginame = "' + @username + '"'
PRINT @sql_stmt
EXECUTE (@sql_stmt)
-- SET @sql_stmt = 'sp_addlogin @loginame= "' + @username + '", @passwd = "' + @u_password + '"'
SET @sql_stmt = 'sp_addlogin @defdb = "testdb1", @loginame = "' + @username + '", @passwd = "' + @u_password + '"'
PRINT @sql_stmt
EXECUTE (@sql_stmt)
PRINT ' '
-- Setup users for each database
IF @testdb1_access = 'Y'
BEGIN
Use testdb1
SET @sql_stmt = 'sp_adduser @loginame="' + @username + '",@name_in_db="' + @username + '"'
--PRINT @sql_stmt
EXECUTE (@sql_stmt)
SET @sql_stmt = 'sp_addrolemember @membername="' + @username + '",@rolename="dba"'
--PRINT @sql_stmt
EXECUTE (@sql_stmt)
PRINT 'User Id "' + @username + '"Added to - testdb1 DB'
PRINT ' '
END
IF @testdb2_access = 'Y'
BEGIN
Use testdb2
SET @sql_stmt = 'sp_adduser @loginame="' + @username + '",@name_in_db="' + @username + '"'
--PRINT @sql_stmt
EXECUTE (@sql_stmt)
SET @sql_stmt = 'sp_addrolemember @membername="' + @username + '",@rolename="dba"'
--PRINT @sql_stmt
EXECUTE (@sql_stmt)
PRINT 'User Id "' + @username + '"Added to - testdb2 DB'
PRINT ' '
END
October 7, 2005 at 2:07 pm
Instead of sp_addUser, Use sp_Grantdbaccess (sp_adduser is old)
for the user to do this he must be a member of the SecurityAdminisrators role, which you have. But also a member of the db_accessAdmin fixed database role in each database the user is allowd to give users access to.
But to use the sp_addrolemember
Only members of the sysadmin fixed server role and the db_owner fixed database role can execute sp_addrolemember to add a member to fixed database roles. Role owners can execute sp_addrolemember to add a member to any SQL Server role they own. Members of the db_securityadmin fixed database role can add users to any user-defined role.
I tested your script out using pubs and northwind and it works to grant access but no sp_addrolemember unless I make my user dbo.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply