SQL Logins with CreateDB rights cannot add themselves to roles

SQL Logins with CreateDB rights cannot add themselves to roles

Post by marts_poin.. » Fri, 03 Nov 2000 21:11:42



What is the preferred way to allow users with createDB rights to add
themselves to roles within a newly created DB?

We're using SQL logins, some of them have createDB rights, to create
new DBs. Each DB also has a user-defined role within it.  I want to
automate the creation of a DB, adding login access and adding of the
user to the user-defined role.

I can't give the current login access rights to the DB, because the
login already has an account in the DB under a different name (dbo):
CREATE DATABASE RoleTest
use RoleTest
exec sp_grantdbaccess TestLogin, 'TestLogin'  <<< Fails!

But if I give ownership away to SA, the current login then loses any
rights to change RoleMember details:
CREATE DATABASE RoleTest
use RoleTest
exec sp_addrole 'UserDefinedRole'
exec sp_changedbowner SA
exec sp_grantdbaccess TestLogin, 'TestLogin'
exec sp_addrolemember UserDefinedRole ,  TestLogin  <<< Fails!

Thanks in advance

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

SQL Logins with CreateDB rights cannot add themselves to roles

Post by Dan Guzma » Fri, 03 Nov 2000 23:56:19


If a user creates (and therefore owns) a database, they are the dbo and have
full permissions on all objects within the database.  Why would you need to
also add the database owner to user roles?

Hope this helps.


Quote:

> What is the preferred way to allow users with createDB rights to add
> themselves to roles within a newly created DB?

> We're using SQL logins, some of them have createDB rights, to create
> new DBs. Each DB also has a user-defined role within it.  I want to
> automate the creation of a DB, adding login access and adding of the
> user to the user-defined role.

> I can't give the current login access rights to the DB, because the
> login already has an account in the DB under a different name (dbo):
> CREATE DATABASE RoleTest
> use RoleTest
> exec sp_grantdbaccess TestLogin, 'TestLogin'  <<< Fails!

> But if I give ownership away to SA, the current login then loses any
> rights to change RoleMember details:
> CREATE DATABASE RoleTest
> use RoleTest
> exec sp_addrole 'UserDefinedRole'
> exec sp_changedbowner SA
> exec sp_grantdbaccess TestLogin, 'TestLogin'
> exec sp_addrolemember UserDefinedRole ,  TestLogin  <<< Fails!

> Thanks in advance

> Sent via Deja.com http://www.deja.com/
> Before you buy.


 
 
 

SQL Logins with CreateDB rights cannot add themselves to roles

Post by marts_poin.. » Sat, 04 Nov 2000 01:22:18


I want all logins/users to be entered into the user-defined role (to
limit their access rights), after the DB is created the current login
cannot be added to the role, until the login is given access to the DB,
which is what is causing the problem.

i.e. the command "exec sp_addrolemember UserDefinedRole, testlogin"
fails because it must be preceded by the command "exec sp_grantdbaccess
TestLogin, 'TestLogin'" which fails.

Therefore I cannot give users the ability to create DBs in which they
have limited access rights, which is what I'm trying to achieve.
Believe me... they need limited access rights and not full control.

Cheers



> If a user creates (and therefore owns) a database, they are the dbo
and have
> full permissions on all objects within the database.  Why would you
need to
> also add the database owner to user roles?

> Hope this helps.



> > What is the preferred way to allow users with createDB rights to add
> > themselves to roles within a newly created DB?

> > We're using SQL logins, some of them have createDB rights, to create
> > new DBs. Each DB also has a user-defined role within it.  I want to
> > automate the creation of a DB, adding login access and adding of the
> > user to the user-defined role.

> > I can't give the current login access rights to the DB, because the
> > login already has an account in the DB under a different name (dbo):
> > CREATE DATABASE RoleTest
> > use RoleTest
> > exec sp_grantdbaccess TestLogin, 'TestLogin'  <<< Fails!

> > But if I give ownership away to SA, the current login then loses any
> > rights to change RoleMember details:
> > CREATE DATABASE RoleTest
> > use RoleTest
> > exec sp_addrole 'UserDefinedRole'
> > exec sp_changedbowner SA
> > exec sp_grantdbaccess TestLogin, 'TestLogin'
> > exec sp_addrolemember UserDefinedRole ,  TestLogin  <<< Fails!

> > Thanks in advance

> > Sent via Deja.com http://www.deja.com/
> > Before you buy.

Sent via Deja.com http://www.deja.com/
Before you buy.
 
 
 

SQL Logins with CreateDB rights cannot add themselves to roles

Post by Dan Guzma » Sat, 04 Nov 2000 14:50:09


I ran the following script under SQL 7 SP 2 and it works except that the
user seems to remain dbo until they reconnect.  It seems to work with fine
with SQL 2000 if a USE is executed after dropping the guest user from the
db_owner role.

This is a bit of a kluge so don't be surprised if it doesn't work with
future SPs or versions.  I can't think of a standard method to accomplish
something like this.

CREATE DATABASE RoleTest
GO
USE RoleTest
GO
EXEC sp_addrole UserDefinedRole
EXEC sp_adduser TempLogin, TestLogin
EXEC sp_addrolemember 'db_owner','guest'
EXEC sp_changedbowner sa
EXEC sp_change_users_login 'update_one', 'TestLogin', 'TestLogin'
EXEC sp_droprolemember 'db_owner','guest'
GO
USE RoleTest
GO

Hope this helps.


> I want all logins/users to be entered into the user-defined role (to
> limit their access rights), after the DB is created the current login
> cannot be added to the role, until the login is given access to the DB,
> which is what is causing the problem.

> i.e. the command "exec sp_addrolemember UserDefinedRole, testlogin"
> fails because it must be preceded by the command "exec sp_grantdbaccess
> TestLogin, 'TestLogin'" which fails.

> Therefore I cannot give users the ability to create DBs in which they
> have limited access rights, which is what I'm trying to achieve.
> Believe me... they need limited access rights and not full control.

> Cheers



> > If a user creates (and therefore owns) a database, they are the dbo
> and have
> > full permissions on all objects within the database.  Why would you
> need to
> > also add the database owner to user roles?

> > Hope this helps.



> > > What is the preferred way to allow users with createDB rights to add
> > > themselves to roles within a newly created DB?

> > > We're using SQL logins, some of them have createDB rights, to create
> > > new DBs. Each DB also has a user-defined role within it.  I want to
> > > automate the creation of a DB, adding login access and adding of the
> > > user to the user-defined role.

> > > I can't give the current login access rights to the DB, because the
> > > login already has an account in the DB under a different name (dbo):
> > > CREATE DATABASE RoleTest
> > > use RoleTest
> > > exec sp_grantdbaccess TestLogin, 'TestLogin'  <<< Fails!

> > > But if I give ownership away to SA, the current login then loses any
> > > rights to change RoleMember details:
> > > CREATE DATABASE RoleTest
> > > use RoleTest
> > > exec sp_addrole 'UserDefinedRole'
> > > exec sp_changedbowner SA
> > > exec sp_grantdbaccess TestLogin, 'TestLogin'
> > > exec sp_addrolemember UserDefinedRole ,  TestLogin  <<< Fails!

> > > Thanks in advance

> > > Sent via Deja.com http://www.deja.com/
> > > Before you buy.

> Sent via Deja.com http://www.deja.com/
> Before you buy.

 
 
 

1. Help: IIS, SQL 2000, Logins, Users, Roles

Well, I guess this is the classic scenario. I've created a web-interface to
a database
and centered the application around the web-interface. Everyone logs in as
SA and who
they are (username, password) is compared with entries in the database they
log onto.

There are two types of individuals that can use the web-interface:
 - Administrators
 - Users

"Administrators" will have access to all tables and stored procedures.
"Users" will
have access to but a few tables and stored procedures. I'm confused about
Logins,
Users, Roles, you name it. So, what I've done ...

I've created two logins: "Admin" and "User". Standard SQL Server
authentication is used
as they log on, giving either "Admin" or "User" plus password when logging
onto the
server. But ...with the above, how do I set up Roles and Users? I mean, step
by step, how do I create
these things in appropriate order and set the object-permissions. When I
look at it, roles and users (first time
for everything) seem interchangeable. Also, what permissions should I allow
this "public"-role to have? I'm
confused about this, and stressed like never before with this project.

Thanks!

Tom

2. Backup Strategy

3. What roles/rights needed: Backup a SQL db

4. how to get timestamp for table?

5. SQL 2000 Application Role Rights Issue

6. OR-Portland-Oracle Project Leader

7. Cannot createdb a database I just "destroydb"'d

8. ole2 problem

9. Errors converting SQL 6.5-Logins to SQL 7.0-Logins

10. Programmatically adding new SQL Logins

11. failure to add new logins in SQL Server 6.5

12. Adding Grants and Logins through SQL-DMO

13. can I copy objects like logins/roles ?