Roles in SQL Server

Just for my reference

http://www.techrepublic.com/article/understanding-roles-in-sql-server-security/1061781
http://msdn.microsoft.com/en-in/library/ms189121.aspx
http://msdn.microsoft.com/en-us/library/ms188659.aspx

An example on how to create Login/User in SQL Server and adding roles using t-sql below

IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'myuser')
BEGIN
    DROP USER myuser
    DROP LOGIN myuser
END;
GO

CREATE LOGIN myuser WITH PASSWORD = '1234!@#$$#@!'
GO

Use [Database];
GO

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'myuser')
BEGIN
    CREATE USER [myuser] FOR LOGIN [myuser]
    EXEC sp_addsrvrolemember @loginame = N'myuser', @rolename = N'sysadmin'
    EXEC sp_addrolemember N'db_owner', N'myuser'
    EXEC sp_addrolemember 'db_ddladmin', N'myuser' -- this contains create table permission
    EXEC sp_addsrvrolemember @loginame = N'myuser', @rolename = N'dbcreator'
END;
GO


In-case of domain account, we need to have below

CREATE LOGIN [domain\user] FROM WINDOWS
GO

To find out assigned permissions for an user, below t-sql can be used. It will display permissions for the logged-in user

"SELECT permission_name FROM fn_my_permissions(NULL, 'SERVER')";
"SELECT permission_name FROM fn_my_permissions(NULL, 'DATABASE')";