As far as I can tell, there is no difference between the 2 operations and both seem to achieve the same thing. From this I assume that bulkadmin is just a wrapper around the ADMINISTER feature although I'd be interested in knowing (as I couldn't find out) if there are other permissions the server role gives you.
Lets look at the example (running on SQL2008 SP2)
-- create some test logins
CREATE LOGIN [DOMAIN\user1] FROM WINDOWS
GO
CREATE LOGIN [DOMAIN\user2] FROM WINDOWS
GO
-- lets impersonate a user
EXECUTE AS LOGIN = 'BARRHIBB\user1'
GO
-- and check the server permissions
SELECT * FROM sys.server_permissions
GO
SELECT * FROM fn_my_permissions(NULL,'SERVER')
GO
-- does the user have bulk op permissions?
SELECT has_perms_by_name(NULL, NULL, 'ADMINISTER BULK OPERATIONS');
GO
-- lets go back to being a superuser
REVERT
GO
USE MASTER
GO
-- how about if we just grant administer bulk operations to the user?
GRANT ADMINISTER BULK OPERATIONS TO [DOMAIN\user1]
GO
-- lets impersonate that user
EXECUTE AS LOGIN = 'DOMAIN\user1'
GO
-- and check the server permissions
SELECT * FROM sys.server_permissions
GO
SELECT * FROM fn_my_permissions(NULL,'SERVER')
GO
-- does the user have bulk op permissions?
SELECT has_perms_by_name(NULL, NULL, 'ADMINISTER BULK OPERATIONS');
GO
-- can the user grant other users to be bulky people?
GRANT ADMINISTER BULK OPERATIONS TO [DOMAIN\user2]
GO
Msg 4613, Level 16, State 1, Line 2
Grantor does not have GRANT permission.
So lets see what happens with the server role:
REVERT
-- remove the users permissions
REVOKE ADMINISTER BULK OPERATIONS TO [DOMAIN\user1]
GO
-- now add the user to the bulk admin role and check permissions again
EXEC MASTER..sp_addsrvrolemember @loginame = N'DOMAIN\user1', @rolename = N'bulkadmin'
GO
-- lets impersonate that user
EXECUTE AS LOGIN = 'DOMAIN\user1'
GO
-- and check the server permissions
SELECT * FROM sys.server_permissions
GO
SELECT * FROM fn_my_permissions(NULL,'SERVER')
GO
-- does the user have bulk op permissions?
SELECT has_perms_by_name(NULL, NULL, 'ADMINISTER BULK OPERATIONS');
GO
NB: The main difference here is that the permission has not been explicitly granted but it is an effective permission.
-- perhaps with the server role the user grant other users to be bulky people?
GRANT ADMINISTER BULK OPERATIONS TO [DOMAIN\user2]
GO
Msg 4613, Level 16, State 1, Line 2
Grantor does not have GRANT permission.
So it seems that the bulkadmin server role doesn't offer any "admin" from a security point of view which begs the question, just why would you add a user to this role rather than just explicity grant them the permission through the GRANT statement (or vice versa)?
Hi
ReplyDeleteHave you tried the bulk insert from various folders. The read permissions of bulk admin and bulk operations seem to differ. For some windows folder bulk operations alone doesn't help in importing the files using bulk insert command. While when we use bulk admin it works all the time. I'm still trying to figure out the reason.
Thanks
Ravi