Asigns or revokes permission on user, or nt account on tables or store procedures for any data base
code: Can't Copy and Paste this? Click here for a copy-and-paste friendly version of this code!
Terms of Agreement:
By using this code, you agree to the following terms...
You may use
this code in your own programs (and may compile it into a program and distribute it in compiled format for languages that allow it) freely and with no charge.
You MAY NOT redistribute this code (for example to a web site) without written permission from the original author. Failure to do so is a violation of copyright laws.
You may link to this code from another website, but ONLY if it is not wrapped in a frame.
You will abide by any additional copyright restrictions which the author may have placed in the code or code's description.
--**************************************
-- Name: Asigns or revokes permission on user,or nt account on tables or store procedures
-- Description:Asigns or revokes permission on user, or nt account on tables or store procedures for any data base
-- By: marisol gonzalez
--
-- Inputs:@user --> user or nt account (domain/user)
user or nt account create on sys_admin no affected at all.
--
-- Returns:a grant all on or remove all on script for every table and store procedure at current database.
--
-- Assumes:1. you must create this sp at master.
2. from query analyzer select any data base
3. run sp_asignarpermisos <user>
4. you will see 2 rowsets:
one for sp
one for tables
5. copy all or some you are interested and paste on query analyzer and run
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=734&lngWId=5--for details.--**************************************
--Genera el script para Asignar o revocar permisos para un usuario o cuenta nt (dominio/usuario)
--cuidado con el rol de sys_admin
--despues de correrlo, copie el resultado en el query analyzer y ejecute.
--parametros: @user --> nombre del usuario o del grupo o cuenta nt
--opcionales
--'a' ---asignar permisos
--'r'--> revocar permisos
--por omar: julio 31, 2003
CREATE PROCEDURE sp_asignarpermisos
@user as varchar(255),
@opcion as char(1)=null
AS
set nocount on
if ( (@opcion is null ) or ((lower(@opcion) <> 'a' ) and (lower(@opcion) <> 'r' )))
print 'Opciones: a ó nada--> asignar permisos, r --> remover permisos'
--Asignar permisos---
if ( (@opcion is null ) or ((lower(@opcion) = 'a')))
goto AsignarPermisos
--Remover Permisos
if (lower(@opcion) = 'r')
goto RemoverPermisos
AsignarPermisos:
--Asigna todos los permisos al usuario--
--Proc. Almacenados--
select 'grant all on ' + db_name() + '..' + name + ' to ' + @user as sp from sysobjects
where type = 'P' and category = 0
--Tablas
select 'grant all on ' + db_name() + '..' + name + ' to ' + @user as tablas from sysobjects
where type = 'U'
return
RemoverPermisos:
--Revoca todos los permisos al usuario--
--Proc. Almacenados--
select 'revoke all on ' + db_name() + '..' + name + ' to ' + @user as sp from sysobjects
where type = 'P' and category = 0
--Tablas
select 'revoke all on ' + db_name() + '..' + name + ' to ' + @user as tablas from sysobjects
where type = 'U'
return
GO
fu (If this comment was disrespectful, please report it.)
9/16/2004 3:21:43 AM:
fr (If this comment was disrespectful, please report it.)
9/16/2004 3:22:44 AM:
fasfds (If this comment was disrespectful, please report it.)
Add Your Feedback
Your feedback will be posted below and an email sent to
the author. Please remember that the author was kind enough to
share this with you, so any criticisms must be stated politely, or they
will be deleted. (For feedback not related to this particular code, please
click here instead.)
9/16/2004 3:20:27 AM: 
