Unanswered: can sp have permissions & looping DropRoleMember
several instances on the same server
different instances have different sets of users
some users have access to more than one instance
one of the instances has become rather too popular and user admin is becoming a regular hassle, so i'm trying to build an sp along the following lines to allow my admin-role users to handle the day-to-day:
sp_addlogin <<<--- PROBLEM #1
if not (ntlogin has dbaccess)
sp_droprolemember WHERE roles <> @myRole) <<<--- PROBLEM #2
if not (ntlogin in role @myRole)
is there a way for me to "grant dbo permissions to my sp" so that non-dbo users in admin-role can run sp_addlogin??
seems better to do it this way than using application roles: admin-role permissions give neat control over exec right on the sp and there is no need for messy passwords client-side.
how can i build a server-side way to sp_droprolemember where login = @ntlogin and role <> @myRole
am i doing any harm by running sp_addlogin or sp_grantdbaccess on users that already have login/access (apart from wasted clock cycles of course!) ??
all suggestions very welcome.
currently using SS 2008R2