CODEWITHSUNDEEP
database-schemasap-ase
santosh
santosh

Reputation: 425

how can we identified schema name in sybase ase 15.5?

I am trying to get schema name in sybase database. First I have create login(user1) from sa user and than i have connect with user1 by giving login name(user1) and password now i have tried to create table by giving following command:-

create table user1.table1(
emp_id                          int                              not null,
name                            varchar(80)                      not null

)

but it was showing access denied error than i have logged-in from sa user and grant sa_role to user1 and then again i have run above mention query for create table and table were created successfully.

here I am not exactly getting that user1 is schema name or not or how can I identified schema name? I want to also know that is there any role except sa_role for grant create insert delete table ,views and all other objects of sybase database.

Upvotes: 1

Views: 4609

Answers (1)

Mike Gardner
Mike Gardner

Reputation: 6651

Sybase ASE does not use the schema concept that SQL Server and Oracle use. Objects are located inside a database, and owned by a user - no other logical separations are there. So your syntax is wrong. 

create table table1 
 (
    emp_id      int                              not null,
    name        varchar(80)                      not null
 )

Sybase ASE Create Table

Additionally, Sybase/SAP best practices tells us all database objects should be created/owned by dbo with permissions granted to groups/roles/users to access those objects. Users who own database objects can not be removed, so if User1 gets fired, you will have to identify all the objects he owns, and change the ownership of those objects before his account can be deleted.

So for your example, the dbo user (typically sa) would create the objects, then GRANT permissions (INSERT/UPDATE/DELETE/etc) to the groups/roles/users who need access.

More information on managing user permissions can be found in the Sybase ASE System Administrators Guide Vol 1.

And more information about Roles/Groups from the System Admin Guide.

Upvotes: 2

Related Questions