Rol sql server 2008

[ilu7ionHD]

Hola que tal, necesito crear un rol para un administrador de BD.
Por lo tanto puede leer, ingresar, actualizar y eliminar datos, tambien puede dar permisos, esta forma de da error:

Código SQL:

CREATE ROLE administrador AUTHORIZATION db_datareader, db_ddladmin, db_datawriter;
 

[Libras]

y porque no le das los permisos de sysadmin?

[ilu7ionHD]

Acaso ese que mencionastes INCLUYE TODOS LOS OTROS PERMISOS, ?
o son todos independientes? por ejemplo puedo ser db_accessadmin para poder agregar o quitar el acceso a la base de datos para inicios de sesión de Windows, pero no tengo el permiso db_datareader , entonces no puedo leer nada?.
Lo que quiero decir es lo siguiente: POR EJEMPLO db_accessadmin incluye a db_datareader, db_datawriter, etc?

[Libras]

http://msdn.microsoft.com/en-us/library/ms188659.aspx

Ahi dice que un sysadmin puede hacer tooooodo :)

[ilu7ionHD]

Libras Master.

Te parece que fuera asi la sentencia?

Código SQL:

CREATE ROLE administrador;
GRANT db_ddladmin TO administrador;
 

[Libras]

pues si te funciona si :)

[ilu7ionHD]

Jjaja soy novato, como recomendarias hacerlo?

[Libras]

primero creas el rol, despues le das permisos al rol sobre la base de datos que necesitas(sobre el schema del rol), despues creas un usuario que agregas al rol, algo asi :P

Código SQL:

USE [master]
CREATE ROLE [dba]
GO
ALTER AUTHORIZATION ON SCHEMA::[db_accessadmin] TO [dba]
GO
USE [master]
GO
ALTER AUTHORIZATION ON SCHEMA::[db_securityadmin] TO [dba]
GO
USE [master]
GO
ALTER AUTHORIZATION ON SCHEMA::[db_owner] TO [dba]
GO
USE [master]
GO
ALTER AUTHORIZATION ON SCHEMA::[db_backupoperator] TO [dba]
GO
USE [master]
GO
ALTER AUTHORIZATION ON SCHEMA::[db_ddladmin] TO [dba]
GO
USE [master]
GO
USE [master]
GO
ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [dba]
GO
USE [master]
GO
ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [dba]
GO
 
 
USE [master]
GO
CREATE LOGIN [libras] WITH PASSWORD=N'*******' MUST_CHANGE, DEFAULT_DATABASE=[testing], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
GO
USE [testing]
GO
CREATE USER [Libras] FOR LOGIN [libras]
GO
USE [testing]
GO
EXEC sp_addrolemember N'dba', N'Libras'
GO
 
USE [testing]
GO
GRANT UPDATE TO [dba]
GO
USE [testing]
GO
GRANT TAKE OWNERSHIP TO [dba]
GO
USE [testing]
GO
GRANT AUTHENTICATE TO [dba]
GO
USE [testing]
GO
GRANT CONNECT REPLICATION TO [dba]
GO
USE [testing]
GO
GRANT CONTROL TO [dba]
GO
USE [testing]
GO
GRANT BACKUP DATABASE TO [dba]
GO
USE [testing]
GO
GRANT CREATE AGGREGATE TO [dba]
GO
USE [testing]
GO
GRANT CREATE FULLTEXT CATALOG TO [dba]
GO
USE [testing]
GO
GRANT CREATE CERTIFICATE TO [dba]
GO
USE [testing]
GO
GRANT CREATE ASYMMETRIC KEY TO [dba]
GO
USE [testing]
GO
GRANT CREATE SYMMETRIC KEY TO [dba]
GO
USE [testing]
GO
GRANT CREATE QUEUE TO [dba]
GO
USE [testing]
GO
GRANT CREATE XML SCHEMA COLLECTION TO [dba]
GO
USE [testing]
GO
GRANT CREATE CONTRACT TO [dba]
GO
USE [testing]
GO
GRANT CREATE REMOTE SERVICE BINDING TO [dba]
GO
USE [testing]
GO
GRANT CREATE ASSEMBLY TO [dba]
GO
USE [testing]
GO
GRANT CREATE SCHEMA TO [dba]
GO
USE [testing]
GO
GRANT CREATE FUNCTION TO [dba]
GO
USE [testing]
GO
GRANT CREATE DATABASE DDL EVENT NOTIFICATION TO [dba]
GO
USE [testing]
GO
GRANT CREATE DEFAULT TO [dba]
GO
USE [testing]
GO
GRANT CREATE PROCEDURE TO [dba]
GO
USE [testing]
GO
GRANT CREATE RULE TO [dba]
GO
USE [testing]
GO
GRANT CREATE ROLE TO [dba]
GO
USE [testing]
GO
GRANT CREATE ROUTE TO [dba]
GO
USE [testing]
GO
GRANT CREATE SERVICE TO [dba]
GO
USE [testing]
GO
GRANT CREATE SYNONYM TO [dba]
GO
USE [testing]
GO
GRANT CREATE TABLE TO [dba]
GO
USE [testing]
GO
GRANT CREATE MESSAGE TYPE TO [dba]
GO
USE [testing]
GO
GRANT CREATE TYPE TO [dba]
GO
USE [testing]
GO
GRANT CREATE VIEW TO [dba]
GO
USE [testing]
GO
GRANT EXECUTE TO [dba]
GO
USE [testing]
GO
GRANT DELETE TO [dba]
GO
USE [testing]
GO
GRANT INSERT TO [dba]
GO
USE [testing]
GO
GRANT ALTER ANY DATABASE AUDIT TO [dba]
GO
USE [testing]
GO
GRANT ALTER ANY FULLTEXT CATALOG TO [dba]
GO
USE [testing]
GO
GRANT ALTER ANY CERTIFICATE TO [dba]
GO
USE [testing]
GO
GRANT ALTER ANY ASYMMETRIC KEY TO [dba]
GO
USE [testing]
GO
GRANT ALTER ANY SYMMETRIC KEY TO [dba]
GO
USE [testing]
GO
GRANT ALTER ANY CONTRACT TO [dba]
GO
USE [testing]
GO
GRANT ALTER ANY DATABASE DDL TRIGGER TO [dba]
GO
USE [testing]
GO
GRANT ALTER ANY REMOTE SERVICE BINDING TO [dba]
GO
USE [testing]
GO
GRANT ALTER ANY ASSEMBLY TO [dba]
GO
USE [testing]
GO
GRANT ALTER ANY DATASPACE TO [dba]
GO
USE [testing]
GO
GRANT ALTER ANY SCHEMA TO [dba]
GO
USE [testing]
GO
GRANT ALTER ANY DATABASE EVENT NOTIFICATION TO [dba]
GO
USE [testing]
GO
GRANT ALTER ANY APPLICATION ROLE TO [dba]
GO
USE [testing]
GO
GRANT ALTER ANY ROLE TO [dba]
GO
USE [testing]
GO
GRANT ALTER ANY ROUTE TO [dba]
GO
USE [testing]
GO
GRANT ALTER ANY SERVICE TO [dba]
GO
USE [testing]
GO
GRANT ALTER ANY MESSAGE TYPE TO [dba]
GO
USE [testing]
GO
GRANT ALTER ANY USER TO [dba]
GO
USE [testing]
GO
GRANT ALTER TO [dba]
GO
USE [testing]
GO
GRANT SHOWPLAN TO [dba]
GO
USE [testing]
GO
GRANT CHECKPOINT TO [dba]
GO
USE [testing]
GO
GRANT REFERENCES TO [dba]
GO
USE [testing]
GO
GRANT BACKUP LOG TO [dba]
GO
USE [testing]
GO
GRANT SELECT TO [dba]
GO
USE [testing]
GO
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [dba]
GO
USE [testing]
GO
GRANT VIEW DEFINITION TO [dba]
GO
USE [testing]
GO
GRANT VIEW DATABASE STATE TO [dba]
GO
 

[ilu7ionHD]

me acabastes!!!!!!!!!! jajaja uhh. Entonces la forma mia, esta mal?

[Libras]

esque con la forma que mencionas creas un rol, le das algunos permisos, pero no le das el "power" sobre los objetos de la base de datos :)

[ilu7ionHD]

Claro, entiendo :)) agradesco tu ayuda amigo. mil gracias

[ilu7ionHD]

Amigo puedes decirme que significa lo que esta en rojo ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [dba]

N'dba', N'Libras'
CREATE LOGIN [libras] WITH PASSWORD=N'*******' MUST_CHANGE, DEFAULT_DATABASE=[testing], CHECK_EXPIRATION=ON, CHECK_POLICY=ON

[Libras]

La N significa que es una cadena de tipo Nvarchar http://www.bernardorobelo.com/2011/0...archar-en.html

on schema, es un alter sobre el schema de la base de datos

WITH PASSWORD=N'*******' MUST_CHANGE, DEFAULT_DATABASE=[testing], CHECK_EXPIRATION=ON, CHECK_POLICY=ON

aqui dice que el password que defines no se cambia por el usuario, que la base de datos defaul(la que aparece cuando se loguea) es testing, que no hay expiracion del password, y que no se basa en la politica de windows para passwords

saludos!

[ilu7ionHD]

me has ayudado demaciado amigo, no se como podria agradecertelo.
use tu codigo y no me tiro ningún error hasta me aparecen los inicio de sección pero al momento de entrar me da error 18452.
solo me deja con la autentificacion de windows los user y pass no lo reconoce

[Libras]

el login que creaste donde aparece? supongo que en los folder del servidor no??

[ilu7ionHD]

Security- logins ahy aparecen

[ilu7ionHD]

me sigue dando error

[Libras]

pregunta, al instalar el sql server se le dejo autenticacion mixta o solo windows, en caso de que sea solo windows, omite el paso de crear el login y dale accesos a un usuario de windows, y como ya tienes el rol creado solo dale permisos sobre el rol.


saludos